Another reason for SQL parameterized query-hit execution plan

1 Overview

The essence of SQL is a series of pseudoCode, Express what is done, not how to do it. Like other languages, SQL statements must be compiled before they can be run. Therefore, each SQL statement must be interpreted by the compiler before it can be run (SQL optimization is required ). These steps all require running costs, so there is a thing in the database called the execution plan, the compiler will save the compiled SQL into the execution plan, when the same SQL, instead of re-compiling.
Through understanding the above execution plan, in order to improve the efficiency of database operation, we need to hit the execution plan as much as possible to save the running time.
2 related SQL

2.1 view all execution plans in the current database:

View code

 Select CP. usecounts As   '  Usage  '  , Objtype  As  '  Type  '  , St.  [  Text  ]   As   '  SQL text  '  , Plan_handle  As   '  Scheduler handle  '          From SYS. dm_exec_cached_plans CP  Cross Apply SYS. dm_exec_ SQL _text (plan_handle) As  St  Where St. Text   Not   Like   '  % Sys %  ' 

2.2 Delete an execution plan

View code

 
--Delete all plansDBCCFreeproccache
 

2.3 Test script (create an employee table and insert 1000 pieces of data to it)

View code

 If    Exists ( Select   *   From SYS. Objects Where   Object_id   =   Object_id (N '  [DBO]. [employee]  '  ))  Drop  Table   [  DBO  ]  . Employee  Go  --  Personnel table  Create   Table  DBO. employee (ID  Int  , Name  Nvarchar ( 50  ));  -- Insert Test Data  Declare   @ I   Int  =  0 , @ Endi   Int  =  1000  ;  While ( @ I  <  @ Endi  )  Begin     Set   @ I  + =  1  ;  Insert DBO. employee (ID, name) Values ( @ I , '  Jiang Dahua  '  +  Cast ( @ I   As   Nvarchar (20  )));  End ;

3. test execution plan

3.1 first Delete All execution plans, then execute select * from employee, and finally view the Execution Plan (view the execution plan script in 2.1) as shown in

That is, SQL Server creates an execution plan for each SQL statement and caches it.

3.2 run SQL again: Select * from employee and view the execution plan

We can see that this plan has been reused for 2 times;

3.3 modify SQL: Select * from employee (add a space after select), execute and view the execution plan

As a result, an execution plan is added, that is, the SQL Server considers two different SQL statements and establishes the execution plan respectively;

4. Reuse the execution plan-using parameterized query methods

4.1 Non-parameterized SQL

StringSelectshorttext =String. Format (@"Select * from employee where name = '{0 }'", "Jiang Dahua 1"); sqlhelper. executenonquery (sqlhelper. defaulconnectiontstring, system. Data. commandtype. Text, selectcmdtext,Null);
 

View the execution plan:

That is, when executing an SQL statement that is not parameterized, SQL Server needs to convert it into a parameter SQL statement and execute it. Two execution plans are required.

Then execute the following code (the query condition has changed)

StringSelectshorttext =String. Format (@"Select * from employee where name = '{0 }'", "Jiang Dahua 2 ");
Sqlhelper. executenonquery (sqlhelper. defaulconnectiontstring, system. Data. commandtype. Text, selectcmdtext,Null);
 

View execution plan

At this time, you do not need to prepare another SQL statement, but you still need to generate another execution plan and cache it;

4.2 parameterized SQL

Sqlparameter [] Param = {NewSqlparameter ("@ Name", Txtemployeename. Text. Trim ())};StringSelectshorttext =String. Format (@"Select * from employee where name = @ name"); Sqlhelper. executenonquery (sqlhelper. defaulconnectiontstring, system. Data. commandtype. Text, selectcmdtext, Param );
 

Enter the parameters and run them. Then, view the execution plan:

You only need to prepare an SQL statement, enter different parameters, execute the statement, and view the execution plan.

Reuse execution plan, perfect...

5. Summary

in general, an execution plan is generated and cached when an SQL statement is executed. We can reduce the pressure on the database by increasing the number of execution plans in the cache. Using parameterized SQL is a good choice. parameterized query not only prevents SQL injection, but also increases the number of execution plans in the cache.