I have a dynamic search query that looks like this:
@CustID INTEGER = -2,
@Name NVARCHAR = ''
--more parameters with default values...
Select x, y, z FROM MyTable
WHERE
(@custid = -2 OR CustomerID = @CustID) AND
(Name = '' OR Name = @Name) AND
--more dynamic search conditions....
We've recently been having a lot of trouble with parameter sniffing and it causes major slowdowns, and even timeouts.
1) I first tried setting all my parameters to local variables, at that fixed the parameters sniffing, but the default query (the query with all default parameter values) was taking 5 seconds when before it would take 1 second. No more timeout level executions though.
2) Next I tried using OPTION (OPTIMIZE FOR (@CustID UNKNOWN, @Name UNKNOWN, ...))
, but that essentially does the same thing as using local variables.
3) Last I tried still using OPTION (OPTIMIZE...
, but instead of UNKNOWN
, I used the default value of each parameter: OPTION (OPTIMIZE FOR (@CustID = -2, @Name = '', ...))
. This kept the default search fast (still about 1 second, and it seems all other searches with other values still execute fast.
Is it a good or known practice to to this (#3)?
Best Answer
I would dynamically construct the query such that each combination of search values results in a different plan.
If you are using a stored procedure to do this, it might look something like this.
Do this in the tempdb:
Create a demo table:
Create a stored proc:
Demo searches using the stored proc:
Running the above generates this query (running with @Debug = 0 will actually execute the query):
more examples...
output:
Just by create date:
output:
Search server and database:
output:
Search server, database, and create-date:
output:
This results in clean target queries that only contain the relevant
where
clauses, thereby allowing the SQL Server Query Optimizer to pick a plan that is relevant to the query, and generate multiple plans that can also be cached.This approach could easily be replicated in C#, or whatever client you're using to submit the query.