1 Overview
The nature of the SQL language is a string of pseudo code that expresses what to do rather than what to do. As in other languages, SQL statements need to be compiled before they can be run, so every SQL needs to be interpreted by the compiler to run (and also to optimize SQL). These steps require running costs, so there is something called an execution plan in the database, and the compiler will store the compiled SQL in the execution plan, and when the same SQL is encountered, the execution plan is invoked directly to execute without the need to compile again.
By understanding the execution plan above, in order to improve the efficiency of database operation, we need to hit execution plan as much as possible, so that we can save the running time.
2 Related SQL
2.1 View all execution plans in the current database:
Copy Code code as follows:
SELECT cp.usecounts as ' use times '
, objtype as ' type '
, St.[text] as ' SQL text '
, plan_handle as ' plan 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 Execution plan
Copy Code code as follows:
--Delete all schedules
DBCC Freeproccache
2.3 Test Script (Create Employee table and insert 1000 data into it)
Copy Code code as follows:
IF EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' [dbo].[ Employee])
DROP TABLE [dbo]. Employee
Go
--Staff table
CREATE TABLE dbo. Employee
(
ID int,
Name nvarchar (50)
);
--Inserting test data
DECLARE @i int=0, @ENDI int=1000;
while (@i< @ENDI)
BEGIN
SET @i+=1;
INSERT dbo. Employee (Id,name) VALUES (@i, ' Shang ' +cast (@i as NVARCHAR (20)));
End;
3 Test Execution Plan
3.1 Execute the Delete all execution plan First, then executes select * from Employee, finally view the execution plan (view execution Plan script in 2.1) the following figure
That is, SQL Server creates an execution plan for each SQL and caches it
3.2 Run Once more Sql:select * from Employee and view execution plan
You can see that this plan is reused for 2, that is, the plan is reused;
3.3 Modify Sql:select * from Employee (add a space after the SELECT) to execute and view the execution plan
The result is a new execution plan that SQL Server considers to be two different SQL statements and establishes the execution plan separately;
4 Reuse execution plan--using parameterized query methods
4.1 Not parameterized SQL
Copy Code code as follows:
String selectcmdtext = String. Format (@ "SELECT * from Employee WHERE name= ' {0} '", "Shang 1");
Sqlhelper.executenonquery (sqlhelper.defaulconnectiontstring, System.Data.CommandType.Text, selectcmdtext, NULL);
To view the execution plan:
that is, when an parameterless SQL is executed, SQL Server needs to convert it into a parameter SQL and execute it. Two implementation plans are needed.
Then execute the following code (the condition of the query changes)
Copy Code code as follows:
String selectcmdtext = String. Format (@ "SELECT * from Employee WHERE name= ' {0} '", "Shang 2");
Sqlhelper.executenonquery (sqlhelper.defaulconnectiontstring, System.Data.CommandType.Text, selectcmdtext, NULL);
View Execution Plan
There is no need to prepare a prepared SQL at this time, but still need to generate an execution plan and cache it;
4.2 Parameterized SQL
Copy Code code as follows:
sqlparameter[] param = {new SqlParameter ("@name", TxtEmployeeName.Text.Trim ())};
String selectcmdtext = String. Format (@ "SELECT * from Employee WHERE name= @name");
Sqlhelper.executenonquery (sqlhelper.defaulconnectiontstring, System.Data.CommandType.Text, Selectcmdtext, param);
Enter parameters and execute, and then view the execution plan:
Only need one to prepare SQL, then, enter different parameters, and execute, and then view the execution plan
Reuse execution plan, perfect ...
5 Summary
Generally, when an SQL statement is executed and the execution plan is generated and cached, 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, and parameterized queries not only prevent SQL injection, but also increase the number of execution plans used in the cache.
