Performance Issue when I add date clause

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

General SQL Server Forums

New to SQL Server Programming

Performance Issue when I add date clause

Author

Topic

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-01-26 : 22:18:35

Hey Guys,

Last few weeks I have running the following query with no issues:
SELECT
H.STORECODE,
SUM(D.QUANTITYSOLD) AS 'QUANTITY',
H.FINISHDATETIME,
H.VALUENETT
FROM
dbo.TXNHEADER H

JOIN dbo.TXNDETAIL D
ON H.id = D.HEADERID

WHERE
H.TRANSACTIONTYPE = 3
AND
H.TXNSTATUS = 'DONE'
AND
H.FINISHDATETIME BETWEEN
CONVERT(DATE,GETDATE()-14) AND CONVERT(DATE,GETDATE())

GROUP BY
H.STORECODE,
H.FINISHDATETIME,
H.VALUENETT,
H.TXNPROCDATE,
H.TRANSACTIONTYPE,
H.TXNSTATUS

The query usually returns the last 2 weeks worth of data, it takes about 8 seconds to process and returns 35,000 rows (roughly). Today it has taken over 15 minutes for 20,000 rows. I then just tried other queries on the database and they run fine. My memory and cpu usage is low on the server and there are no other jobs being performed.

I then tried altering my existing query and I removed the date clause (WHERE H.FINISHDATETIME BETWEEN CONVERT(DATE,GETDATE()-14) AND CONVERT(DATE,GETDATE())) and the query runs much quicker.

Is there any reason why my query with the date clause will take me such a long time to retrieve my results?

Do any of you know why it would run so slow now when I have been running the same query for the last month and it only takes about 7-8 seconds on average?

Thanks in advanced.

Love the site

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-26 : 22:23:28

Show us your indexes for the tables in your query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

pavlos
Yak Posting Veteran

59 Posts

Posted - 2012-01-26 : 22:31:16

still new to ssms..
i can access indexes via the object explorer.. but what is the easiest way to post them up on here? and what data did you want out of them?

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2012-01-27 : 01:41:03

right click on the table and select script table as | create to | clipboard.....and then paste it here, do the same with the index.

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources