To understand why stored procedures are faster than direct SQL commands,
we need to understand how SQL Server processes the commands that it
receives. When we submit any command for SQL Server to execute, it goes
through the following 4 stages:
1. The command is parsed for syntax. Any commands that are syntactically
incorrect are rejected.
2. The command is then translated into an internal format known as a
sequence tree or query tree.
3. The command is optimized based on estimated performance costs, and an
execution plan is generated from the sequence tree that contains all the
necessary steps to check constraints and carry out the command.
4. The command is executed.
If we send direct SQL statements one at a time from your application, then
each statement requires all four stages for each single statement. Contrary to
common belief, the execution plan is not saved with the stored procedure when
it is created.
For a stored procedure, stages 1 and 2 happen only once, when
we save the stored procedure. Step 3 occurs only if the execution plan is not
already in the memory cache. Subsequent executions only need step 4