T-SQL Dynamic Query (2)--keyword query

Next: T-SQL Dynamic Query (1)--Introduction

Objective:

In the development of features, we often encounter scenarios such as the following: The application has a query function that allows the user to select the desired condition in 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, a user finds information by one of the following conditions:

1. Customer Name

2. Customer ID

3. Customer identification number (e.g., National ID card, American Social Security number, etc.).

And there are appropriate indexes on all three columns. This series focuses on dynamic SQL and option (RECOMPILE) query hints to handle requirements, but as mentioned earlier, frequent compilation and recompilation can put a heavy strain on the server, especially for those features that operate particularly frequently. Also, using dynamic SQL for some simple problems is a bit wasteful.

For the above problem, we can use a lighter method: using the IF statement.

The source of this article: http://blog.csdn.net/dba_huangzj/article/details/49929669

If statement:

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

IF @custno is not NULL  --Customer number   SELECT ... From customers WHERE Custno = @custnoELSE IF @idno are not NULL  --identification number   SELECT ... From customers WHERE Natregno = @natregnoELSE IF @custname are not NULL--Customer name   SELECT TOP   ... From   customers   WHERE  custname like @custname + '% '   ORDER by  custnameelse   RAISERROR (' No query conditions are available! ', 16, 1)

Note: Don't dwell on the existence of tables and columns inside, this is just a general demonstration.

In addition, top 200 above is designed to avoid performance problems due to overly vague queries that result in large amounts of data being returned because of the user entering a very short string to query the customer name.

If your system returns data from other tables at the same time, and you do not like duplicate joins, you can pre-store all the matching customers into a table variable or temporary table, and then join again:

If @custno is not a null   insert @cust (custno) VALUES (@custno) ELSE IF @natregno is not null   insert @cust (CUSTNO) SEL ECT Custno from customers WHERE Natregno = @natregnoELSE IF @custname are not NULL   INSERT @cust (custno)      SELECT TOP (Custno) from   customers      WHERE custname like  @custname + '% '      ORDER by  Custnameelse   RAISERROR (' No query criteria available! ', 1, ' SELECT ... From   @cust cjoin   customers cst on cst.custno = C.custnojoin   ...

The source of this article: http://blog.csdn.net/dba_huangzj/article/details/49929669

There is a potential performance problem with this notation, and we want the optimizer to use the index on the query column, regardless of how the user chooses the query criteria. But the way SQL Server creates execution plans makes this situation difficult to always do. The problem of parameter sniffing is mentioned earlier, when a stored procedure is executed and a reusable execution plan is not found in the cache, SQL Server "sniffs" the entire stored procedure and current value, producing an execution plan that is optimal for the current value.

In other words, if the first user chooses to query with the customer number as a parameter, then the optimizer optimizes the customer number, and the underlying processing will have a null form for the customer name, which would cause the table scan if subsequent users search using the customer name, which is certainly not what you expect.

In order to avoid this situation, some precautions can be used. One is to split the original SELECT statement in the stored procedure into three specific SELECT statements, but it is undeniable that this approach makes the statement more and more large. The other is to specify the index with the index hint in the statement, but the statement is tied to death, and if the index is renamed for some reason, the statement will fail to run.

In a way, using the appropriate optimizefor hints may be a better choice:

SELECT TOP custnofrom   customerswhere  custname like @custname + ' percent ' ORDER by  custnameoption (OPTIMIZE for ( @custname = N ' zzzzzzz '))

This hint triggers SQL Server to create a query plan for the value you specify, such as zzzzzz above, without caring what value you actually pass in. But then you have to pick a value that is sufficiently selective and representative, and if you specify a value that is seldom used, it may be worse than not specifying it.

However, regardless of the way you use it, you should test your production environment-scale data (preferably simulating the behavior of your production environment) to ensure that execution plans and performance meet your expectations.

Based on the reason for the parameter sniffing, you need to do a test like this:

exec your [email protected] = 123EXEC your [email protected] = ' 1234567890 ' exec your [email protected] = ' ABC ' exec sp_recompile your sp< c1/>--empty stored procedure cache exec your [email protected] = ' 1234567890 ' exec your [email protected]custno = 123EXEC your [email protected] = ' ABC ' EXEC sp_recompile your spexec your [email protected] = ' ABC ' exec your [email protected] = 123EXEC your [email protected] = ' 1234567890 '

That is, you need to test the parameters of all parameters when they are generated separately as the first execution plan, and if there are many parameters, you can imagine the number of times you need to test. Note that the above sp_recompile is intended to clear the plan cache by recompiling to reduce the impact of the plan cache.

In the example above, if the user has added% to the string passed in using the @custname parameter, the scan may be a better way. If you need a query that supports a query string preceded by a%, the best way is to split it into two branches, such as:

IF Left (@custname, 1) <> '% '   --above query else-   -query statement with optimize for

Summary:

So far, we've seen using if to achieve a certain level of dynamic query. This approach is not very suitable for very many conditions, as shown earlier, you have to test and write a very large number of conditions, which may cause the code is greatly inconvenient to maintain. However, for simple cases, such as 2 or 3 parameters, this method is very effective, and the following will introduce other situations of the response measures.

The source of this article: http://blog.csdn.net/dba_huangzj/article/details/49929669

OR:

If you do not want to use multiple if judgments, and you can ignore the previous plus% of the string mentioned above, then the statement can be written in a way similar to the following:

SELECT TOP 200 ... From   customerswhere  (Custno = @custno and @custno are not NULL) OR       (Natregno = @natregno and @natregno are not N ULL) OR       (CustName like @custname + '% ' and @custname are not NULL) ORDER by  CustName

The WHERE clause here is actually:

Custno = @custno or Natregno = @natregno or CustName [email protected] + '% '

However, it is purposeful to add the is not NULL, plus the optimizer can generate an execution plan by indexing the indexes on three criteria. Because the is isn't a null condition, SQL Server can add a filter operator named "Startup expression" at run time, which can access only the desired index according to the actual situation, rather than as in the code.

However, this strategy requires that all query conditions be on a single table and have a relatively reasonable index. If the query criteria involve different tables, then performance does not necessarily meet your expectations.

Summary:

Many coding and optimization data shows that you should not use or too much, because it can cause non-sarg to appear. This can affect performance. However, in the above situation, or can behave well, so we do not because of some "iron law", "military" and not to consider and try other ways. Finally, don't blindly use this strategy, you need to verify that the execution plan and performance of each strategy meet expectations.

The source of this article: http://blog.csdn.net/dba_huangzj/article/details/49929669

Full-Text Search:

In addition to the above two methods, when you need to find a table or a fixed set (that is, do not dynamically add the removal data table according to the criteria) for different conditions of the query, using a large number of indexes to support a variety of queries often do not benefit.

A full-text search (fulltext) has been introduced from SQL 2005 to solve this problem to some extent. However, from the application experience, because the computer and SQL Server such RDBMS is developed by foreigners, so the support of Chinese is not satisfactory. So here is just a mention of the system of the English environment, this approach can be considered.

The source of this article: http://blog.csdn.net/dba_huangzj/article/details/49929669

Summarize:

This article demonstrates the handling scenarios for some simple keyword queries. The main use of the if/or two ways, as the text is written in many places, any one way should do sufficient validation and testing, especially in a certain amount of data, otherwise you run very well on the small database, perhaps to the formal environment on the collapse.

The next article will be devoted to the knowledge of static SQL.

T-SQL Dynamic Query (2)--keyword query