Comparing datetime fields results in slow query performance

Hi,
I am currently deduping a data warehouse containing around 3 million
records. Most of my dedupe scripts run in 3 or 4 minutes but the
scripts which compare datetime fields take up to 3 hours. I have a
non-clustered index on the date column. Can anybody offer any advice on
how I might improve query times please?
Thanks,
Charlie.which version of SQL Server? (2000 or 2005)
what the index plan says?
what type of comparison do you do?
what the index tunning wizard says against your query?
comparing date/time is slower then comparing integer.
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegroups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>|||In addition to the previous post, my guess is you use the columns inside an
expression, so they are not Searchable ARGuments (SARGs) anymore. Try to
rewrite the queries to have the indexed datetime columns without an
expression in the Where clause. For example:
create table a
(a datetime)
create index aa on a(a)
insert into a values ('2006-01-16')
insert into a values ('2006-01-17')
go
select a from a
where datediff(dd,a,getdate()) < 2 -- this query should do an index scan
select a from a
where dateadd(dd,-1,convert(char(10),getdate(),112)) = a -- this query
should do an index seek
However, take care you get correct results - don't forget that you always
have time part in the datetime data. You should check if the Between
operator would be useful for you.
Dejan Sarka, SQL Server MVP
Mentor, www.SolidQualityLearning.com
Anything written in this message represents solely the point of view of the
sender.
This message does not imply endorsement from Solid Quality Learning, and it
does not represent the point of view of Solid Quality Learning or any other
person, company or institution mentioned in this message
<chairleg@.gmail.com> wrote in message
news:1137577249.061546.244550@.g43g2000cwa.googlegroups.com...
> Hi,
> I am currently deduping a data warehouse containing around 3 million
> records. Most of my dedupe scripts run in 3 or 4 minutes but the
> scripts which compare datetime fields take up to 3 hours. I have a
> non-clustered index on the date column. Can anybody offer any advice on
> how I might improve query times please?
> Thanks,
> Charlie.
>