Ad hoc concepts

Ad hoc is generally called ad hoc lookup. When ad hoc lookup is not clearWikipediaAs follows:

 

Ad hoc allows end users to create specific and custom query requests on their own. Generally, you can query data through a user-friendly graphical interface without having to have a deep understanding of SQL or database architecture.

 

InStackoverflowAs follows:

Ad hocIs Latin for "for this purpose ". you might call it an "on the fly" query, or a "just so" query. it's the kind of SQL query you just loosely type out where you need it var newSqlQuery = "SELECT * FROM table WHERE id = " + myId; ... Which is an entirely different query each time that line of code is executed, depending on the valuemyId. The opposite of an ad hoc query is a predefined query such as a stored procedure, where you have created a single query for the entire generalized purpose of selecting from that table (say ), and pass the ID as a variable.

 

Let the Code explain what ad hoc is. In SSMs, the following query statement is ad hoc query:

 

use AdventureWorks2008R2goSELECT  soh .SalesOrderNumber  ,         sod.ProductID FROM    Sales.SalesOrderHeader  AS soh          INNER  JOIN Sales.SalesOrderDetail  AS sod                 ON soh.SalesOrderID = sod.SalesOrderID  WHERE    soh.SalesOrderNumber  = 'SO43662'  SELECT  soh .SalesOrderNumber  ,         sod.ProductID FROM    Sales.SalesOrderHeader  AS soh          INNER  JOIN Sales.SalesOrderDetail  AS sod                 ON soh.SalesOrderID = sod.SalesOrderID  WHERE    soh.SalesOrderNumber  = 'SO58928' 

This type of hard-code query is usually temporary and has a special purpose. It corresponds to a parameterized query. See the following tsql code:

declare @orderNumber nvarchar(50)SELECT  soh .SalesOrderNumber  ,         sod.ProductID FROM    Sales.SalesOrderHeader  AS soh          INNER  JOIN Sales.SalesOrderDetail  AS sod                 ON soh.SalesOrderID = sod.SalesOrderID  WHERE    soh.SalesOrderNumber  = @orderNumber 

 

This query is a parameterized query, and the generated execution plan can be reused. The execution plan generated by ad hoc cannot be reused. Compile is required each time, consuming a considerable amount of CPU resources, when memory pressure occurs, these execution plans are executed once.

First, it is cleared. To avoid the cost of this situation, there is database-level parameterization option, which allows the system to automatically convert ad hoc queries into parameterized queries and reuse the execution plan.

 

In C # code, how does one write an ad hoc query SQL statement?

 

cmd.CommandType = CommandType.Text; cmd.CommandText = @"SELECT soh.SalesOrderNumber,                              sod.ProductID                      FROM Sales.SalesOrderHeader AS soh                             INNER JOIN Sales.SalesOrderDetail AS sod                                    ON soh.SalesOrderID = sod.SalesOrderID                     WHERE soh.SalesOrderNumber = '" + txtSalesOrderNo.Text + "'";   dtrSalesOrders = cmd.ExecuteReader();

 

The SQL statement composed by dynamic splicing is ad hoc query, and the corresponding parameterized query is:

cmd.CommandType = CommandType.Text; cmd.CommandText = @"SELECT soh.SalesOrderNumber,                              sod.ProductID                      FROM Sales.SalesOrderHeader AS soh                             INNER JOIN Sales.SalesOrderDetail AS sod                                ON soh.SalesOrderID = sod.SalesOrderID                     WHERE soh.SalesOrderNumber = @SalesOrderNo";                     cmd.Parameters.Add("@SalesOrderNo", SqlDbType.NVarChar, 50);  cmd.Parameters["@SalesOrderNo"].Value = txtSalesOrderNo.Text;  dtrSalesOrders = cmd.ExecuteReader();

Of course, the stored procedure is a 100% parameterized query. No matter the stored procedure does not contain any parameters, there should be no large number of ad hoc queries in the production environment, which will lead to high CPU utilization, the common solution to system performance degradation is

Rewrite the code in the program and convert it into the Writing Method of the stored procedure. If there is no way to rewrite the client code, there are several ideas to relieve the CPU pressure, but it cannot be 100% effective!

 

1: Forced parameterization

ALTER  DATABASE  AdventureWorks SET  PARAMETERIZATION FORCED 

2. Modify instance configurations

EXEC sp_configure  'show advanced options',1 RECONFIGURE EXEC sp_configure  'optimize for ad hoc workloads',1 RECONFIGURE

I hope this article will clarify the concept of ad hoc query!