[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Help optimizing a query-- its not using the indexes

Edward Galarza

3/27/2007 5:22:00 PM

This query is slow on my production server:

Select Top 10 * From Data
Where
(GetDate()-Created > 365) And
(GetDate()-Modified > 60)
Order By MyPrimaryKey

Data has about a million rows (each record is not very large). Created and
Modified are datetime columns. I have separate indexes defined for each of
them, but SQL Server 2005 insists on doing a full index scan on
MyPrimaryKey. Any suggestions for improving this query or making it use the
indexes?

Some names have been changed to protect their identity ;)

Thanks!

5 Answers

Tibor Karaszi

3/27/2007 5:37:00 PM

0

Never do a calculation on the column side when you can avoid it. I don't know what datatype the
created and modified columns are, but you should transform the conditions to something like:

WHERE
Created >= DATEADD(GETDATE, -365)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Dan English" <nospam@cox.net> wrote in message news:uCn7kRJcHHA.4836@TK2MSFTNGP03.phx.gbl...
> This query is slow on my production server:
>
> Select Top 10 * From Data
> Where
> (GetDate()-Created > 365) And
> (GetDate()-Modified > 60)
> Order By MyPrimaryKey
>
> Data has about a million rows (each record is not very large). Created and Modified are datetime
> columns. I have separate indexes defined for each of them, but SQL Server 2005 insists on doing a
> full index scan on MyPrimaryKey. Any suggestions for improving this query or making it use the
> indexes?
>
> Some names have been changed to protect their identity ;)
>
> Thanks!


TheSQLGuru

3/27/2007 5:37:00 PM

0

1) The optimizer will ignore indexes if it estimates a query will access
between approx 8 and 15% of the rows in a given table. This alone could be
reason for not using indexes. Sequential I/O from table scans is SO much
more efficient than async I/O from index/bookmark lookup activity.

2) Get in the habit of NEVER modifying/functioning a field that is indexed.

3) TSQL's syntax for date comparison is using DATEDIFF or DATEADD.
DATEDIFF ( datepart , startdate , enddate )DATEADD (datepart , number,
date )Taking 2 and 3 into consideration, you might rewrite your query
thusly:SELECT top 10 * FROM dbo.DataWHERE Created < DATEADD(dd, 365,
GETDATE())AND Modified < DATEADD(dd, 60, GETDATE())ORDER BY myprimarykeyBut
still do NOT expect index usage if there are lots of old data in your
system. If you want to see how bad it can be, force the use of the index on
created by using the (INDEX=...) syntax.

--
TheSQLGuru
President
Indicium Resources, Inc.

"Dan English" <nospam@cox.net> wrote in message
news:uCn7kRJcHHA.4836@TK2MSFTNGP03.phx.gbl...
> This query is slow on my production server:
>
> Select Top 10 * From Data
> Where
> (GetDate()-Created > 365) And
> (GetDate()-Modified > 60)
> Order By MyPrimaryKey
>
> Data has about a million rows (each record is not very large). Created
> and Modified are datetime columns. I have separate indexes defined for
> each of them, but SQL Server 2005 insists on doing a full index scan on
> MyPrimaryKey. Any suggestions for improving this query or making it use
> the indexes?
>
> Some names have been changed to protect their identity ;)
>
> Thanks!


Edward Galarza

3/27/2007 5:50:00 PM

0

Thanks guys for the suggestion about DATEADD

AK

3/27/2007 6:04:00 PM

0

On Mar 27, 12:37 pm, "TheSQLGuru" <kgbo...@earthlink.net> wrote:
> 1) The optimizer will ignore indexes if it estimates a query will access
> between approx 8 and 15% of the rows in a given table.

There isn't any hardcoded constant like 8% or 15%. For narrow tables
the threshold may be less than 0.5%, for very wide tables it can
easily exceed 15%. Do your own experiments and see for yourself.

AK

3/27/2007 6:06:00 PM

0

On Mar 27, 12:21 pm, "Dan English" <nos...@cox.net> wrote:
> This query is slow on my production server:
>
> Select Top 10 * From Data
> Where
> (GetDate()-Created > 365) And
> (GetDate()-Modified > 60)
> Order By MyPrimaryKey
>
> Data has about a million rows (each record is not very large). Created and
> Modified are datetime columns. I have separate indexes defined for each of
> them, but SQL Server 2005 insists on doing a full index scan on
> MyPrimaryKey. Any suggestions for improving this query or making it use the
> indexes?
>
> Some names have been changed to protect their identity ;)
>
> Thanks!

In addition to Tibor's suggestion, try one index on both Created and
Modified. Do you really need to select all the columns? If not, try a
covering index. Most likely the first column should be Modified.