Another reason for SQL parametric query to hit the execution plan

1 Overview

The essence of SQL is a series of pseudo-code, which expresses what is done, not what it means. 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:
Copy codeThe Code is as follows:
SELECT cp. usecounts AS 'usage times'
, Objtype AS 'type'
, St. [text] AS 'SQL text'
, Plan_handle AS 'scheduler handler'
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
Copy codeThe Code is as follows:
-- Delete all plans
DBCC FREEPROCCACHE

2.3 Test script (create an employee table and insert 1000 pieces of data to it)
Copy codeThe Code is as follows:
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

Copy codeThe Code is as follows:
String selectshorttext = 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)
Copy codeThe Code is as follows:
String selectshorttext = 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
Copy codeThe Code is as follows:
SqlParameter [] param = {new SqlParameter ("@ name", txtEmployeeName. Text. Trim ())};
String selectshorttext = 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.