So I am trying to create a view that automatically pulls data from the last twelve months (starting from the end of the last month.)

When I run this with a where clause like the following :

WHERE Visit_Date between 'Dec 1 2012' and 'Dec 1 2013'

It will run in a ~1min.

I have some calculations that will automatically create those dates. But when I use them in the where clause the query is still running after 15 minutes.

WHERE Visit_Date between DATEADD(mm,-12,DATEADD(mm,DATEDIFF(mm,12,GETDATE()),0))
                 and Dateadd(dd,-1,DATEADD(mm,DATEDIFF(mm,12,GETDATE()),0))

The query is running on a table with 50+ million records. I am sure there this is a more efficient way to do this. I am guessing what is happening is its running through the Getdate() calculations for every row, which is obviously not ideal.

Any suggestions? Please keep in mind that I am creating a view, and I don't usually write stored procedures or dynamic SQL.

I think @Andriy is probably right (I've also blogged about it) that this is due to a cardinality estimation bug (the dates are reversed when making estimates). You can see more details in KB #2481274, Connect #630583 and the question @Andriy pointed out:

Query runs slow with date expression, but fast with string literal

For something you only change once a month, I think you could consider creating a job that alters the view at the beginning of every month, hard-coding the date range into the view. This might not be a terrible alternative to enabling trace flag 4199, which may or may not be a permanent fix, and may or may not cause other issues if you turn it on globally (as opposed to just for the session that runs this query - again no guarantee it will always make this fast). Here is the kind of process I am thinking about:

CREATE PROCEDURE dbo.AlterThatView
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @end DATE = DATEADD(DAY, 1-DAY(GETDATE()), GETDATE());

  DECLARE @start DATE = DATEADD(MONTH, -12, @end);

  DECLARE @sql NVARCHAR(MAX) = N'ALTER VIEW dbo.ViewName
    AS
      SELECT ...
      WHERE Visit_Date >= ''' + CONVERT(CHAR(8), @start, 112) + '''
        AND Visit_Date <  ''' + CONVERT(CHAR(8), @end,   112) + ''';';

  EXEC sp_executesql @sql;
END
GO

Just create a job that runs, say, a minute after midnight on every 1st of the month, and calls this procedure. You may want to have the job to run a SELECT from the view once too.

Please don't use BETWEEN for date range queries and stop using lazy shorthand for dateparts.

more