0

I get something weird. i ran this sql:

SELECT   Id , GameTypeId , PlayerId , BetAmount , Profit ,
         DateAndTime
FROM     Results
WHERE    DateAndTime >= DATEADD (DAY , -1 , SYSDATETIME ())
         AND
         DateAndTime < SYSDATETIME ()
ORDER BY DateAndTime ASC;

i have noncluster index on the date column
and the actual number of rows that return is
672 row from 1600016 rows in the table. (the estimated row was 1)

after that i ran this sql:

declare @d DATETIME2(7) 
set @d = DATEADD (DAY , -1 , SYSDATETIME ())
declare  @d2 DATETIME2(7)
set @d2  = SYSDATETIME ()

SELECT   Id , GameTypeId , PlayerId , BetAmount , Profit ,
         DateAndTime
FROM     Results
WHERE    DateAndTime >= @d
         AND
         DateAndTime < @d2
ORDER BY DateAndTime ASC;

and the actual execution plan was TABLE SCANE !!! and the actual number of rows that return is
672 row from 1600016 rows in the table. (the estimated row was 144000 r0ws)

some 1 know what happend here ?!?!?

dani
  • 17
  • 2

5 Answers5

4

Because you're using variables for your values, the query optimizer doesn't know how selective your WHERE clause is, and decides to use a table scan. Try creating a clustered index on your DateAndTime field.

Valentino Vranken
  • 5,597
  • 1
  • 26
  • 28
  • 1
    variables - not parameters. The value of variables isn't known at compile time. `OPTION (RECOMPILE)` can help. – Martin Smith Jan 13 '11 at 11:52
  • i understood this. but is there a way to tell the optimizer to recompile on every run the query or what can i do in this case if i can get a lot of argument in the variables ? – dani Jan 13 '11 at 11:52
  • @dani: create the `SP` using `WITH RECOMPILE` – Quassnoi Jan 13 '11 at 12:04
2

Try

declare @d DATETIME2(7) 
set @d = DATEADD (DAY , -1 , SYSDATETIME ())
declare  @d2 DATETIME2(7)
set @d2  = SYSDATETIME ()

SELECT   Id , GameTypeId , PlayerId , BetAmount , Profit ,
         DateAndTime
FROM     Results
WHERE    DateAndTime >= @d
         AND
         DateAndTime < @d2
ORDER BY DateAndTime ASC
OPTION (RECOMPILE);

This will recompile the plan for the statement once the value of the variables are known and so allow accurate cardinality estimates to be used. If you know you will always be selecting a very small percentage you could just use the FORCESEEK (if SQL Server 2008) hint instead to avoid the recompiles but using this hint may be catastrophically bad for larger ranges because of the number of key lookups!

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
1

In addition to Martin's answer...

Results.DateAndTime should be datetime2(7) too as per the variables. if not, you most likely have a datatype precedence issue

gbn
  • 422,506
  • 82
  • 585
  • 676
0

If the number of estimated rows is very high, the optimizer comes to the conclusion that a full table scan might be more effective than an index seek + RID lookup.

so, the question is why the number of estimated rows is way off?

I'm, unfortunately, not so deep into SQL Server yet. However, I'd say that's because of the bind parameters; that can affect the cardinality estimates (estimates rows) quite a lot (at least in other databases).

Markus Winand
  • 8,371
  • 1
  • 35
  • 44
0

I've found that when the estimated number of rows is vastly different from the actual number, you're either missing statistics somewhere or the statistics that you have are out of date. You should be able to look at a query plan in SSMS and find out which statistics are either missing or out of date based on cardinality estimates for the different operators.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68