CODEWITHSUNDEEP
sql-server-2005
nvahalik
nvahalik

Reputation: 559

SQL Server takes forever to process when I use a local variable instead of specifying a date directly?

I apologize in advance for my newbness! Using SQL Server 2005 (9.00.4053.00).

It takes 2:22 to run this query: 

DECLARE @Start_date CHAR(10);
SET @Start_date = '2010-07-01';

SELECT 
    DATEPART(wk, rp.dtScanDate) AS 'Week Number',
    COUNT(rp.qwImageID) AS 'Documents',
    SUM(CAST(rp.iCompressedSize AS BIGINT))/1024/1024 AS 'Compressed',
    SUM(CAST(rp.iUncompressedSize AS BIGINT))/1024/1024 AS 'Uncompressed'
INTO [#TempRVOSPolicy]
FROM
    Policy rp
WHERE
    dtScanDate >= @Start_date
GROUP BY 
    DATEPART(wk, rp.dtScanDate)
ORDER BY
    DATEPART(wk, rp.dtScanDate);

But it takes 0:00 to run this one:

DECLARE @Start_date CHAR(10);
SELECT 
    DATEPART(wk, rp.dtScanDate) AS 'Week Number',
    COUNT(rp.qwImageID) AS 'Documents',
    SUM(CAST(rp.iCompressedSize AS BIGINT))/1024/1024 AS 'Compressed',
    SUM(CAST(rp.iUncompressedSize AS BIGINT))/1024/1024 AS 'Uncompressed'
INTO [#TempRVOSPolicy]
FROM
    Policy rp
WHERE
    dtScanDate >= '2010-07-01'
GROUP BY 
    DATEPART(wk, rp.dtScanDate)
ORDER BY
    DATEPART(wk, rp.dtScanDate);

Why is this?

Upvotes: 0

Views: 159

Answers (3)

a1ex07
a1ex07

Reputation: 37354

If you always run one query after another, it may return you cashed result (for second query)...

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 134961

what happens when you do this?

DECLARE @Start_date datetime;
SET @Start_date = '20100701';

Upvotes: 1

Justin Niessner
Justin Niessner

Reputation: 245399

Take a look at the Query Plan that SQL Server generates.

My guess is that the query optimizer optimizes away the cast from char(10) to datetime (does it a single time) in the second query whereas it needs to cast the value once for each row in the first.

Upvotes: 1

Related Questions