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

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: Dynamically created SQL vs Parameters in SQL Server

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.