Parameterized query is too slow

Question

I have a complex query in .NET. Please see the query (simple query for explanation purposes) below:

SELECT * FROM Person WHERE Name='Ian' AND DateOfBirth='1961-04-04'

and this:

SELECT * FROM Person WHERE Name=@Ian AND DateOfBirth=@DateOfBirth

The table is indexed (name and date of birth).

The first query takes a fraction of a second to run from .NET. The second query takes about 48 seconds. Is this something to do with the execution plan? Is there anything I can do to force SQL Server to recreate the execution plan?

I have seen this question: https://dba.stackexchange.com/questions/530/how-do-you-clear-out-all-old-query-plans-from-within-microsoft-sql-server. However, this is more for stored procedures.

Answer 1

First, you want a composite index on Person(Name, DateOfBirth), not two indexes (the columns can be in either order).

Second, this probably has to do with the execution plan.

I am going to suggest the RECOMPILE option:

SELECT p.*
FROM Person p
WHERE Name = @Ian AND DateOfBirth = @DateOfBirth
OPTION (RECOMPILE);

What can happen with parameterized queries is that the execution plan is cached the first time it is run -- but that execution plan may not be the best for subsequent invocations.

If that doesn't work, then the problem may be data types, because data type incompatibility can prevent the use of indexes. Be sure that the data types and collations are the same. You might need to cast() the parameters to the appropriate type and/or use COLLATE.