Procedures, Parameters, and the Optimizer

The stored procedure is a very powerful tool and if you follow a few rules, it will perform very fast and efficiently.

Rule 1: Do not change parameters that are SARGS.

Rule2: If you must break Rule 1, split your procedures.

Let's create a procedure to use in our examples:

procs_params_optimizer_blog.png

A little pubishing industry background.  ISBN10 was a 10 character identifier used for all books.  The industry started running out of available ISBNS, so a new ISBN13 was introduced.  There is a formula to convert between ISBN10 & ISBN13 and many legacy applications still rely upon ISBN10.  Now let's dig into RULE1.

There are two input parameters for this procedure:@ISBN and @discount, but only 1 of them is a SARG.  The statement below breaks the first rule by modifying the parameter that is a SARG: @ISBN.  It does this in the following sql:

SELECT @ISBN=

   dbo.udf_CnvISBN13_to_ISBN10(@ISBN)

Why does this matter?  The optimizer must first calculate all of the query plans prior to executing the procedure itself, so the above sql statement happens AFTER the plans have been determined.  If I pass in the ISBN= '9780486438511', the optimizer will look to use that value to join to Book.ISBN.  The optimizer chooses the query plan, runs and then wham!  You are now looking for ISBN='0486438511'and the optimizer most likely picked a wrong plan, which will lead to poor performance.

You notice that I have mentioned nothing about the modification of @discount.  That's because it is not used as a SARG and the optimizer isn't going to use it in determining the query plans.

If due to certain restrictions the application is passing in an ISBN3 and you must convert it to an ISBN10, you use RULE 2

procs_params_optimizer_inner_blog.png

As you can see from the above image, I have broken the stored procedure into two different procedures.  The first procedure merely does the conversion of the ISBN and override of the discount before passing it on to the 'inner' procedure.  When the inner procedure is executed, the optimzer will have the correct information and will grab the most efficient query plan, based upon the data passed in.