"Go" parameterized query

SQL Server generates a query plan when it executes a check statement, caches the query plan in the database, and, if the next time the same SQL statement is executed, leverages the cached execution plan without recompiling the build execution plan.

The use of parameterized queries can improve the reuse rate of query plan and improve the efficiency of execution. Here is an example of SQL Server 2005, which analyzes the parameterization of SQL Server queries.

In SQL Server 2005, you can use the following SQL Server statement to view the cached execution plan:

1 SELECT text 2 3  from sys. Dm_exec_cached_plans 4 5  Cross APPLY sys. dm_exec_sql_text (plan_handle) 6 7 ORDER  by DESC ;

Analyze the execution of the following query code:

1 " SELECT * from t1 where col1 = ' abc ' " ; 2 command. ExecuteNonQuery (); 3 " SELECT * from t1 where col1 = ' BCD ' " ; 4 command. ExecuteNonQuery (); 

Start by emptying the cache with the DBCC freeproccache command, lest the cache of other query plans affect the analysis, then execute the code, and then view the cache with two plans:

SELECT * from t1 where col2 = ' abc '

SELECT * from t1 where col2 = ' BCD '

You can see that SQL Server generated a query plan for two queries, with a count of 1, and no query plans were reused.

But if we use parameterized query methods

1Command.commandtext ="SELECT * from t1 where col1 = @str";2Command. Parameters.Add ("@str","ABC");3 command. ExecuteNonQuery ();4Command. parameters[0]. Value ="BCD";5Command. ExecuteNonQuery (); 

The query plan generated after execution is

(@strnvarchar (3)) Select * from t1 Where col1 = @str

SQL Server generated a parameterized query plan, and the query plan was used 2 times, and SQL Server reused the query plan.

Of course, parameterized queries are not the best, especially for parameterized queries of string types, and SQL Server generates different query plans for parameters of different lengths, such as:

" SELECT * from t1 where col1 = @str " ; command. Parameters.Add ("@str""ABC"); command. ExecuteNonQuery (); command. parameters[0"abcd"; command. ExecuteNonQuery ();  

SQL Server generates two query plans,

(@str nvarchar (4)) Select * from t1 Where col1 = @str

(@str nvarchar (3)) Select * from t1 where col1 = @str,

Therefore, using stored procedures is the best way to reuse a query plan because SQL Server caches the query plan generated by the entire stored procedure and reuses the query plan for the stored procedure, regardless of the incoming parameters.

"Go" parameterized query