Why do I get a Timeout Expired error running this query?

Question

I'm running this query on SQL Server 2008 using ODBC. If I take out the last part (OR li.DATEGENERATED >= (...))) then I don't get the error... but I can run it in SSMS just fine.

SELECT COUNT(*) FROM dbo.POITEMST li
 WHERE (? IS NULL OR li.DATEGENERATED >= ?)
   AND (? IS NULL OR (li.DATEGENERATED >= (
    SELECT MAX(t.DATEGENERATED) FROM dbo.POITEMST t WHERE t.PONUM = ?)))

Here is how I'm setting up parameters

DbParameter startDateParam = cmd.CreateParameter();
startDateParam.ParameterName = "StartDate";
startDateParam.Value = (StartDate.HasValue ? (object)StartDate.Value : (object)(new DateTime(1753, 2, 2)));
cmd.Parameters.Add(startDateParam);

startDateParam = cmd.CreateParameter();
startDateParam.ParameterName = "StartDate";
startDateParam.Value = (StartDate.HasValue ? (object)StartDate.Value : (object)(new DateTime(1753, 2, 2)));
cmd.Parameters.Add(startDateParam);

DbParameter startPoParam = cmd.CreateParameter();
startPoParam.ParameterName = "StartPO";
startPoParam.DbType = DbType.String;
startPoParam.Value = (string.IsNullOrEmpty(StartPurchaseOrder) ? (object)DBNull.Value : (object)StartPurchaseOrder);
cmd.Parameters.Add(startPoParam);

startPoParam = cmd.CreateParameter();
startPoParam.ParameterName = "StartPO";
startPoParam.DbType = DbType.String;
startPoParam.Value = (string.IsNullOrEmpty(StartPurchaseOrder) ? (object)DBNull.Value : (object)StartPurchaseOrder);
cmd.Parameters.Add(startPoParam);

EDIT: Those dates are only in there because I was trying to figure this out... it was passing in DBNull before if there wasn't a start date.

Answer 1

How long did it take to run in ssms ? If you can run the query with the execution plan enabled (query menu, execution plan -- not the estimated one.) If there is an index issue this should let you know on the execution plan pane.

Can you or your dba run SQL profiler; and capture what is actually being passed into sql server to confirm it matches what you expect.

Answer 2

Hi,

I think you can refactor your query to improve performance:

SELECT COUNT(*) FROM dbo.POITEMST li
WHERE li.DATEGENERATED >= ISNULL(?,'1753-02-02')
AND li.DATEGENERATED >= ISNULL((SELECT MAX(t.DATEGENERATED) FROM dbo.POITEMST t WHERE t.PONUM = ?), '1753-02-02')

Combining comparison against null with OR looks to be part of the problem.

Hope this helps,

Bill

Answer 3

Divide and conquer. Start with T-SQL with hard-coded values, both NULL and non-null. Start with the simplest query and build up, examing the query plan at each step. You may find you have indexing problems, etc. Resolve that then go back to the code.

It's really hard to help much with just a query without more schema info - table layouts, indexes, etc.

Answer 4

Do the following:

1. Refactor the query
2. Make it into a stored procedure
3. Create indexes on the appropriate columns

In my experience, the indexes make a massive difference when experiencing performance differences between SSMS and ODBC of the same query.