A quick example of an efficient SQL Server search

The recent online system suddenly appears to have more than 10 notes, looking at the situation over time, after a few searches.

Discover the cause of the problem, through the end and the end of the two sides of the same time, the efficiency of the inquiry to raise more than a few hundred times

First of all, the original application of the business logically edit for each page number is fixed to 10 pen, so users can exchange up to 10 of the information

And the original business logically album was written like this.

This language is translated into the following example language when SQL Server is executed

(Here A and B are 1:n)

SELECT Lawno,lcno

From A INNER JOIN B

WHERE 1=1 and (LAWNO+LCNO) in (' A1 ', ' A2 ' .... ' A10 ')

In the case of fewer in terms, even if the efficiency is not good, still can be in the information of the

This problem is due to the change in business logically, from the original user can only be able to exchange a maximum of 10 pen information

Change to a maximum of 500 of the information.

From the example above, the in parameters of the WHERE clause are up to 500

In addition, the condition of the linked fields bit is also the result of using the function. Index Scan

In a number of parameters, the search time naturally pulls

Let's see what happens before the change.

In the above image, the test language only put 10 in terms of the parameters, you can see the efficiency of the whole body is not very good

And then we'll see how we do the adaptation to the digital end.

First, we will change the original reference parameters to Table valued Parameter form.

And the original in lookup language, modified to do INNER JOIN with table valued Parameter

Next, you need to create a custom Table Type on the digital side

Finally, let's look at the results of the changes.

You can see that after the whole modification, the Logical read dropped by as much as 4, and the Execute project moved the Parallelism.

Does it feel simple? This usage can be used not only in the search for the language, but also in a large number of Insert slow situations

You guys, if the end of this situation suddenly appears, you can consider to see and open communication, instead of TVP to write yo

Thank you all.

A quick example of an efficient SQL Server search