Before we begin talking about performance issues, we first need to define what a stored procedure is. A stored procedure consists of one or more SQL statements that perform a unit of work, that are compiled into an execution plan when executed. An execution plan contains two parts; one part that contains the code to be executed, and one part that contains the variables and parameters that are to be used by the code.
Stored procedures provide a way to share code, in much the same way that a server component can be shared among multiple programs and applications. Multiple users and programs can execute the same stored procedures, thus providing code reuse. Stored procedure maintenance becomes easier because we can change one stored procedure and it immediately becomes effective for all users and programs.
Stored procedures are more efficient than SQL statements, when executed from a program. The reason why this is true is that when the stored procedure is created and saved it is compiled. During the compilation process, SQL Server creates and optimizes an execution plan for the stored procedure. Also, once a stored procedure is executed, it is placed in SQL Server cache. This means that subsequent executions are executed from cache, which also provides improved performance.
SQL statements executed from your programs, however, are not as efficient. Each time the SQL statements are executed by your program, SQL Server must compile and optimize them before they actually get executed. While this performance decrease is not that noticeable with small numbers of SQL statements, or on your personal computer running the Desktop edition of SQL Server, it becomes more evident on large scale SQL Server systems with multiple databases and hundreds of concurrent clients.
If you must execute SQL statements from your program, and
you are executing the same SQL statements in a loop, then ADO can help. The
ADO Command object provides
a property called Prepared
which, when set to True, will instruct
SQL Server to compile and save a copy of your SQL statements in cache. After
you execute the SQL statements the first time, subsequent executions are executed
from the SQL Server cache. While this will help subsequent executions
of your SQL statements, you still take a performance hit on the first
execution.
As you can see from this scenario, you can gain some benefits if you use ADO to help you to optimize your SQL statements. Using stored procedures, however, will provide the greatest performance benefit overall. We'll be covering this subject of performance comparisons in much more depth in the next chapter – for now let's take a look at how we actually create stored procedures.
Comments