Need Help making this faster - 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 2005 Forums

Transact-SQL (2005)

Need Help making this faster

Author

Topic

vrod
Starting Member

3 Posts

Posted - 2009-12-10 : 17:43:48

I need help speeding up this query that has a table spool taking 48%cost and a table scan taking 49% cost. I am wondering if there is quicker way of retrieving this dataset.

Currently to retieve the data it is taking 3 seconds per day that I retrieve and this dataset is mainly going to be used to retrieve a month at a time so its kinda doggy.

I read some stuff on using Table Variables (Tables in memory) to speed things up but I have no clue on how to use them.

Here is the query:



SELECT     CASE WHEN Zip.Zip IS NULL THEN '0' ELSE Zip.Zip END AS Zip, CASE WHEN ZipInfo.City IS NULL THEN 'N/A' ELSE ZipInfo.City END AS City, 
                      SUM(ej.dbo.ItemLine.Amount) AS Amount, COUNT(DISTINCT ej.dbo.TransactionNode.TransactionID) AS ZipTotal, SUM(ej.dbo.ItemLine.Amount) 
                      / COUNT(DISTINCT ej.dbo.TransactionNode.TransactionID) AS Average
FROM         Zip INNER JOIN
                      ej.dbo.TransactionNode ON Zip.TrxNum = ej.dbo.TransactionNode.TransactionNumber AND 
                      Zip.TrmNum = ej.dbo.TransactionNode.TerminalNumber INNER JOIN
                      ej.dbo.ItemLine ON ej.dbo.TransactionNode.TransactionID = ej.dbo.ItemLine.TransactionID INNER JOIN
                      ej.dbo.ItemTypeDictionary ON ej.dbo.ItemLine.ItemTypeID = ej.dbo.ItemTypeDictionary.ItemTypeID INNER JOIN
                      item.dbo.DepartmentDefinition ON ej.dbo.ItemLine.Department = item.dbo.DepartmentDefinition.departmentNumber LEFT OUTER JOIN
                      ZipInfo ON Zip.Zip = ZipInfo.Zip
WHERE     (Zip.Date BETWEEN '11/17/09' AND CONVERT(DATETIME, CONVERT(VARCHAR, '11/17/09', 101) + ' 23:59:59.999')) AND 
                      (ej.dbo.TransactionNode.TransactionDateTime BETWEEN '11/17/09' AND CONVERT(DATETIME, CONVERT(VARCHAR, '11/17/09', 101) 
                      + ' 23:59:59.999')) AND (Zip.Zip = ZipInfo.Zip)
GROUP BY Zip.Zip, ZipInfo.City
ORDER BY ZipTotal DESC

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-10 : 17:46:12

Are you saying 3 seconds is too long?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

vrod
Starting Member

3 Posts

Posted - 2009-12-10 : 18:02:47

quote:
Originally posted by X002548

Are you saying 3 seconds is too long?

It takes 3 seconds for 1 days worth of data but for some odd reason when I run it for 1 weeks worth of data it takes 2 minutes. I would hate to see what it pulls for a months worth.

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-10 : 18:05:00

how many rows are we talking about?

How are you calling the rows

Where do the rows go?

Do you want to create a text file?

We need more details

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-12-10 : 18:07:25

Also...post the DDL of your table...including the indexes

Also..you convert...convert...convert....datetime, doesn't make much sense

Do you have any stored procedures, or local variables?

Or is ALL of this being called from a front end application

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

vrod
Starting Member

3 Posts

Posted - 2009-12-10 : 18:27:08

quote:
Also..you convert...convert...convert....datetime, doesn't make much sense

I only want to query where the shortdate's equal each other (12/10/09) and not the whoe DATETIME field. This was the only thing I found that worked but by all means if you know of a better way of making it work then let me know.

quote:
Also...post the DDL of your table...including the indexes

Whats a DDL? No indexes have been made and nor do I understand how to use them.

quote:
Do you have any stored procedures, or local variables?

Or is ALL of this being called from a front end application

No SP's, No Local variables, I am running the query from SSMSEE.

Rows being returned are roughly anywhere from 0 - 999

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources