Query keeps running

Question

Post reply

Query keeps running

a2zwd

SSCrazy

Points: 2356
More actions
October 6, 2008 at 5:12 am

#382946

Hi,
I am writing a query to display records from a table between 2 dates.
When I write the query with static dates, its executing in 1 second. If I substitute variables, its executing for a long time(keeps executing even after 30 minutes.)
Whats the problem.
Ex:
DECLARE @FromDate DATETIME, @ToDate DATETIME
SET @ToDate = GETDATE()
--SET @ToDate = CAST(YEAR(@ToDate) AS VARCHAR(4)) + '-' + CAST(MONTH(@ToDate) AS VARCHAR(2))+ '-1'
SET @FromDate = DATEADD(dd, -1, GETDATE())
--SET @FromDate = CAST(YEAR(@FromDate) AS VARCHAR(4)) + '-' + CAST(MONTH(@FromDate) AS VARCHAR(2))+ '-1'
SELECT * FROM REFERRALS WHERE RECVD_DTTM >= '2008-10-06 23:59:59' AND RECVD_DTTM < '2008-10-05 00:00:00'
It is executing in 1 second where as
SELECT * FROM REFERRALS WHERE RECVD_DTTM >= @FromDate AND RECVD_DTTM < @ToDate
its keep on executing

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

abhijeetv SSCommitted Points: 1945 More actions · Answer 1

a few Qs:

What is the commented part in your SQL? If uncommented then both the variables have the same date value.

Can you make this a dynamic SQL to try and print the SQL which would help in identification of the exact cause?

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 2

abhijeetv (10/6/2008)
a few Qs:
What is the commented part in your SQL? If uncommented then both the variables have the same date value.
Can you make this a dynamic SQL to try and print the SQL which would help in identification of the exact cause?

Hey,

Do you have the appropriate index on RECVD_DTTM attribute?

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

a2zwd (10/6/2008)
Hi,
I am writing a query to display records from a table between 2 dates.
When I write the query with static dates, its executing in 1 second. If I substitute variables, its executing for a long time(keeps executing even after 30 minutes.)
Whats the problem.
Ex:
DECLARE @FromDate DATETIME, @ToDate DATETIME
SET @ToDate = GETDATE()
--SET @ToDate = CAST(YEAR(@ToDate) AS VARCHAR(4)) + '-' + CAST(MONTH(@ToDate) AS VARCHAR(2))+ '-1'
SET @FromDate = DATEADD(dd, -1, GETDATE())
--SET @FromDate = CAST(YEAR(@FromDate) AS VARCHAR(4)) + '-' + CAST(MONTH(@FromDate) AS VARCHAR(2))+ '-1'
SELECT * FROM REFERRALS WHERE RECVD_DTTM >= '2008-10-06 23:59:59' AND RECVD_DTTM < '2008-10-05 00:00:00'
It is executing in 1 second where as
SELECT * FROM REFERRALS WHERE RECVD_DTTM >= @FromDate AND RECVD_DTTM < @ToDate
its keep on executing

How many times did you try both versions? Is there a chance that when you tried the variables version you had some blocking? The huge time difference (1 second compared to over 30 minutes) on a very simple query makes me believe that this is more a case of blocking then using a wrong query plan. Did you check for blocking when you ran the slower query? Did you check if both queries have different query plan?

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

a2zwd SSCrazy Points: 2356 More actions · Answer 4

Hi,

I've checked in Active Monitoring. The process is blocked by my own processid. STATE is in SLEEP mode. WAIT TYPE is PAGEIOLATCH SH

The problem is at Ref.RECVD_DTTM >= xxxxx

If I substitute literal date, its running well even though Ref.RECVD_DTTM < @ToDate(variable)

How can I resolve this issue.

thanks

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 5

Try setting just your variables and then print them. Sometimes when working with a Date Timestamp, you aren't getting the value you expect.

Greg E

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

I can see why this runs fast:

SELECT * FROM REFERRALS WHERE RECVD_DTTM >= '2008-10-06 23:59:59' AND RECVD_DTTM < '2008-10-05 00:00:00'
It is executing in 1 second where as

It won't select anything. It is looking for records where RECVD_DTTM is greater than or equal to 2008-10-06 23:59:59 AND LESS THAN 2008-10-05 00:00:00. This will not return anything.

Could you post the DDL (create table statement) for the table, some sample data for the table (as insert statements that can be cut, paste and run in SSMS), and the expected results of a typical query based on the sample data provided?

😎