0

Which method is most efficient when comparing PARTS of date/datetime values? Example for comparing month of datetimes:

where insdate =DATEADD(month, DATEDIFF(month, 0, @insdate), 0)

or

where year(insdate)=year(@insdate) and month(insdate)=month(@insdate)

I'm using sql server

George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • Tag the dbms you're using. (DATETIME and DATEDIFF are both product specific functions.) – jarlh Jun 07 '17 at 08:05
  • How can I edit tags after my post? – George Menoutis Jun 07 '17 at 08:51
  • It's easy. Click "edit", add another tag, click "save". – jarlh Jun 07 '17 at 08:59
  • Use whatever reads "cleanest" to you. If this query is identified as an actual performance hotspot, *measure* different approaches and see if *any* of them meet your requirements. You cannot learn to write "performant" SQL (or any other language, generally) by trying to learn thousands of rules of the form "always do X instead of Y". – Damien_The_Unbeliever Jun 07 '17 at 09:10

2 Answers2

1

I disagree with Damien_The_Unbeliever's assertion that you should just use whichever reads cleaner, as there are objective reasons why one approach will be better than the other. The most pertinent of these is what is known as SARGability.

In essence this refers to whether SQL Server can use your values in the efficient manners it is designed to do, such as utilising indexes.

The differences in your two examples are nicely outlines here.

In short, if you have functions or calculated values on both sides of your equality conditions, SQL Server is definitely going to have to check every single value returned, whereas if you apply the principles of SARGability from the off, even if you don't see any significant benefits immediately you are at least in a better position to realise those benefits later on if required.

iamdave
  • 12,023
  • 3
  • 24
  • 53
  • I am struggling to understand both the contents of your link and what you mean after the "whereas". To my understanding, you are saying to me something like "try to use sargable things as often as possible, although in this specific instance it will probably not help". I understand how that answer covers the index use aspect of the performance question. How about the per-row calculations of datediff,dateadd, and year/month()? It seems to me that the algorithms of the former are far more calculated from the ones of the latter, which are like "crops". Still, I'm only speculating, thus I ask. – George Menoutis Jun 07 '17 at 13:00
  • @GeorgeMenoutis Ah, I see what you are getting at. Basically, the differences in computation time between the various functions you could choose will have an inconsequential impact on your query run-time compared to properly setting your query up to use the built in optimisations of the SQL Engine. If you are in a situation where thousandths of a second of overall query execution time is important, you should be testing every possible way of writing your query anyway so this discussion is moot. – iamdave Jun 07 '17 at 14:01
  • @GeorgeMenoutis Bookmark (and read) Tibor's website where he discusses this and other important aspects of tsql. http://www.karaszi.com/sqlserver/info_datetime.asp#Searching – SMor Jun 07 '17 at 14:45
  • @SMor The question being asked is not about how to use these functions, but how well they perform in relation to one another. Your link does not provide any information in that regard. – iamdave Jun 07 '17 at 14:48
  • @iamdave If you apply a function to a column, you prevent the engine from using useful indexes - sargability in other words. And given that the 2 examples posted by OP are **NOT** logically equivalent, comparing their "performance" is basically pointless. Tibor explains how to search using datetime values with useful examples and with explanations about performance impacts. – SMor Jun 07 '17 at 15:25
  • Smor, I believe the two examples I gave are logically equivelant. If not, please tell me why, but I searched for those. – George Menoutis Jun 07 '17 at 20:31
1

In my opinion, the best way to implement Year or YearMonth check is to cast date in this format YYYYMMDD and then work with that.

This is an example:

Filter by YearMonthDay

SELECT * FROM myTable 
WHERE CONVERT(VARCHAR,MyField,112) = 20170607

Filter by YearMonth

SELECT * FROM myTable 
WHERE CONVERT(VARCHAR,MyField,112) / 100 = 201706

Filter by Year

SELECT * FROM myTable 
WHERE CONVERT(VARCHAR,MyField,112) / 10000 = 2017

For sure this perfomrs better than using Year() ,Month() , DateAdd(), DateDiff() functions.

DarioN1
  • 2,460
  • 7
  • 32
  • 67
  • 1
    Why 'for sure'? I come from a low-level computer designing field, and when I see the year() etc functions, I understand "cut off the rest", which could probably be just an AND operation with a mask, which is a single cpu instruction. On the contrary, converting between types invokes algorithms with multiple instructions. Maybe I am out of focus using level 1 language logic on a level 5 one that is sql, but that's how I think it goes. Please tell me where I should think otherwise. – George Menoutis Jun 07 '17 at 13:20
  • @GeorgeMenoutis this is completely true if you work with low level, but SQL Server is an relational engine and implements different low level logic... – DarioN1 Jul 03 '20 at 10:39