T-SQL dynamic query (1) _ MySQL

Cause: due to recent work needs and past questions, I decided to study dynamic SQL. I have been away from the first-line development for a few years, and I have not studied many technical things too much. I am an independent author of DBA and SQLServer performance optimization and management. more causes:

Due to recent work needs and past concerns, I decided to study dynamic SQL. I have been away from the first-line development for a few years, and I have not studied many technical things too much. I am an independent author of DBA and the art of SQL Server Performance Optimization and management, it focuses more on performance issues that meet functional requirements. However, I think this article is useful not only to DBAs, but also to database developers and even designers and architects.

Preface:

Has the reader ever encountered a similar function: an application (whether B/S or C/S structure) has some functions, allow users to select different conditions for query (for the sake of convenience, the query function is only available here ). In extreme cases, you can select different display columns, column display sequence, sorting form, or even secondary query of the result set on the interface. These functions are implemented in front-end programming languages and sometimes even better. However, the front-end implementation requires the support of the background database, so it is more focused on the database layer.

In the above example, the user may see this, but as a developer, you need to consider more than just this, if we use stored procedures to support data of this function, we need to consider that different query conditions may need to be associated with different tables. for example, we need to filter the date and condition at the same time, these two conditions are distributed in tables A and B, so you need to associate them during query. However, when you select to filter the region and customer name, the data may be distributed in tables B and C. you need to associate tables B and C in the same stored procedure.

Due to the limitations of relational database theory, it is difficult for you to flexibly construct such logic while keeping the code concise and efficient. In addition to using the cursor to add a large number of judgments, this method can almost achieve any function, but is often extremely inefficient, dynamic SQL appears in our scope of consideration. We need to use some tools for two purposes: obtaining correct data and obtaining reasonable performance.

Introduction:

The above examples are widely used in modern information systems. However, the following core issue arises: in SQL Server (probably in all RDBMS), there is no single execution plan that can well support all possible query conditions. You also want SQL Server to optimize user input.

There are two ways to achieve this requirement at the database level:

