Cardinality Estimation Issue? Datetime parameters vs cast?

Question

I am trouble shooting poor performance for the query I have posted below.

Here is my problem:

If I run the exact query below in sql server managment studio or using .NET sqlclient the query takes an average of 14 seconds to run.

However, if in .NET I take out this part of the query:

DECLARE @time_diff INT
DECLARE @start_date DATETIME
DECLARE @end_date DATETIME

SET @time_diff = 2
SET @start_date = '05/04/11 00:00:00 AM'
SET @end_date = '5/4/2011 11:59:59 PM'

and use parameters in code like this:

sqlParameter = sqlCmd.Parameters.Add(New SqlParameter("@start_date", System.Data.SqlDbType.DateTime))
sqlParameter.Value = parameters.StartDate
sqlParameter = sqlCmd.Parameters.Add(New SqlParameter("@end_date", System.Data.SqlDbType.DateTime))
sqlParameter.Value = parameters.EndDate

then the query takes about 2 - 3 minutes to run.

---

Also, I have noticed that it takes about 2 - 3 minutes to run, if i replace the parameter values with string date constants and run in .NET or SSMS

--AND v.call_start_time BETWEEN @start_date AND @end_date
AND v.call_start_time BETWEEN '05/04/11' AND '5/4/2011 11:59:59 PM'

I found this article http://msdn.microsoft.com/en-us/library/ms175933%28SQL.90%29.aspx on cardinality and the query optimizer and I think it has to do with my issue but it doesn't seem to make sense in my case. According to the article, casts should be executed when determining cardinality. In my case this doesn't appear to be happening. The article also says to use parameters in place of local variables - which isn't working well with .NET parameters either.

Regarding using a stored procedure instead: I have a variable number of parameters, I don't know how well it would work out. Even if this is the only option, then i would still like to know exactly what the problem is.

The query:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO ON

DECLARE @time_diff INT
DECLARE @start_date DATETIME
DECLARE @end_date DATETIME

SET @time_diff = 2
SET @start_date = '05/04/11 00:00:00 AM'
SET @end_date = '5/4/2011 11:59:59 PM'

--INSERT QUERY
SELECT * INTO ##tmp_15 FROM (-- PRI CALL RECORDINGS SEARCH QUERY:
    SELECT DISTINCT
    v.call_recording_id,
    v.call_start_time,
    v.call_source,
    v.call_type,
    IsNull(v.phone, '') AS phone,
    d.call_duration_seconds AS pri_call_duration_seconds,
    IsNull(cdr.extension, 'N/A') AS pri_extension,
    IsNull(users.last_name, 'N/A') AS pri_last_name,
    IsNull(users.first_name, 'N/A') AS pri_first_name,
    NULL AS debtor_no,
    NULL AS d_extension,
    NULL AS d_last_name,
    NULL AS d_first_name
    FROM tbl_call_recordings AS v
    JOIN tbl_pri_call_details AS d ON v.call_recording_id = d.call_recording_id
    LEFT JOIN (
        SELECT extension, phone, call_start_time
        FROM tbl_pri_cdr_records
        WHERE is_discard = 0
    ) AS cdr ON v.phone = cdr.phone AND
        ABS(DATEDIFF(mi, v.call_start_time, cdr.call_start_time)) <= @time_diff

    -- MATCH RECORDS TO USER INFO VIA EXTENSION
     LEFT JOIN (
        SELECT extension,
               start_date,
               IsNull(end_date, GETDATE()) AS end_date,
               usr.user_id,
               last_name,
               first_name,
              cr_user_id
        FROM tbl_extensions AS ext
        JOIN tbl_extension_users AS ext_usr ON ext.id = ext_usr.extension_id
        JOIN tbl_users AS usr ON ext_usr.user_id = usr.user_id
    ) AS users ON cdr.extension = users.extension
    AND v.call_start_time BETWEEN users.start_date AND users.end_date
    WHERE 1 = 1

    -- INSERT PRI SEARCH CONSTRAINTS HERE:
    AND v.call_start_time BETWEEN @start_date AND @end_date
    --AND v.call_start_time BETWEEN '05/04/11' AND '5/4/2011 11:59:59 PM'

UNION

-- DIALER RECORDINGS SEARCH QUERY:
      SELECT
        v.call_recording_id,
        v.call_start_time,
        v.call_source,
        v.call_type,
        IsNull(v.phone, '') AS phone,
        NULL AS pri_call_duration_seconds,
        NULL AS pri_extension,
        NULL AS pri_last_name,
        NULL AS pri_first_name,
       d.debtor_no,
        IsNull(users.extension, 'N/A') AS extension,
        IsNull(users.last_name, 'N/A') AS last_name,
        IsNull(users.first_name, 'N/A') AS first_name
      FROM tbl_call_recordings AS v
      JOIN tbl_dialer_call_details AS d ON v.call_recording_id = d.call_recording_id

      -- MATCH RECORDS TO USER INFO VIA EXTENSION
      LEFT JOIN (
                SELECT extension,
                       start_date,
                       IsNull(end_date, GETDATE()) AS end_date,
                       last_name,
                       first_name,
                       cr_user_id
                FROM tbl_extensions AS ext
                JOIN tbl_extension_users AS ext_usr ON ext.id = ext_usr.extension_id
                JOIN tbl_users AS usr ON ext_usr.user_id = usr.user_id

      ) as users ON d.agent = users.cr_user_id
        AND v.call_start_time BETWEEN users.start_date AND users.end_date
      WHERE 1 = 1
      -- INSERT DIALER SEARCH CONSTRAINTS HERE:
    AND v.call_start_time BETWEEN @start_date AND @end_date
    --AND v.call_start_time BETWEEN '05/04/11' AND '5/5/2011'
)t

Answer 1

It seems that changes to the data since the statistics were last updated have probably had a disproportionate impact upon the range under current consideration meaning that the statistics are down right misleading at the moment and need to be manually updated.

A good article discussing this issue is here.