Component Workshop Ltd

This advice applies to Microsoft SQL Server. It might also affect other database servers too.

When you write stored procedures, which are an excellent way of encapsulating functionality and improving performance, you should avoid the use of EXEC and EXECUTE keywords at all costs.

Many developers try and break down larger procedures to create smaller modules. The temptation, from a code-cleanliness point of view, is strong, but as soon as you do this you will cripple the performance of your procedures. Even if your procedure is not critical, you must be aware of the knock-on effects that a long-running query can have on the rest of a database or database server.

The reason that EXEC causes such a slowdown is simple: the server can not plan ahead for queries that are executed as a result of EXEC.

SQL Server can only work so incredibly fast because it can (ordinarily) plan ahead. These plans involve not only choosing between tables versus indexes, but lots of very fine detailed information about the methods it will use to perform each individual component of a query (taking into account ordering, joins and many other elements). Without a plan, it has to do this each time it encounters a query, and it takes a long time.

Well written procedures are good because a plan can be created the first time a stored procedure is called and then cached. That’s why the first time you run a new or modified stored procedure it might take 1 second to run, but subsequently it can return instant results.

By calling EXEC, you are preventing SQL Server from creating a plan for the entire procedure and every single query that it calls, either directly or indirectly.