1. compile a static SQL statement with a query prompt (such as OPTION (RECOMPILE) to force SQL Server to compile the query every time.

2. use dynamic SQL to create a string that can overwrite the user-defined query conditions and execute the statement.

Both methods are common and have their own advantages and disadvantages. you cannot simply say which one is good or which one is not good. this series of articles will introduce the two types of content.

Note: This series uses SQL 2008 R2 and the Microsoft demo database AdventureWorks2008 R2 for demonstration.

Basic knowledge:

In order to allow readers to prepare some knowledge, I would like to introduce some basic knowledge including:

Execution plan cache statistics parameter sniffing (introduction)

Note: In order to control the length of the article, this series of articles will not discuss the above knowledge in depth, for more information, see the relevant information in my book "SQL Server Performance Optimization and management Art.

Execution plan:

After an SQL statement (SQL statement/batch or stored procedure) is submitted to SQL Server, SQL Server first analyzes the statement syntax, if it does not meet the requirements, immediately stop the subsequent operation and return the error message to the client.

After the SQL statement syntax analysis is passed, SQLServer finds whether an execution Plan is available in the memory Plan Cache (part of the memory Buffer Pool) based on the statement text, if yes, it will be executed directly. If no, it will be compiled. After compilation, an execution plan is generated and submitted to the storage engine for execution and cache. The general process is as follows:

Execution plan is one of the core performance. the storage engine accesses and returns data to the client based on the execution plan. a good execution plan can efficiently and quickly complete data requests, the unreasonable execution plan will increase the running time of a simple request from several seconds to several hours. The execution plan is also the core tool for analyzing performance issues. In SQL Server Management Studio (SQL 2000 is called the query analyzer and SQL2005 is short for SSMS), you can use the shortcut key Ctrl + M to view the execution plan (the actual execution plan) and Ctrl + L (estimated execution plan) to obtain, you can also get through the graphical interface:

Execution plan cache:

After the SQL Server Optimizer compiles and optimizes statements, it submits the statements to the storage engine for execution, and then caches the execution Plan to the Plan Cache in the memory for subsequent reuse.

The most important purpose of plan cache is to reduce unnecessary compilation and re-compilation, which puts significant CPU pressure on systems with high loads. If a stored procedure is called more than 100 times in one second, avoiding a 5 ms compilation process, the execution plan of the stored procedure can be reduced from 5 minutes to ms.

In addition, the plan cache is closely related to the Statement Text. we will introduce later that their execution plans can be reused for the following two statements:

SELECT ID,A,B FROM TB WHERE ID=1SELECT ID,A,B FROM TB WHERE ID=10

However, the following two statements are considered to be two different statements, resulting in the compilation of the second statement as a new statement, which usually leads to performance problems and a waste of resources:

Select id, A, bfrom tb where id = 1 select id, A, B FROM TB WHERE ID = 10 -- note space

For this type of problem, the general solution is reasonable coding standards and the use of parameterized queries, which will be introduced later.

In addition, the planned cache has the features of first-in-first-out storage. assume that the following storage process is used:

CREATEPROC TEST (@ID INT)AS  SELECT ID,A,B FROM TB WHERE ID=@IDGO

At the same time, we assume that the data distribution of the ID column on the TB table is extremely uneven. for example, ID = 1 occupies 90% of the columns, and ID = 10 only accounts for 0.01% of the columns, the number of data rows exceeds 1 million, and appropriate indexes are supported. In this case, index scanning should be appropriate for queries with ID = 1, while index search should be reasonable for queries with ID = 10. When you run the stored procedure for the first time and take @ ID = 1 as the parameter, the execution plan is generated and cached by index scan. this is correct. However, when the second execution takes @ ID = 10 as the parameter, the execution plan may be reused for some reason and the index scan method is still used. at this time, you may obviously feel the performance is low. This situation and vice versa. This issue will be detailed in a series of articles about parameter sniffing in the future.

Finally, the plan cache is in the memory, that is, it is unstable. restarting the service/server or some commands and operations may cause the plan to be flushed out of the memory. Periodically collect and store the execution plan in a dedicated monitoring database or save the execution plan as a file if necessary.

Statistics:

Statistics is a "table" that describes table data. We often say that data distribution is stored in statistics for SQL Server. Statistics can be automatically created or manually created, and the corresponding statistics are usually created along with the index creation. For detailed statistics, see Chapter 6 SQL Server Performance Optimization and management art.

Statistics are also the core of performance optimization. After SQL server analyzes the statement syntax, it selects the indexes and table Association algorithms used in the execution plan based on the statistics, the expiration and inaccuracy of statistical information will seriously affect the execution plan generation and the overall performance of the server.

In addition, during the optimization process, if the statistical information is accurate enough, the optimizer can determine whether data needs to be associated to avoid unnecessary table joining. Subsequent sections of the series will also be demonstrated.

Note: The current statistical information algorithm has been used since SQL2000 at the latest, but since SQL 2014, it has been greatly changed, making the algorithm more reasonable. if conditions are met, we recommend that you use SQL 2014 or later versions.

Parameter sniffing:

In many cases, this function is regarded as a performance killer or a negative term, but it is reasonable. we should analyze the specific situation. What is parameter sniffing? This example has been introduced in the execution plan cache section. Parameter passing is used in some stored procedures or other objects, but SQL Server does not know what parameters will be passed in during actual execution, in this case, SQL Server uses an estimate to generate an execution plan. However, in some cases, SQL Server also "sniffers" the actual input parameters, determine whether the execution plan in the cache is unreasonable for the current statement and its parameters, and whether it is necessary to re-compile the statement.

Therefore, this function is necessary and useful in many cases. However, if this function is used for some reason when it is not required, a reasonable execution plan will be discarded and an unreasonable execution plan will be selected, the result is that the query runs well, but it suddenly becomes very slow.

In general, we need to understand the nature of parameter sniffing before making a conclusion. In the future, I will write a special article on parameter sniffing. once the press conference adds a link to this article.

Environment preparation:

Software Environment: SQL Server 2008 R2, preferably Enterprise Edition. The operating system is random.

Sample Database: AdventureWorks 2008 R2,: http://msftdbprodsamples.codeplex.com/releases/view/59211

In addition to the above environment, we also need to build a stored procedure template in the database, which is a template. In fact, some statements will be added to the template during subsequent demonstration:

CREATE PROCEDURE sp_Get_orders                 @salesorderid     int     = NULL,                 @fromdate    datetime     = NULL,                 @todate      datetime     = NULL,                 @minprice    money        = NULL,                 @maxprice    money        = NULL,                 @custid      nchar(5)     = NULL,                 @custname    nvarchar(40) = NULL,                 @prodid      int          = NULL,                 @prodname    nvarchar(40) = NULL,                 @employeestrvarchar(MAX) = NULL,                 @employeetblintlist_tbltype READONLYAS SELECT o.SalesOrderID, o.OrderDate, od.UnitPrice, od.OrderQty,       c.CustomerID, per.FirstName as CustomerName,p.ProductID,       p.Name as ProductName,  per.BusinessEntityID as EmpolyeeIDFROM   Sales.SalesOrderHeader oINNER JOIN   Sales.SalesOrderDetail od ON o.SalesOrderID = od.SalesOrderIDINNER JOIN   Sales.Customer c ON o.CustomerID = c.CustomerIDINNER JOIN   Person.Person per on c.PersonID=per.BusinessEntityIDINNER JOIN   Production.Product p ON p.ProductID = od.ProductIDWHERE  ???ORDER  BY o.SalesOrderIDGO

Note the above WHERE ??? In the following example, we will add or remove the WHERE condition and create a named stored procedure.

Briefly describe the parameters of the stored procedure:

@ Salesorderid	Order ID
@ Fromdate	Start date
@ Todate	End date
@ Minprice	Lowest Price
@ Maxprice	Highest price
@ Custid	Customer ID
@ Custname	Customer name
@ Prodid	Product ID
@ Prodname	Product Name
@ Employeestr	A string combination of employee IDs separated by commas
@ Employeetbl	Employee ID table value parameters

If you do not use any query conditions, the query conditions cannot be filtered. Therefore, you need a simple EXEC SP_GET_ORDERS method to return all database orders.

By rewriting the stored procedure, you can meet the following requirements in your business:

You can select how to sort the results. Depending on the input parameters, the statement can access different tables or columns. You can select a comparison operator, such as @ custname = 'xxxx' or @ custname! = 'Xxxx '. You can add or remove columns from the output results, or select what to aggregate in the aggregate query. Other requirements that you can think of, or even that you cannot think of, but that customers will think.

Next Article: T-SQL dynamic query (2) -- keyword query