Problem with huge data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with huge data

Author

Topic

madhulatha_b
Starting Member

22 Posts

Posted - 2006-12-18 : 23:55:54

Hi,

In my application we are generating statistics report for two years. For this report I need to pull the data from 4 major tables and each table is containing more than 20 lacs records. When user clicks on report option we are fetching the data from 4 tables (using Joins) and sending the data to ASP page. Because of huge data I am always getting "Timed out problem"

To solve this I have created a job to fetch the data from 4 tables and store the data in one single table. When user clicks on the report I am fetching the data from this table, manipulating into xml format and sending this data to ASP page. In this case I have not faced any Timedout problem.

Is there any better way to solve this problem. Could any body suggest me

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 01:07:47

Please post your query here.
Also post the indexes you might have.

Peter Larsson
Helsingborg, Sweden

madhulatha_b
Starting Member

22 Posts

Posted - 2006-12-19 : 02:22:57

Hi,

Here is the Query

SELECT
P.Sender
,o.[OrderSystem]
,datepart( yy, P.sent )
,datepart( qq, P.sent )
,datepart( mm, P.sent )
,datename( mm, P.sent )
,Pr.Market_Unit
,Pr.Country
,count( distinct convert( varchar(255), t6.[Order] ) ) AS 'OrderCount'
,SUM( P.Quantity * P.net_price * Pr.Offered_Curr_To_SEK_Rate ) AS 'OrderValue'
,AVG( datediff( hh, P.imported, P.sent ) ) AS 'LeadTime'
FROM
DB1..Node6 P
JOIN DB1..Treenode t6 ON t6.Node_ID = P.Treenode
JOIN DB2..Orders o ON o.Node_ID = t6.[Order]
JOIN DB1..treenode t2 ON t2.Id_ = DB1.dbo.Split_Id( t6.Id_, 2, 0 )
JOIN DB1..Node1 Pr ON Pr.TreeNode = t2.Node_Parent
WHERE
t6.status = 6
AND t6.type_ = 6
AND datepart( yy, P.sent ) > datepart( yy, getdate() ) - 2
AND NOT P.sent IS NULL
AND NOT Pr.Offered_Curr_To_SEK_Rate IS NULL

GROUP BY
P.Sender
,o.OrderSystem
,datepart( yy, P.sent )
,datepart( qq, P.sent )
,datepart( mm, P.sent )
,datename( mm, P.sent )
,Pr.Market_Unit
,Pr.Country

Fetching data from two databases DB1,DB2
Treenode,node1,node6 are in DB1 and orders is in DB2

Node 1 and Node 6 used to stored project and product nodes details (Project Structure is

Project
|
Area
|
Site
|
Product
)

Treenode contains all nodes details

Indexes are

Table Name Clustered Index Non Clustered Index
Node1 Node_ID Treenode
Node6 Node_ID Treenode
Treenode Node_ID Node_Parent,Order
ORders Node_ID Treenode

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 06:34:00

I think it is this row causing problems

JOIN DB1..treenode t2 ON t2.Id_ = DB1.dbo.Split_Id(t6.Id_, 2, 0)

Is there another way to write this line? Using LIKE or something?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 07:13:37

Try this

SELECT		P.Sender,
		o.[OrderSystem],
		datepart(yy, P.sent),
		datepart(qq, P.sent),
		datepart(mm, P.sent),
		datename(mm, P.sent),
		Pr.Market_Unit,
		Pr.Country,
		count(distinct t6.[Order]) AS 'OrderCount',
		SUM(P.Quantity * P.net_price * Pr.Offered_Curr_To_SEK_Rate) AS 'OrderValue',
		AVG(datediff(hh, P.imported, P.sent)) AS 'LeadTime' 
FROM		DB1..Node6 P 
INNER JOIN	DB1..Treenode t6 ON t6.Node_ID = P.Treenode
			AND t6.status = 6
			AND t6.type_ = 6
INNER JOIN	DB2..Orders o ON o.Node_ID = t6.[Order] 
INNER JOIN	DB1..treenode t2 ON t2.Id_ = DB1.dbo.Split_Id( t6.Id_, 2, 0 ) 
INNER JOIN	DB1..Node1 Pr ON Pr.TreeNode = t2.Node_Parent 
			AND NOT Pr.Offered_Curr_To_SEK_Rate IS NULL 
WHERE		datediff(yy, P.sent, getdate()) >= 2 
		AND NOT P.sent IS NULL
GROUP BY	P.Sender,
		o.OrderSystem,
		datepart(yy, P.sent),
		datepart(qq, P.sent),
		datepart(mm, P.sent),
		datename(mm, P.sent),
		Pr.Market_Unit,
		Pr.Country

Peter Larsson
Helsingborg, Sweden

madhulatha_b
Starting Member

22 Posts

Posted - 2006-12-19 : 08:39:23

I have executed the above query. But still I am facing same problem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-19 : 08:58:22

And my other question? About the LIKE instead of UDF approach?

Peter Larsson
Helsingborg, Sweden

madhulatha_b
Starting Member

22 Posts

Posted - 2006-12-20 : 04:56:53

I tried the same query by removing UDF. It took one min less to execute the query. But still I am facing the same problem. I am not getting any data to front-end. It is showing empty page after 5 mins. From ASP page I am calling this query.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 05:03:38

If you also remove the line with
"count(distinct t6.[Order]) AS 'OrderCount',"

how fast does the query run then?

Peter Larsson
Helsingborg, Sweden

madhulatha_b
Starting Member

22 Posts

Posted - 2006-12-20 : 06:52:42

It took 20 sec less time to execute after removing the 'Order Count' line

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-20 : 08:24:33

"20 seconds less"... What does that mean?
20 seconds less than 30 minutes? 20 seconds less than 21 seconds?

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-20 : 08:40:32

this line also is not as efficient as it can be:

WHERE datediff(yy, P.sent, getdate()) >= 2

you are better off by calculating a starting date and writing:

WHERE P.Sent >= @StartingDate

That will allow any indexes on the Sent column to be used. And if that column isn't indexed, I would add an index to it.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-20 : 08:44:14

Another thing you can do:

Don't group on quarter or date name, just group on year and month. You can derive quarter by writing (Month-1)/3 +1 and you should display the month name at your presentation layer. Less grouping = more efficient. You should always only group on the minimum needed to calculate your results, and in this case that would be year/month and not year/quarter/month/monthname.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources