Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Datediff versus Convert for datetime comparison (ignoring time part) Expand / Collapse
Author
Message
Posted Tuesday, June 15, 2010 1:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 13, 2013 11:41 PM
Points: 2, Visits: 35
Hello,

In my WHERE clause, I need to compare to 2 datetime columns by ignoring the time part.
I wanted to know whether using the DateDiff function rather than the Convert function would give better query execution performance ?

Which one of the following is better:

1. ... WHERE DateDiff(d, MyDateTime1, MyDateTimeCol2) > 0
2. .. WHERE Convert(DateTime, MyDateTime1, 112) < Convert(DateTime, MyDateTime2, 112)

SQL Server experts, please reply.

Thanks.
Post #937765
Posted Tuesday, June 15, 2010 1:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
Although without testing it I can't quantify it, I think the first would perform better, my rationale is that it should be a fairly simple math operation where only the numbers to the left of the decimal would be evaluated.

To prove the second item won't even work:
DECLARE @test datetime
SET @test = GETDATE()

SELECT CONVERT( datetime, @test, 112 )
GO

I think you'd have to convert it to text to do the comparison and text operations are going to be more expensive..

If I may offer an alternative, can you add a calculated column that filters off the time and is set to persisted, then you could do a direct comparison and the cost of the stripping of the date would occur a single time for each record at the moment of insertion.

CEWII
Post #937775
Posted Tuesday, June 15, 2010 3:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:06 AM
Points: 6,545, Visits: 8,767
To answer the question asked, the DateDiff will be more efficient than the convert. You might want to check out the "Common Date/Time Routines" link in my signature.

However, neither of these will be very efficient in a where clause... neither is SARGable, and neither will be able to utilize an index on the column. The best you can hope for with these is an index scan, though you may also get a table scan or clustered index scan. If this is a large table, just take a lunch break while it's running.

Elliott's suggestion to have a persisted, calculated column would work out here - just add an index to that column, and you'll be able to take care of both problems.



Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #937851
Posted Tuesday, June 15, 2010 3:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 31, 2010 9:46 AM
Points: 274, Visits: 473
From what I've read, the DATEDIFF() method has proven to be more efficient.

Overall, for SQL to use any index, it has to highly selective or a covering index: I suspect that's unlikey here.

Of course you can avoid any manipulation on one of the columns by doing two manipulations on the other one . Then at least one of the columns is SARGable.

I'd be suprised if the overhead of two persisted 8-byte columns is really worth it here, unless you do this type of thing a lot on those columns. Typically a little occasional CPU overhead is much better than permanent I/O overhead.


Scott Pletcher, SQL Server MVP 2008-2010
Post #937863
Posted Tuesday, June 15, 2010 5:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:06 AM
Points: 6,545, Visits: 8,767
scott.pletcher (6/15/2010)
Of course you can avoid any manipulation on one of the columns by doing two manipulations on the other one . Then at least one of the columns is SARGable.


Scott, you've got me curious about this. Just what two manipulations would you perform on one datetime column (with time info) that would prevent performing any manipulations on the other datetime column (with time info)? I don't see how you can do it with just two manipulations.


Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #937883
Posted Tuesday, June 15, 2010 5:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 04, 2013 1:49 PM
Points: 1,104, Visits: 1,174
Overall, for SQL to use any index, it has to highly selective or a covering index: I suspect that's unlikey here.


In a smaller environment you'll see the optimizer frequently do without a given index unless it's really perfect for the job. On the smaller tables the cost of the scan is simply not high enough to justify the use of the index. However, as you are working in a larger environment, the optimizer may even choose to use a non-covering index with less than fantastic selectivity just because the cost of the scan is so enormously high. In this case you could see a key lookup to pick up the uncovered columns.

The big thing is not to guess ... do testing and see what the issues are with the execution plan in your environment. It very well may even differ greatly from dev to test to prod.

Of course you can avoid any manipulation on one of the columns by doing two manipulations on the other one . Then at least one of the columns is SARGable.


Potentially instead of doing a DATEDIFF which includes both columns, you could hypothetically leave the one date column alone and do a comparison with a DATEADD on the other column, but since the other column isn't SARGable you'll still have it looking at each record. In a huge environment this will result in a crushing performance hit unless the result set is otherwise greatly reduced by some other criteria. In a really small environment you shouldn't really notice the inefficiency of date manipulations. So again, it's just up to you to test to figure out what works for your specific environment.


└> bt


Forum Etiquette: How to post data/code on a forum to get the best help
Post #937892
Posted Wednesday, June 16, 2010 7:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 31, 2010 9:46 AM
Points: 274, Visits: 473
However, as you are working in a larger environment, the optimizer may even choose to use a non-covering index with less than fantastic selectivity just because the cost of the scan is so enormously high. In this case you could see a key lookup to pick up the uncovered columns.
...
Potentially instead of doing a DATEDIFF which includes both columns, you could hypothetically leave the one date column alone and do a comparison with a DATEADD on the other column, but since the other column isn't SARGable you'll still have it looking at each record. In a huge environment this will result in a crushing performance hit unless the result set is otherwise greatly reduced by some other criteria.


Isn't that somewhat self-contradictory? Isn't it better to have one of them SARGable than neither? Assuming you already have an index on that one column (if neither column is in any index, don't see how you avoid a scan no matter what you do). I would think the other column would have to be in the index as well, even if just INCLUDEd .... but the optimizer gets better all the time, so I won't try to limit it .

I believe he wanted specific code of how to do the compare leaving one column alone, not just a generic statement.


Scott Pletcher, SQL Server MVP 2008-2010
Post #938182
Posted Wednesday, June 16, 2010 8:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 31, 2010 9:46 AM
Points: 274, Visits: 473
WHERE date_col1 >= DATEADD(DAY, DATEDIFF(DAY, 0, date_col2), 0)
AND date_col1 < DATEADD(DAY, DATEDIFF(DAY, 0, date_col2) + 1, 0)

Or:

WHERE date_col1 BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0, date_col2), 0) AND DATEADD(MS, -3, DATEADD(DAY, DATEDIFF(DAY, 0, date_col2) + 1, 0))



Scott Pletcher, SQL Server MVP 2008-2010
Post #938209
Posted Wednesday, June 16, 2010 10:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, January 03, 2014 3:52 PM
Points: 6,066, Visits: 5,277
I can see how that would work.

It still seems like a lot of operations.

If this is an infrequent or even marginally frequent query it shold probably be ok. If not you might look at my original suggestion.. A lot depends (in my mind) on how often this kind of comparison is done.

CEWII
Post #938376
Posted Wednesday, June 16, 2010 10:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 31, 2010 9:46 AM
Points: 274, Visits: 473
Absolutely right.

If this is a commonly used query, I would probably "bite the bullet" and make sure there was an effective (i.e. time-saving) covering index and that SQL was actually using it for the query (it's easy to forget that step, but just because you know the covering index is there doesn't necessarily mean SQL will use it ).

If it's run once a month, say, you might just let this particular query be a little slow rather than adding an index to the table or otherwise enlarging the table.


Scott Pletcher, SQL Server MVP 2008-2010
Post #938380
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse