asked
Date filter - better performance ?
Hi experts, Which one is better performance wise , please ? _od.transaction_datetime between @startDate and @endDate_ OR _AND od.transaction_datetime >= @startDate AND od.transaction_datetime <= dateadd(day,1,@enddate)_
This web site runs off of voting. Please indicate all helpful answers by clicking on the thumbs up next to those answers. If any one answer leads to a solution, please indicate that by clicking on the check mark next to that answer.
Your first will likely be faster by a smidge due to the additional day you are querying in the second query. If you remove that dateadd and the subsequent additional day, then the two will be on par with each other.
Would both yield the same result ? I am a bit concerned because the col contains both date and time instead of date. I would be using _od.transaction_datetime between @startDate and @endDate_
Unfortunately _od.transaction_datetime between @startDate and @endDate_ is not including the data with transaction_datetime = enddate. Any suggestions on the best approach please.
I have checked that we got data for the end date and they were all missing when I did _between_ Problem is that the date also contains time in it. I will give your suggestion a try now. But If I try something like below, will it be any better ? _cast(transaction_datetime as date) between @startdate and @enddate_
If your raw data (transaction_datetime) is of DATETIME data type, first thing you need to do is check if it actually has a TIME part. If it does have TIME, then you need to be careful in setting your WHERE clause. When you use: WHERE transaction_datetime BETWEEN @startDate and @endDate MSSQL will automatically cast @startDate and @endDate to DATETIME data type. This is called "implicit casting". It does this because your raw date (transaction_datetime) is of DATETIME data type. So, lets suppose you want to retrieve data for July 2014. WHERE transaction_datetime BETWEEN @startDate and @endDate ... given your DATE variables becomes... WHERE transaction_datetime BETWEEN 2014-07-01 and 2014-07-31 ... which is implicitly cast to DATETIME as... WHERE transaction_datetime BETWEEN 2014-07-01 00:00:00.000 and 2014-07-31 00:00:00.000 And this is the reason why you will not get a complete result set if your data does have TIME part. Because even if it has a single milisecond (i.e. 2014-07-31 00:00:00.001), then it is greater than 2014-07-31 00:00:00.000 and will therefore be absent in your result set. A few things you can do..... You could actually CAST(transaction_datetime) AS date. That will effectively get rid of the TIME part and will make your BETWEEN work as is. However, it is not the best approach from a performance perspective, because you are performing an unnecessary CAST operation on potentially your entire data. As far as I know, "BETWEEN" versus ">= AND <=" are equivalent. The problem in your code is your "BETWEEN" is not coded properly (because your data has TIME constituent). Your ">= AND <=" IS coded properly because of the addition of DATEADD. Adding a day will fix your end point as follows. WHERE transaction_datetime BETWEEN @startDate and DATEADD(day, 1, @endDate) ... given your DATE variables becomes... WHERE transaction_datetime BETWEEN 2014-07-01 and DATEADD(day, 1, 2014-07-31) ... after adding a day ... WHERE transaction_datetime BETWEEN 2014-07-01 and 2014-08-01 ... which is implicitly cast to DATETIME as... WHERE transaction_datetime BETWEEN 2014-07-01 00:00:00.000 and 2014-08-01 00:00:00.000 The above will produce the exact same result as the ">= and <=" statement. Performance wise, tey are identical as far as I know. However, notice ">= and <=" may not be what you really need. I think your end point should be a "<" as opposed to a "<=". In my opinion, the below would be the best course of action: WHERE transaction_datetime >= @startDate and transaction_datetime < DATEADD(day, 1, @endDate) Or in our illustrative example: WHERE transaction_datetime >= 2014-07-01 00:00:00.000 and transaction_datetime < 2014-08-01 00:00:00.000
Alternatively, this is also ok: WHERE transaction_datetime BETWEEN 2014-07-01 00:00:00.000 and 2014-07-31 23:59:59.997
17 People are following this question.
