SQL Server optional parameter performance issue

Question

Hello guys,

I have an optional parameter in a SP e.g.

@MyParam int = NULL

When I check the param against NULL upfront I end up with TWO queries e.g.

IF (@MyParam IS NULL)
	BEGIN
		SELECT ...
	END
ELSE 
	BEGIN
		SELECT ...
	END

Otherwise if I check the param in the WHERE CLAUSE I end up with some performance issue.

SELECT Id, Blah FROM MyTable WHERE (MyField = @MyParam OR @MyParam IS NULL);

Is there a better way to use the optional parameter avoiding redundancy (in the first case) and to prevent the performance hit (in the second case) ??

Thank you so much

Answer 1

Not sure what perf issue you're running into with that query. It seems like it is going to run just fine. One thing that comes to mind is character sets. We've seen perf issues in queries where the DB is using VARCHAR and we are passing parameters as NVARCHAR. The DB has to do a conversion which, for large tables is slow (relative).

You could try rewriting your query to this.

WHERE MyField = COALESCE(@MyParam, MyField)

If MyParam is null then it compares MyField to itself. Of course if that is a nullable field then it isn't going to work so well. Alternatively ISNULL could also be used but I don't know that there is a perf difference.

Answer 2

As Viorel says, OPTION (RECOMPILE) is a simple solution.

For a longer discussion, you may be interested in my article Dynamic Search Conditions. If you prefer to watch videos, I also a presentation of the topic.

Answer 3

in general the query optimizer is pretty good at optimizing

(@MyParam IS NULL OR MyField = @MyParam)

but if the query has too many joins, it may not work. also convert, isnull() or coalesce(), trim(), etc of a column should be avoid as they generally force scans rather than scan seeks.

note: performance may be better if MyField is not nullable. (remember MyField = null is always false even if MyField is null).

Answer 4

Hi @Kuler Master

At the end of the SELECT, INSERT, UPDATE, and DELETE statements, you can add an Option(<query_hint>) clause to guide the execution plan to be generated by SQL Server.

In this way, you can guide SQL Server to generate a relatively safe execution plan with good performance for all possible variable values by adding hints.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".