Compilation and Changes to Indexes

There’s one last compilation consideration with procedures. What happens to optimization plans in memory when you add new indexes to a table? Unfortunately, existing plans will not be optimized when you add indexes. In order to force SQL Server to generate new plans for a particular table, use sp_recompile:

sp_recompile publishers

Each stored procedure and trigger that uses table publishers
will be recompiled the next time it is executed.

Remember to run sp_recompile tables after creating indexes, or you won’t see the effect of new indexes on stored procedures until you restart the server.

On-the-Fly Execution

It’s worth mentioning a special case of stored procedure execution. SQL Server 6.5 introduced on-the-fly execution of a quoted string. This would allow you to construct a SQL query in a batch or procedure, and then execute that batch. Here’s an example:

create proc table_select
(@tablename varchar(30))
as
execute (“select * from “ + @tablename)
return 0
go

The on-the-fly procedure selects all columns and rows from the table named in the parameter. This particular procedure doesn’t provide any performance benefit because optimization of the main query occurs with every execution. On the other hand, you could use this approach to build a complex security system in which a user’s query is evaluated and certain statements are rejected because of an invalid condition or because of other factors, such as the time of day.

Be aware that you will need to include additional error checks when you write systems using the EXECUTE command in this way. Runtime parsing and syntax errors can be introduced, whereas they cannot with a typical stored procedure.

Although it’s a good idea to avoid this approach in interactive application systems, on-the-fly execution is effective for performing scheduled table and database maintenance within a cursor loop. Here’s an example of a batch that runs UPDATE STATISTICS on all the user tables in a database:

declare obj_curs cursor        -- set up the cursor
for
select name
  from sysobjects
 where type = “U”              -- just “user” tables
declare @name varchar(30)      -- variable for the name of the table
open obj_curs                  -- open the cursor
fetch obj_curs into @name      -- fetch the first row
while (@@fetch_status = 0)     -- if a row is found …
begin
  print @name                  -- print the name of the table
  execute (“update statistics “ + @name)
                               -- run update stats
  fetch obj_curs into @name    -- get the next table
end
close obj_curs                 -- close the cursor
deallocate obj_curs            -- remove the cursor from memory

Because UPDATE STATISTICS requires the literal name of a table (not a variable), the EXECUTE statement is the only way to do this. It passes the table name as part of a literal string, for example, update statistics titles. As this is a maintenance batch (not an application-based stored procedure), there won’t be a noticeable performance difference between running this and executing a literal list of UPDATE STATISTICS statements. The benefit is that the list of tables needs no maintenance. Instead, the cursor builds its own loop based on every table in the database.

Summary

Today you’ve learned about procedure performance issues. There are lots of guidelines throughout this chapter, but here are the most important points. Remember to take small bites. Keep your code small and tight. Avoid multipurpose procedures. Instead, use subprocedures to get the best optimization. Be particularly tuned in to the problems caused by temporary tables. Finally, avoid on-the-fly execution in your production systems.