Query that worked for months now timesout

Question

I have a query run from a Delphi application that suddenly started timing out last week after running perfectly for months. further more when it runs it slows the server to a crawl leaving other users believe the system has crash Running from the Management Studio I stop the query after over 5 minutes of spinning Server id SqlExpress 2008 R2

The now offending query

SELECT     *
FROM        SignelOutilsListeJobs_View4
WHERE     (CreatedDate > (GETDATE() - 365))

To make it interesting here is the time required & rows return when I just change the number of days back. Activity monitor does not seem to show more than the query is running

SELECT     *
FROM        SignelOutilsListeJobs_View4 -- 00.00.02 38882 ROWS

SELECT     *
FROM        SignelOutilsListeJobs_View4
WHERE     (CreatedDate > (GETDATE() - 600)) -- 00.00.02 16217 ROWS


SELECT     *
FROM        SignelOutilsListeJobs_View4
WHERE     (CreatedDate > (GETDATE() - 500)) -- 00.00.02 13013 ROWS


SELECT     *
FROM        SignelOutilsListeJobs_View4
WHERE     (CreatedDate > (GETDATE() - 200)) -- 00.00.12  4118 ROWS

So I am left wondering what is happening here? Any ideas?

Thanks

First guess: a bad query plan has gotten cached for that query. — Tab Alleman, Oct 22 '15 at 13:49
... why not just pre-compute the date limit in your Delphi code, then pass it as query parameter? It may be less error prone, allow to easily re-use the same single statement with various offsets, and also compatible with all DB backends... — Arnaud Bouchez, Oct 22 '15 at 14:45
Not an answer to the performance challenge but you should use DATEADD instead of the math shortcut. It is clear what you are doing and is not relying on default behavior which could change. DATEADD(day, -200, getdate()) — Sean Lange, Oct 22 '15 at 14:54
have you already considered to add an index to the `CreatedDate` column or/and partitioning the table itself? Maybe [this SO question](http://stackoverflow.com/questions/17381875/how-to-improve-performance-for-datetime-filtering-in-sql-server) can give you some good advice — fantaghirocco, Oct 22 '15 at 20:17
Did this morning with same result. I understand that the query may not be optimized but the real issue here is that it returns under 12 seconds with 200 or 600 days back but never does with 365, I've waited over 7 minutes for a result. — Marc, Oct 23 '15 at 14:51

fantaghirocco · Answer 1 · 2015-10-23T07:09:16.873

From GETDATE (Transact-SQL):

Using SWITCHOFFSET with the function GETDATE() can cause the query to run slowly because the query optimizer is unable to obtain accurate cardinality estimates for the GETDATE value. We recommend that you precompute the GETDATE value and then specify that value in the query as shown in the following example. In addition, use the OPTION (RECOMPILE) query hint to force the query optimizer to recompile a query plan the next time the same query is executed. The optimizer will then have accurate cardinality estimates for GETDATE() and will produce a more efficient query plan.

In other words you can try to edit the query as follows:

SELECT *
FROM SignelOutilsListeJobs_View4
WHERE CreatedDate > (GETDATE() - 200) OPTION (RECOMPILE)

As an alternative to the above, you can consider to create an unique clustered index on the view:

CREATE UNIQUE CLUSTERED INDEX SignelOutilsListeJobs_unique_index1
ON SignelOutilsListeJobs_View4 (CreatedDate, <some unique key>)

From the Microsoft TechNet:

I've tried the following three version of the query resulting in the same result, note that I stop the query after waiting a minute before users knock on my door - SELECT * FROM SignelOutilsListeJobs_View4 WHERE (CreatedDate > (GETDATE() - 365)) OPTION (RECOMPILE) - SELECT * FROM SignelOutilsListeJobs_View4 WHERE (CreatedDate > dateadd(day, -365, GETDATE())) OPTION (RECOMPILE) - declare ADATE date -AT CHAR REMOVED FOR POST SET ADATE = GETDATE() - 365 SELECT * FROM SignelOutilsListeJobs_View4 WHERE (CreatedDate > @ADATE) OPTION (RECOMPILE) — Marc, Oct 22 '15 at 19:54

Query that worked for months now timesout

1 Answers1