T-SQL dynamic query (2) keyword query _ MySQL

T-SQL dynamic query (1) -- Introduction

Preface:

During feature development, we often encounter a scenario similar to the following: the application has a query function that allows users to select the required conditions among many query conditions. This is also the focus of this series.

But sometimes you may find that some conditions are more or less mutually exclusive. For example, you can use one of the following conditions to find information:

1. customer name

2. customer ID

3. customer ID number (for example, domestic ID card and American Social Security number ).

And the three columns have appropriate indexes. This series focuses on dynamic SQL and OPTION (RECOMPILE) query prompts to deal with the demand, but as mentioned earlier, frequent compilation and recompilation will put a lot of pressure on the server, especially for features that are frequently operated. In addition, dynamic SQL is a waste of some simple problems.

To solve the problem above, we can use a lightweight method: using the IF statement.

IF statement:

For the above problem, let's take a look at the following example:

IF @ custno is not null -- customer number SELECT... from mers where custno = @ custnoELSE IF @ idno is not null -- ID number SELECT... from mers where natregno = @ natregnoELSE IF @ custname is not null -- customer name select top 200... from mers where custname LIKE @ custname + '%' order by custnameELSE RAISERROR ('no query condition is provided! ', 16, 1)

Note: Do not worry too much about the existence of tables and columns in it. here is just a rough demonstration.

In addition, the TOP 200 above aims to avoid performance problems when the user inputs a very short string to query the customer name, resulting in a large amount of data returned due to a too fuzzy query.

If the system returns data from other tables at the same time and does not like repeated JOIN, you can store all matching customer numbers in one table variable or temporary table, and then JOIN:

IF @ custno is not null insert @ cust (custno) VALUES (@ custno) else if @ natregno is not null insert @ cust (custno) SELECT custno FROM customers WHERE natregno = @ natregnoELSE IF @ custname is not null insert @ cust (custno) select top (200) custno from mers where custname LIKE @ custname + '%' order by custnameELSE RAISERROR ('no query condition is provided! ', 16, 1) SELECT... FROM @ cust cJOIN customers cst ON cst. custno = c. custnoJOIN...

This writing method has a potential performance problem. no matter how you select the query conditions, we want the optimizer to use the index of the query column. However, it is difficult for SQL Server to create an execution plan. Previously mentioned parameter sniffing. when a stored procedure is executed and no reusable execution plan is found in the cache, SQL Server will "sniff" the entire stored procedure and the current value to generate an execution plan that is best suited to the current value.

In other words, if the first user selects the customer number as the parameter query, the optimizer will optimize the customer number, and the customer name will be included in the underlying processing in the form of NULL, if you use the customer name for search in the future, the table will be scanned. this is definitely not what you expected.

Some preventive measures can be taken to avoid this situation. One of them is to split the original SELECT statement in the stored procedure into three targeted SELECT statements, but it is undeniable that this method will make the statement more and more huge. The other is to use the index prompt in the statement to specify the index, but the statement is bound to death. if the index is renamed for some reason, the statement will fail to run.

To some extent, using the appropriate OPTIMIZEFOR prompts may be a better choice:

SELECT TOP 200 custnoFROM   customersWHERE  custname LIKE @custname + '%'ORDER  BY custnameOPTION (OPTIMIZE FOR (@custname = N'ZZZZZZZ'))

This prompt will trigger SQL Server to create a query plan for the value you specified (ZZZZZZ above), without worrying about the value actually passed in. However, you need to select a value with sufficient selection degree and representativeness. if you specify a value that is rarely used, it may be worse than not.

However, no matter how you use it, you should test the data in the production environment scale (preferably simulating the behavior of the production environment) to ensure that the execution plan and performance meet your expectations.

Based on the parameter sniffing, you need to perform a test similar to the following:

EXEC your SP @ custno = 123EXEC your SP @ natregno = '000000' EXEC your SP @ custname = 'abc' EXEC sp_recompile your SP -- clear the stored procedure cache EXEC your SP @ natregno = '000000' EXEC your SP @ custno = 123EXEC your SP @ custname = 'abc' EXEC sp_recompile your SPEXEC your SP @ custname = 'abc' EXEC you SP @ custno = 123EXEC your SP @ natregno = '000000'

That is to say, you need to test all the parameters that are generated separately as the first execution plan. if there are many parameters, you can imagine that the number of tests is also large. Note that the sp_recompile above is used to clear the plan cache by re-compiling to reduce the impact of the plan cache.

In the preceding example, if % is added to the string passed in by the @ custname parameter, scanning may be better. If you need to support query with % ahead of the string, the best way is to split it into two branches, such:

IF left (@ custname, 1) <> '%' -- query ELSE above -- query statement with OPTIMIZE

Summary:

So far, we have seen that IF is used for dynamic queries to a certain extent. This method is not very suitable for situations with many conditions. as shown above, there are many conditions for testing and writing, which may cause a large amount of code to be difficult to maintain. However, this method is very effective in simple cases, such as two or three parameters. we will introduce the corresponding measures in other cases in the future.

OR:

IF you do not want to use multiple IF statements and ignore the % prefix of the string mentioned above, you can rewrite the statements in a similar way:

SELECT TOP 200 ...FROM   customersWHERE  (custno = @custno AND @custno IS NOT NULL) OR       (natregno = @natregno AND @natregno IS NOT NULL) OR       (custname LIKE @custname + '%' AND @custname IS NOT NULL)ORDER  BY custname

The WHERE clause here is actually:

custno = @custno OR natregno = @natregno OR custname LIKE@custname + '%'

However, adding is not null is purposeful. after adding it, the optimizer can generate an execution plan by connecting the indexes on the three conditions. Because the condition is not null, SQL Server can add a filter operator named "startup expression" at runtime. this operator can only access the required indexes according to actual conditions, instead of the code.

However, this policy requires that all query conditions apply to a table and have relatively reasonable indexes. If the query conditions involve different tables, the performance does not necessarily meet your expectations.

Summary:

Many coding and optimization materials show that do not use too much OR, because it will lead to the emergence of non-SARG. This affects performance. However, in the above cases, OR can still be used, so we should not consider OR try other methods because of some "iron laws" and "military rules. Finally, we should emphasize that you should not blindly use such a policy. you need to verify whether the execution plan and performance of each policy meet expectations.

Full-text search:

In addition to the preceding two methods, when you need to query a table or a fixed set (that is, you do not dynamically add or remove data tables according to the conditions) for different conditions, using a large number of indexes to support various queries often does not offer any benefit.

Full-Text Search (Fulltext) was introduced in SQL 2005 to solve this problem to a certain extent. However, from the perspective of application experience, since RDBMS such as computers and SQL Server are developed by foreigners, Chinese support is not satisfactory. So here we just mention that this method can be considered for English environment systems.

Summary:

This article demonstrates some simple keyword query solutions. The IF/OR method is mainly used. as I have written in multiple places, all methods should be fully verified and tested, especially when a certain amount of data is involved, otherwise, you may run well on a small database, and the database may crash in the official environment.

The above is the T-SQL dynamic query (2) keyword query _ MySQL content, more relevant content please pay attention to PHP Chinese network (www.php1.cn )!