-1

I executed a SQL query in which I had to select data between FromDate and ToDate.

Earlier I used DATEDIFF function and it took around 8 sec to complete. but when same query I executed with BETWEEN condition (..WHERE TranDate BETWEEN @FromDate AND ToDate); it executed in no time.

What could be the reason for this?

Awadhesh Singh
  • 1
  • 1
  • Welcome toSO. Please tag your question with the db provider. – Uwe Allner Jul 31 '15 at 09:56
  • 1
    It's very difficult to compare two queries when you don't even show us the queries. All we know about one of them is that it "used DATEDIFF". Obviously, if the queries are large, don't try to include the whole thing in the question - don't include 5000 columns in the select list if the same issues arise with one column. – Damien_The_Unbeliever Jul 31 '15 at 09:59
  • 1
    When use used DATEDIFF the optimizer couldn't use an index, because the condition was not "sargable": https://en.wikipedia.org/wiki/Sargable – dnoeth Jul 31 '15 at 10:01
  • 1
    Please include, at least, the two different queries you are executing. The table structure and any/all indexes are also important to know about. – HansLindgren Jul 31 '15 at 10:11
  • I agree with all the others that the question needs more info to be answered. @dnoeth is probably right about the issue, see a [similar question](http://stackoverflow.com/questions/6233319/how-to-improve-performance-of-non-deterministic-function-of-a-column-in-a-where) – ypercubeᵀᴹ Jul 31 '15 at 10:25

1 Answers1

0

It's hard to answer like this with so little information about your database schema and your queries, but I'll give it a try.

The difference between your 2 queries is that in the first one (with DATEDIFF) is not sargable whereas the second one (using BETWEEN is sargable). It is what dnoeth mentionned in the comments.

Why is it important ? Because, as wikipedia says :

In relational databases, a condition in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query.

Ok, so now we understand that being sargable is great because you can take advantage of indexes. But the next question is : why indexes are important and why DATEDIFF would not be sargable ?

First, if you don't know what indexes are I advise you to look here.

Here, I'll use the sqlserver doc on DATEDIFF, so the first argument is startdate and the second is end date. Also, I'll assume you built your query like this, with foo_date being the date of the row you are checking : (DATEDIFF(FromDate, foo_date) > 0) AND (DATEDIFF(foo_date, ToDate) >0)

Why is this not sargable ? Because what is indexed is the values of foo_date, and no the results of the function.

Let's take an example. You are your sql server. You want to find all the rows that match your criteria. And your foo_date field is indexed so you have an ordered list of foo_date.

We tell you BETWEEN FromDate AND ToDate. Well, that's easy, you look at the middle (in your index), you find that the Date is between FromDate AND ToDate, so you look before (index n/4 if n is your number of rows), then you see it's before FromDate so you look between n/4 and n/2, etc. You find quickly the first row matching and you do the same for ToDate and your select all the rows in between. Ok, that was quick. Note that the server probably does not do exactly that but it was to give you the insight on the performance gain given by indexes.

Then, someone tells you (DATEDIFF(FromDate, foo_date) > 0) AND (DATEDIFF(foo_date, ToDate) >0). Ok, so you could think (DATEDIFF(FromDate, foo_date) > 0) means FromDate > foo_date and do the same as before. But what is most likely to happen, is that sqlserver won't understand that and ... will need to check for all rows if this is true. So you need much more calculations so much more time.

I hope with this little example you see that using indexes can greatly influence performance and also why DATEDIFF is not sargable.

I encourage all database experts (which I'm not) to correct the mistakes I have most likely said.

Community
  • 1
  • 1
Pholochtairze
  • 1,836
  • 1
  • 14
  • 18