Tags: MSSQL , Prepared Statement

Database prepares execution plan(steps) for each query fired on it. Preparing execution plan for each query will increase execution cost. To reduce cost on creating execution plan for each query database provides Prepared Statements.

Prepared Statement
Prepared Statements are pre-cached queries stored in database. Prepared Statement used when same query used with different values. Preparing execution plan in advance for particular query will increase query performance.

When, Prepared Statement?
Multiple occurrence of same query with different values will lead to Prepared Statement. Lets understand following queries...

/* Database will prepare two execution plan for following queries */
SELECT * FROM user_master WHERE user_name = 'vicky.thakor'
SELECT * FROM user_master WHERE user_name = 'chirag.thakor'

/* Prepared Statement */
SELECT * FROM user_master WHERE user_name = :param1

- First two queries are simple SQL statement, both are same except its value. For this queries database will prepare two execution plan.
- Third query will be pre-cached query and can accept n number of values and database don't have to prepare execution plan multiple times. Third query template/Prepared Statement used for above two query.

Rule for execution plan
Database will consider space, comma, special symbol while creating execution plan either its Prepared Statement or Simple SQL Query. Database will prepare different execution plan for following queries which seems similar to us but not for database.

/* Execution Plan 1; Comment is part of query*/
/* Native Query */ SELECT * FROM user_master WHERE user_name = 'vicky.thakor'

/* Execution Plan 2; Comment is part of query, We just changed 'q' in comment */
/* Native query */ SELECT * FROM user_master WHERE user_name = 'vicky.thakor'

/* Execution Plan 3 */
SELECT * FROM user_master WHERE user_name = 'vicky.thakor'

/* Execution Plan 4 (Prepared Statement)*/
SELECT * FROM user_master WHERE user_name = :param1

/* Execution Plan 5 (Prepared Statement); Comment is part of query */
/* Prepared Statement */SELECT * FROM user_master WHERE user_name = :param1

So far you noticed how importance of query writing and Prepared Statement. We all are using Prepared Statement from different programming languages, Program just prepare one of the above Prepared Statement and maintain same query structure for all database execution.

Now we will see how you can use Prepared Statements while writing Stored Procedures, functions in Microsoft SQL Server.

Step 1: Clear all available execution plan/Prepared Statements from cache.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Step 2: We will see the execution plan for simple SQL Query

SELECT * FROM user_master WHERE user_name = 'vicky.thakor'
SELECT * FROM user_master WHERE user_name = 'chirag.thakor'

Step 3: Check your execution plan in cache. You will find two execution plan in it.

SELECT usecounts, size_in_bytes, cacheobjtype, text 
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype != 'Parse Tree'

Step 4: Now again clear all available execution plan/Prepared Statements from cache.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Step 5: Now we will Prepare Statement in Database. Execute following query two times with different values. Change your table name, column name and value.

EXEC sp_executesql
@statement = N'SELECT * FROM user_master WHERE user_name = @param1',
@parameters = N'@param1 varchar(100)',
@param1 = N'vicky.thakor'

Step 6: Check your execution plan in cache. You will notice that same execution plan used twice.

SELECT usecounts, size_in_bytes, cacheobjtype, text 
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype != 'Parse Tree'

Tags: MSSQL , Prepared Statement