How Does Dateadd Impact the Performance of a SQL Q

问题:

Say for instance I'm joining on a number table to perform some operation between two dates in a subquery, like so:

select n
      ,(select avg(col1)
          from table1
         where timestamp between dateadd(minute, 15*n, @ArbitraryDate) 
                             and dateadd(minute, 15*(n+1), @ArbitraryDate))
  from numbers
 where n < 1200

Would the query perform better if I, say, constructed the date from concatenating varchars than using the dateadd function?

回答1:

Keeping data in the datetime format using DATEADD is most likely to be quicker

Check this question: Most efficient way in SQL Server to get date from date+time?

The accepted answer (not me!) demonstrates DATEADD over string conversions. I've seen another too many years ago that showed the same

回答2:

Be careful with between and dates, take a look at How Does Between Work With Dates In SQL Server?

I once optmized a query to run from over 24 hours to 36 seconds. Just don't use date functions or conversions on the column , see here: Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code

to see what query performs better, execute both queries and look at execution plans, you can also use statistics io and statistics time to get how many reads and the time it took to execute the queries

回答3:

I would NOT go with concatenating varchars.

DateAdd will def be better performace than string contatenation, and casting to DATETIME.

As always, you best bet would be to profile the 2 options, and determine the best result, as no DB is specified.

回答4:

most likely there will be no differenfce one way or another. I would run this:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

followed by both variants of your query, so that you see and compare real execution costs.

回答5:

As long as your predicate calculations do not include references to the columns of the table you're querying, your approach shouldn't matter either way (go for clarity).

If you were to include something from Table1 in the calculation, though, I'd watch out for table scans or covering index scans as it may no longer be sargable.

In any case, check (or post!) the execution plan to confirm.

回答6:

Why would you ever use a correlated subquery to begin with? That's going to slow you up far more than dateadd. They are like cursors, they work row by row. Will something like this work?

 select n.n , avgcol1   
    from numbers n 
    left outer join  
        (
        select avg(col1) as avgcol1, n
        from table1 
        where timestamp between dateadd(minute, 15*n, @ArbitraryDate)  
           and dateadd(minute, 15*(n+1), @ArbitraryDate)
        Group by n
        ) t
     on n.n = t.n
    where n < 1200