Advantages and disadvantages of using ad hoc

Ad hoc is translated as "ad hoc query ". That is, you use osql or sqlcmd instead of using the remote process call reference as a language event to submit the transact-SQL. In other words, you use the SSMs command window orProgramThe SQL statements directly sent after splicing are all ad hoc. After declaring that this does not include the SQL statements that are executed through parameters.

In addition to SQL injection that we are familiar with when using ad hoc, it brings additional network overhead and is not good at implementing hierarchical development. When you reference more than two tables in the ad hoc that you splice in the program, additional compilation overhead will be incurred. The following is an example:

Code
-- 1. Clear the cache for observation.
DBCC Freeproccache;
Go

--2. Current cached object
SelectUsecounts, cacheobjtype, objtype,Text 
FromSYS. dm_exec_cached_plans
CrossApply SYS. dm_exec_ SQL _text (plan_handle)
Go

-- 3. Execute ad hoc statements that reference one table and two tables respectively.
Select   *
From Sales. salesorderheader
Where Salesorderid = 44501
Go
-- Change the first letter of the table name to lowercase, as long as it matches the original Cache
-- Any inconsistency will be executed as a new statement. Therefore
-- Generates another parameterized execution plan.
Select   *
From Sales. salesorderheader
Where Salesorderid = 44501
Go
Select   *
From Sales. salesorderheader o
Join Sales. salesorderdetail OD
On O. salesorderid = OD. salesorderid
Where O. salesorderid = 44501

Then we runCodeView the cache and get the following results:

As shown in row 2nd, the query linking two tables does not have a corresponding parameterized execution plan. Rows 4th and 6th correspond to the queries that reference only one table. Because the case sensitivity of the table is different, an execution plan is generated respectively. Continue to run the following code:

Code
-- 4. Change the parameters and view the tracking result.
Select   *
From Sales. salesorderheader
Where Salesorderid = 43860
Go
Select   *
From Sales. salesorderheader o
Join Sales. salesorderdetail OD
On O. salesorderid = OD. salesorderid
Where O. salesorderid = 43860

If Code 2 is run again to query the cache code, the following result is obtained:

We can see the first field shown in row 8th, and this cache plan is used twice.

The tracking result is as follows, indicating that the original cache plan is used:

If a stored procedure is used, the scheduler cache cannot be used because of parameterization. However, the scheduler cache of the stored procedure may cause the optimizer to select an incorrect execution plan because no re-compilation is executed. I have already discussed this. Some people may think that compiling such a statement takes up more time and resources, but as the number of users increases, these time overhead and network overhead cannot be ignored.

However, it is reasonable to use ad hoc in some cases. For example, if a stored procedure is used during the update operation on a table, all the field values are generally passed in, and all the fields are updated no matter whether they are updated or not. If the customer only updates a field, in addition to unnecessary bytes transmission, This method also brings about unnecessary field updates. Especially when the table you want to update has a max field, the situation is even more obvious. If you stick to the stored procedure, the only way is to use the instead trigger to determine which fields are updated and then update only the modified fields. However, this process is quite complicated, and we do not recommend using triggers. In this case, it is the application of ad hoc. In the program, you can easily determine which values have changed, and then splice the updated fields into an SQL statement and send it back to the server, this avoids extra network transmission overhead (especially if the max type is available but the user does not modify this field ). At the same time, you still have fewer operations on fields without updating in the update statement, and the performance is naturally more efficient than using the stored procedure. You may have a lot of dynamically spliced SQL statements in the program. If you want to execute dynamic queries during the process, you generally need to use with recomplie to avoid incorrect execution plans. In addition, I think there is no need to use ad hoc in the program. After reading about LINQ, the system usage and performance cannot meet our requirements at the same time.

there is an option parameterization in the alter database definition. You can force parameterization of all ad hoc statements. However, this is generally not recommended. For more information, see forced parameterization .