Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Blogs

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

  • Comments 3
  • Likes

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
  • Frequent visitors might recall a thread earlier in the spring regarding optional parameters to a stored...

  • Well, the current leg of the great "optional parameter code-off" has been completed.  You definitely...

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment