Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Data Warehousing / January 2006

Tip: Looking for answers? Try searching our database.

Comparing datetime fields results in slow query performance

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
chairleg@gmail.com - 18 Jan 2006 09:40 GMT
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.
Jéjé - 18 Jan 2006 18:28 GMT
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.

> Hi,
>
[quoted text clipped - 7 lines]
>
> Charlie.
Dejan Sarka - 18 Jan 2006 21:14 GMT
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.

Signature

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

> Hi,
>
[quoted text clipped - 7 lines]
>
> Charlie.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.