How does DateTime.Now affect query plan caching in SQL Server?

Question

Question:

Does passing DateTime.Now as a parameter to a proc prevent SQL Server from caching the query plan? If so, then is the web app missing out on huge performance gains?

Possible Solution:

I thought DateTime.Today.AddDays(1) would be a possible solution. It would pass the same end-date to the sql proc (per day). And the user would still get the latest data. Please speak to this as well.

Given Example:

Let's say we have a stored procedure. It reports data back to a user on a webpage. The user can set a date range. If the user sets today's date as the "end date," which includes today's data, the web app passes DateTime.Now to the sql proc.

Let's say that one user runs a report--5/1/2010 to now--over and over several times. On the webpage, the user sees 5/1/2010 to 5/4/2010. But the web app passes DateTime.Now to the sql proc as the end date. So, the end date in the proc will always be different, although the user is querying a similar date range.

Assume the number of records in the table and number of users are large. So any performance gains matter. Hence the importance of the question.

Example proc and execution (if that helps to understand):

CREATE PROCEDURE GetFooData
    @StartDate datetime
    @EndDate datetime
AS

    SELECT *
    FROM Foo
    WHERE LogDate >= @StartDate
    AND LogDate < @EndDate

Here's a sample execution using DateTime.Now:

EXEC GetFooData '2010-05-01', '2010-05-04 15:41:27' -- passed in DateTime.Now

Here's a sample execution using DateTime.Today.AddDays(1)

EXEC GetFooData '2010-05-01', '2010-05-05' -- passed in DateTime.Today.AddDays(1)

The same data is returned for both procs, since the current time is: 2010-05-04 15:41:27.

Answer 1

The query plan will be cached regardless of parameter values. Parameters basically guarantee that a consistent, reusable query exists, since they are type-safe as far as SQL server is concerned.

What you want is not query plan, but result caching. And this will be affected by the behavior you describe.

Since you seem to handle whole days only, you can try passing in dates, not datetimes, to minimize different parameter values. Also try caching query results in the application instead of doing a database roundtrip every time.

Answer 2

Because you invoke a stored procedure, not directly a query, then your only query that changes is the actual batch you send to SQL, the EXEC GetFooData '2010-05-01', '2010-05-05' vs. GetFooData '2010-05-01', '2010-05-04 15:41:27'. This is a trivial batch, that will generate a trivial plan. While is true that, from a strict technical point of view, you are loosing some performance, it will be all but unmeasurable. The details why this happes are explained in this response: http://stackoverflow.com/questions/1608522/dynamically-created-sql-vs-parameters-in-sql-server/1608856#1608856

The good news is that by a minor change in your SqlClient invocation code, you'll benefit from even that minor performance improvement mentioned there. Change your SqlCommand code to be an explicit stored procedure invocation:

SqlCommand cmd = new SqlCommand("GetFooData", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@StartDate", dateFrom);
cmd.Parameters.AddWithValue("@EndDate", DateTime.Now);

As a side note, storing localized times in the database is not a very good idea, due to the clients being on different time zones than the server and due to the complications of daylight savings change night. A much better solution is to always store UTC time and simply format it to user's local time in the application.

Answer 3

In your case, you are probably fine if the second parameter is just drifting upward in real time.

However, it is possible to become a victim of parameter sniffing where the first execution (which produces the cached execution plan) is called with parameters which produce a plan which is not typically good for the other parameters normally used (or the data profile changes drastically). The later invocations might use a plan which is sometimes so poor that it won't even complete properly.

If your data profile changes drastically by different choices of parameters, and the execution plan becomes poor for certain choices of parameters, you can mask the parameters into local variables - this will effectively prevent parameter sniffing in SQL Server 2005. There is also the WITH RECOMPILE (either in the SP or in the EXEC - but for heavily called SPs, this is not a viable option) In SQL Server 2008, I would almost always use the OPTIMIZE FOR UNKNOWN which will avoid producing a plan based on parameter sniffing.