Query Performance issue - 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 2000 Forums

SQL Server Administration (2000)

Query Performance issue

Author

Topic

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2006-10-02 : 15:50:33

Hi Guys,

While executing the below Queries, we find that the exection time diff from each.

Query1:
=======

use Test
go
declare @DateFrom varchar(20), @DateTo varchar(20)
declare @dDateFrom datetime, @dDateTo datetime

select @DateFrom = '9/28/2006', @DateTo = '9/29/2006'
select @dDateFrom = @DateFrom, @dDateTo = @DateTo

SELECT distinct Symbol from ABCOrders
WHERE OrderDateTime >= @dDateFrom and OrderDateTime < @dDateTo
ORDER BY Symbol

Results: This query is taking 40 seconds to return 883 records
========

Query2:
=======

select distinct Symbol from ABCOrders
WHERE OrderDateTime >= '9/28/2006' and OrderDateTime < '9/29/2006'
ORDER BY Symbol

Results: This query is taking 6 seconds to return 883 records
========

Can anyone explain/suggest me, how the execution plan for both queries are different? and why the first Query is taking more time that second one?

Regards
Sri

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2006-10-02 : 16:02:08

In short, the optimizer has no idea how far apart two variables are. But it does have a good idea how far apart two constants are. Because of this, the first query does a table scan, and the second uses an index seek.

Luis Martin
Yak Posting Veteran

54 Posts

Posted - 2006-10-02 : 20:24:50

What do you see in execution plan?. I think the answer is there (like mcrowley suggest).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 01:07:17

If you run the two queries after each other within a small time frame, there is a good chance that the query is cached.
Just for experimenting, runt the two queries the other order, with the constant date first, and then the variable date selection.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-03 : 01:18:19

How do you know the execution plans are different?

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-10-03 : 01:27:32

Isn't there some trick with Parameter Sniffing that can help with this? (Assuming the actual code is in an SProc). Damned if I can remember what it is though ...

Kristen

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-10-03 : 01:49:05

Run the following SET statements in Query Analyzer for us so we can get some more information about what is going on. These commands will output detailed timing and disk IO statistics for your queries.

SET STATISTICS IO ON
SET STATISTICS TIME ON

After you run the SET statements, remark those lines out and then run your test queries from the same Query Analyzer session. Paste the results from the Messages tab in a reply here. Make sure you run your queries at least twice (quickly one after another) and paste the results from the last attempt.

After you run your commands with the two SET statements above, open a new QA session to your database and issue the following SET statement: