CODEWITHSUNDEEP
sqlsql-server
Sven Müller
Sven Müller

Reputation: 131

Speed up query containing convert

does someone know how to speed up following query:

select count (*)

from table

where column1 between DATEADD(day, -90,  convert(date, getdate())) AND getdate() 

and column2 is not null

This query takes 20 sec. for 8.000 rows

I think/know the bottleneck is the conversion, but it is necessary...

Upvotes: 1

Views: 128

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44336

It is unlikely to be an index issue.

There is a bug in sqlserver-2008. It should be fixed in newer versions of sqlserver

Try this instead:

declare @from datetime = DATEADD(day, -90,  convert(date, getdate()))
declare @to datetime = getdate() 

select count (*)
from table
where column1 between @from and @to 
and column2 is not null

You can read about a similar problem here

Upvotes: 1

Karthik Kola
Karthik Kola

Reputation: 93

Make sure that column1 and WHERE clause condition datatypes are compatible or not. Also try to create the index on columns when necessary, but there is always maintenance over head for indexes.

Thanks.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270593

Your query is fine. The conversions are on constants, rather than on the column. Instead, you need an index. This will probably help:

create index idx_table_column1_column2 on table(column1, column2);

This is a covering index, so only the index will be used to satisfy the query.

Upvotes: 2

Related Questions