Query not using an index on a date column properly

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)

Query not using an index on a date column properly

Author

Topic

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-06-02 : 11:01:37

I'm running the following two queries:



DECLARE @QPrevDate DATETIME
select @QPrevDate = '2005-06-01'

select count(*)
FROM	Quotes
where	quote_date >= '2005-06-01'

select count(*)
FROM	Quotes
where	quote_date >= @QPrevDate

They both return the same results but the second one runs about 100 times slower. There is an index on Quote_Date. They both use it. the second query seems to use it "impropery".

They both generate an index seek but the second query thinks many more columns will be returned than actually are.

Has anyone seen anything like this?

I've tried updating stats. I've dropped and recreated the index. It's like the parser can't figure out what's going to when the parameter is a variable rather than a constant.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-02 : 13:40:12

paste that into query analyzer, then hit CTRL + L

Check the execution plan of each. Are they exactly the same?

EDIT: Duh that is where you got your info from in the first place. weird.

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-02 : 13:44:50

What you really want is CTRL + K. CTRL+L is the estimated execution plan and CTRL+K is the actual execution plan. They can differ.

Bill, are you viewing the estimated execution plan or the actual execution plan? Do they differ?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-02 : 14:20:54

Did you run profiler when you did this? What are the Reads and Duration?

How about the execution plan shown in profiler...is it the same too?

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

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2005-06-02 : 16:46:49

I'm viewing the actual execution plan. (The estimated plan shows the same thing though.)

For those especially curious...

StmtText
-------------------------------------------------------------------

DECLARE @QPrevDate DATETIME
select @QPrevDate = '2005-06-01'

select count(*)
FROM Quotes
where quote_date >= '2005-06-01'

(2 row(s) affected)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1003])))
|--Stream Aggregate(DEFINE:([Expr1003]=Count(*)))
|--Index Seek(OBJECT:([fq_test].[dbo].[QUOTES].[IX_QUOTES_QuoteDate]), SEEK:([QUOTES].[QUOTE_DATE] >= 'Jun 1 2005 12:00AM') ORDERED FORWARD)

(3 row(s) affected)

StmtText
----------------------------------------------------------------

select count(*)
FROM Quotes
where quote_date >= @QPrevDate

(1 row(s) affected)

StmtText
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|--Compute Scalar(DEFINE:([Expr1002]=Convert([Expr1009])))
|--Stream Aggregate(DEFINE:([Expr1009]=Count(*)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1006], [Expr1007], [Expr1008]))
|--Compute Scalar(DEFINE:([Expr1006]=Convert([@QPrevDate]), [Expr1007]=NULL, [Expr1008]=If (Convert([@QPrevDate])=NULL) then 0 else 22))
| |--Constant Scan
|--Index Seek(OBJECT:([fq_test].[dbo].[QUOTES].[IX_QUOTES_QuoteDate]), SEEK:([QUOTES].[QUOTE_DATE] > [Expr1006] AND [QUOTES].[QUOTE_DATE] < [Expr1007]), WHERE:(Convert([QUOTES].[QUOTE_DATE])>=[@QPrevDate]) ORDERED FORWARD)

(6 row(s) affected)

Oddly enough they show vastly different query plans but very similar results in Profiler. In this simple of a query they run right about the same duration.

What I posted here was a simplification of the entire query. The two different query plans don't affect just getting a count that much. But they do affect the larger query pretty significantly.

I just finished a similar test on a different server using the database from a completely different application. When you query a table with an index on a date column do you see different query plans?

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources