1

I received a claim for an incredible slow query in our print label software. With SQL Profiler I got the offender query and started to test in SSMS with really bad performance (around 00:50:00). The query is long, and the tables are big, but there is a

WHERE 
 ...
 fieldX > {ts '2016-01-01 00:00:00} /* fieldX is a DATETIME */
 ...

I changed to 

WHERE 
 ...
 fieldX > DATEADD(YYYY, -1, GETDATE())
 ...

And the execution time now shows 00:00:00 !!

I just want understand why?

Improve this question
  • 2
    Those two queries are not doing the same thing. – a_horse_with_no_name Apr 11 '17 at 8:36
  • 1
    The change in query string also caused a new plan to be compiled, that might have had an influence too. – Tom V Apr 11 '17 at 8:37
  • @TomV I merged the tables to a new database for test it and the query was changed several times before this point. I got better times before, but -10, -20 seconds, but anything so extreme like jump from 50 to ~0. But of course, I believe the compiled plan play a role in this – HEDMON Apr 11 '17 at 9:24
  • @RDFozz, before I merged the tables to a test environment and got the same results. With your idea to alter the date in the original query I found that selecting the rows after 2016-02-25 disappear the problem. But the last record (before) is from 2016-02-23. At 24 I don't see any record, but if I use 24 in the filter the query come slowly again. Anyway, now is more clear is a problem with the database(/client app) itself and not a SQL issue. Please, can you 'answer' the question? ;) – HEDMON Apr 12 '17 at 10:56
2

I recommended (originally, in the comments) that HEDMON try some different dates. Since his test date and his production dates were different, it seemed like it might be possible that there was a selectivity issue with the dates; that SQL could return the data much faster with a more recent date than with an older one.

HEDMON did try that, and found (from his comments) that dates from 2017-02-25 forward responded quickly, while dates older than that were slow.

With this, he felt comfortable that the presentation of the date (using the {ts } syntax) had nothing to do with the issue.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.