Database Programming: Just What Were Those Performance Implications, Anyway?

Last Thursday, when I offered a solution for implementing optional parameters to a stored procedure, I blithely stated, "this approach is not without performance implications," and then let the issue rest. Well, Brian noticed and posted a comment asking for the details. I've responded to him in the comments for that post, but I want to make sure that anybody interested sees it, so here it is..

Hi Brian..

The performance issues are basically around optimization of the query plan.

In the example above, if the cardinality of all combinations of the four parameters is roughly identical (say, in the most obvious case, they're unique) then there should be no performance issues with this construction if you build the recommended index. Each invocation would result in an index seek, which is the best possible outcome in this scenario.

If, however, the cardinality of unique sets of parameters is markedly different (say there is one occurrence of '1,2,3,4' and 100,000 occurrences of '1,2,3,5' among the data), then we might get into trouble if we compile the stored procedure with the '1,2,3,4' parameter set and subsequently run it with the '1,2,3,5' parameter set. In this case, the best approach would most likely be to use the version-appropriate RECOMPILE option. In SQL Server 2000, you'd build the entire stored procedure with the WITH RECOMPILE option. In SQL Server 2005, you'd place the OPTION (RECOMPILE) directive on the SELECT statement.

Please let me know if there's anything further you need. Thanks for your question!

-wp

Comments

• Anonymous
  January 01, 2003
  Frequent visitors might recall a thread earlier in the spring regarding optional parameters to a stored...
• Anonymous
  January 01, 2003
  Well, the current leg of the great "optional parameter code-off" has been completed.  You definitely...