The fact that the first query is very fast indicates that you have an index
The reason for the long execution time in the second case is most likely
that the database optimizer selects a table scan instead of an index seek
followed by a number of bookmark lookups.
The reason for the good performance if you use hardcoded dates is that with
hardcoded dates, the query optimizer can use the statistics for the ADETDATE
column to estimate how many rows will match the condition. If only a few
rows match, it will use the index because it is faster.
If you have expressions in the query, the optimizer selects a plan that
avoids worst-case behavior, so to avoid the potentiual cost of a large
number of bookmark lookups it selects a table scan instead.
There are two ways to handle this situation:
1) Modify the indexes so you can avoid bookmark lookups.
Either make the index on ADETDATE clustered, or if that is not possible, add
the column ADA to the ADETDATE index (thus creating a covering index)
2) Give the query optimizer more information so it can select a better plan
If you know (because you know your data) that the query will never return
more than say 1000 rows, you can write the query like this:
Select TOP 1000 ADETDATE , ADA from CC_CDR
where ADETDATE >=convert(char(8),getdate(),112)
and ADETDATE <convert(char(8),getdate()+1,112)
This will tell the optimizer that it will never have to perform more than
1000 bookmark lookups, so it will probably use the index instead of
performaing a table scan.
"Clive" <com> wrote in message