4

I have a table with about 1.5 million rows with date_run is indexed non cluster. Query #1 takes 0 second to finish and query #2 takes 3 seconds. Can someone please explain why query #2 runs slower. I also included execution plans for both. Sql server version 2014.

query #1

select  avg14gain
from stocktrack
where 
date_run >=  '2013-3-21'
and date_run <  '2013-3-22'

Valid XHTML http://biginkz.com/Pics/DateHardCoded.jpg.

query #2

declare @today date
declare @yesterday date
set @today='2013-3-22'
set @yesterday='2013-3-21'
select avg14gain
from stocktrack
where 
date_run  >=   @yesterday
and b.date_run <@today

Valid XHTML http://biginkz.com/Pics/DataAsigned.jpg.

  • 2
    Whats `b` in `b.date_run` ? – Alex K. May 11 '15 at 16:25
  • Query 1 is doing an index seek, query 2 is doing a table scan. This means the second query has to read each row in the table, while the first query is only reading select rows from the index. – Dave.Gugg May 11 '15 at 16:26
  • 4
    Yes, but why it is doing table scan? date_run is indexed. These two queries is practically the same except #1 is hardcoded date and #2 is assigned. –  May 11 '15 at 16:31
  • what's the datatype of `date_run`? – Lamak May 11 '15 at 16:54
  • 1
    It has to do with the variable changing your cardinality estimate. SQL doesn't know ahead of time what that variable will be so it's assuming it will be more effeciant to scan the table than to do a seek and an RID lookup. – Zane May 11 '15 at 16:58
  • 6
    To @Zane 's point, since the optimizer doesn't know the value of the variable at plan compile time, it's going to rely on the density vector value(s) of the filter in question to satisfy the second query. In the first query, the optimizer knows the filter values and, as such, can use the histogram to generate estimates. It makes decisions about which operation(s) would be the most efficient based on these estimates. – swasheck May 11 '15 at 17:02
  • 3
    Thanks for elaborating @swasheck Try running with option recompile. – Zane May 11 '15 at 17:10
  • Look at the estimated row counts for each plan. They will be very different, reflecting the optimiser's guess of how many rows exist of a parameterised value. – Michael Green May 12 '15 at 11:42

2 Answers2

0

I am not sure why your query is not picking up the index, but you can use an index hint.

Try something like this:

declare @today date
declare @yesterday date
set @today='2013-3-22'
set @yesterday='2013-3-21'
select avg14gain
from stocktrack
where 
date_run  >=   @yesterday
and b.date_run <@today
with (index([stocktrack].[ix_drun]))

also you can try what is suggested in this post: TSQL not using indexes. See @Justin Dearing's answer (rebuild index / update statistics).

Community
  • 1
  • 1
Pio
  • 4,044
  • 11
  • 46
  • 81
  • I don't like using index hints on the chance that the index might change later and will break if it does. – Zane May 11 '15 at 17:37
  • I optimized the query using with (index([stocktrack].[ix_drun])) . It is faster, but still takes 2 seconds because of the sorting in the execution plan. How can I get rid of sort in this execution plan? –  May 11 '15 at 17:57
  • You can also add a [plan hint](https://technet.microsoft.com/en-us/library/ms190727(v=sql.105).aspx). At this point I would rather go with [@pmbAustin](http://stackoverflow.com/users/594448/pmbaustin)'s solution of [INCLUDE columns](https://msdn.microsoft.com/en-us/library/ms190806.aspx) on the index. – Pio May 12 '15 at 11:21
0

Create an index on date_run, with avg14gain as an INCLUDE column on that index. That way the entire query can be satisfied from the one index, and the optimizer will see that.

pmbAustin
  • 3,890
  • 1
  • 22
  • 33