CREATE PROC … WITH RECOMPILE

To be on the safe side, you could always run the procedure with EXECUTE … WITH RECOMPILE. The problem is that you lose one of the principal benefits of stored procedures (though there are many others). Of course, all of those new plans will be hanging around in memory, clogging up the procedure cache and often damaging performance.

If you are planning to recompile every time you run, you should instead flag the procedure itself to recompile every time by creating the procedure WITH RECOMPILE. In this example, notice how the compilation instruction comes between the parameter list and as:

create   proc   test_execution
           (@title char(12))
with   recompile
a s
select   count(*)
    from   titles
  where   title   =   @title
return   0
go

After the first time you run this procedure, SQL Server will prepare a new optimization plan every time the procedure is executed.

How to Avoid Optimization Problems

It should be clear to you now that you need to think hard about procedure execution and optimization issues. Let’s develop some guidelines that will help you plan your stored procedures.

The most important step is to determine whether your stored procedure is well-suited to our more refined understanding of optimization. A lot of problems can be solved with better code and some ingenious solutions. Let’s look at a couple of problems and solutions.

Multipurpose procedures are notoriously bad. Here’s an example of a simple one:

-- DON’T DO THIS … REALLY BAD CODE!!!
create proc a_proc
(@action char(1),
 @col1 int,
 @col2 int)
as
if @action = “I”
   insert a (col1, col2) values (@col1, @col2)
else if @action = “U”
   update a
   set col1 = @col1
   where col2 = @col2
else if @action = “D”
   delete a
   where col2 = @col2
return

The first value passed for col2, even if it’s passed for an INSERT, will determine the optimization of all the data modification statements. If the parameter value @col2 is null for an INSERT procedure the first time this is run, the UPDATE statement will be optimized for a null value, even if in practice it is never run that way.

Okay, but what if it’s way too late? Your application is already written, and it accesses a master procedure for each table. It happens all the time. Luckily, there is an easy solution. Simply modify the procedure to call a subprocedure for each distinct logical unit of work:

create proc a_proc_ins
(@col1 int,
 @col2 int)
as
insert a (col1, col2) values (@col1, @col2)
return
create proc a_proc_upd
(@col1 int,
 @col2 int)
as
update a
   set col1 = @col1
   where col2 = @col2
return
create proc a_proc_del
(@col1 int,
 @col2 int)
as
delete a
where col2 = @col2
return
-- GOOD WORKAROUND IF YOU WERE STUCK WITH THE PREVIOUS CODE
create proc a_proc
(@action char(1),
 @col1 int,
 @col2 int)
as
if @action = “I”
   exec a_proc_ins (@col1, @col2)
else if @action = “U”
   exec a_proc_upd (@col1, @col2)
else if @action = “D”
   exec a_proc_del (@col1, @col2)
return

This suite of procedure and subprocedures solves the compilation problem and runs far more efficiently.

Table 17.2 shows a couple of other circumstances that cause compilation problems.