Geek Answers Handbook

SQL Query works quickly with 19 elements in the "IN" section - much slower with 20. Why?

I have a request that includes the following:

... AND Record.RecordID IN (1,2,3,10,11,12,13,16,17,18,26,27,28,557,31,32,33,36,37,93) AND ...

The problem is that if this list contains 20 items or more, the request takes more than 25 seconds to complete. If there are less than 20, it is executed immediately. Any ideas on how to optimize?

+4
source share
7 answers

Place the RecordID in a temporary table and use inner join to filter them. For SQL Server, it looks like this:

 declare @RecordIds table (int RecordID) insert into @RecordIds values (1) insert into @RecordIds values (2) ... insert into @RecordIds values (93) select r.* from Records r inner join @RecordIds ri on ri.RecordID = r.RecordID
+9
source

One thing to do is look at the optimizer plan (if possible) and see how the plan differs when you use 20 elements or less vs. > 20. In Oracle, for example, you can use an explanation plan to see this conclusion.

Here is some information on how to use the explanation plan in Oracle: http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/ex_plan.htm

Other things to consider are whether you have an index in RecordID. Perhaps after you go over a certain threshold (> 20 elements in your case), the optimizer decides to better use a full table scan and use your index.

Sometimes with some databases, you can use optimizer hints to convince the optimizer to use the index if it really leads to better performance.

Here is a link to optimizer hints that you can read: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm

My answer is Oracle oriented, but the same principles should apply to most databases.

+9
source

The 20th element allows you to calculate the balance of this specific estimate of the cost of the request from one plan to another. with 20 points, you probably get a full table scan. IN is just syntactic sugar for OR .. OR ... OR ... OR. And OR is the enemy of good query plans. Use the compound, as Andomar suggested.

Update

If you exit the IN syntax, you can also use the query plan hint and make sure that the query remains on the optimal plan, the IN syntax forces you to change the query each time you run, so you cannot use the query plan hint.

+2
source

It seems dirty and unnecessary, but you tried:

 (Record.RecordID IN (--19 items--) OR Record.RecordID = 20th_item) AND

I don’t know why adding the 20th element to the IN group would have to drag it around the edge.

0
source

for MySQL manual says: "The number of values ​​in the IN list is limited only by the max_allowed_packet value." It seems unlikely that this is a problem, but this is the place to look.

In any case, storing your IN() values ​​in the temp table and attaching the query to it should bypass the whole problem.

0
source

The CLR table evaluation function will be another way of creating a table based on the provided parameters - for more information, see SQL Server 2005: CLR Table

0
source

It seems that when you add the 20th element, the optimizer generates a different execution plan. Implementation plans are based on statistics. Search criteria affect expected rows of results. When you add more items to the criteria list, the expected returned rows change and the optimizer can generate a new execution plan.

Check the execution plan (CTRL-L) of both queries. This is the only way to find out why it takes longer when you have more than 20 items in the list.

Before considering execution plans, update your table statistics:

 UPDATE STATISTICS records

if you can wait

 UPDATE STATISTICS records WITH FULLSCAN

The second will take longer, but you will have more consistent statistics.

0
source

More articles:


All Articles