CODEWITHSUNDEEP
sqlperformancewhere-clause
topherg
topherg

Reputation: 4303

SQL Query concatenation performance boost

I have a class (that reflects a db row) that has more than 200 instances created within the codes bootstrap. Each one has a single SELECT query with the only condition being WHERE 'tblA'.'AID' = #, but I was thinking of creating a single query, that would parse 200 WHERE clauses connected by OR logic, then from the result, 200 objects are created with the data already found, so there is only 1 query.

I am implementing this on a test server at the moment, but I was wondering if this was a bad step for efficiency, and at what time it would be better to do 2 sets of queries, taking care of half the clauses each (or how ever many more need to be made)?

Additional, I am also writing a performance enhancer into it to replace something like

WHERE `tblA`.`AID` = 2 OR `tblA`.`AID` = 3 OR `tblA`.`AID` = 5 OR `tblA`.`AID` = 6 OR `tblA`.`AID` = 7

with

WHERE (`tblA`.`AID` >= 2 AND `tblA`.`AID` <= 3) OR (`tblA`.`AID` >= 5 AND `tblA`.`AID` <= 7)

or even

WHERE `tblA`.`AID` >= 2 AND `tblA`.`AID` <= 7 AND `tblA`.`AID` <> 4

Upvotes: 0

Views: 137

Answers (2)

Martin Smith
Martin Smith

Reputation: 453453

You still haven't specified DBMS.

For SQL Server this might be modestly worthwhile (though you may well want to consider joining on a table valued parameter or similar rather than having a lengthy IN list anyway).

SQL Server will do separate individual seeks rather than collapse them into contiguous ranges. This is covered thoroughly in the article When is a Seek not a Seek? but some examples below.

CREATE TABLE T (X int PRIMARY KEY)

INSERT INTO T 
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM master..spt_values v1, master..spt_values v2

SET STATISTICS IO ON;

SELECT *
FROM   T
WHERE   X IN ( 1, 2, 3, 4, 5, 6, 7, 8, 9,10,
              11,12,13,14,15,16,17,18,19,20,
              21,22,23,24,25,26,27,28,29,30,
              31,32,33,34,35,36,37,38,39,40,
              41,42,43,44,45,46,47,48,49,50,
              51,52,53,54,55,56,57,58,59,60,
              61,62,63,64)

Table 'T'. Scan count 64, logical reads 192

SELECT *
FROM   T
WHERE   X BETWEEN 1 AND 64

Table 'T'. Scan count 1, logical reads 3

As mentioned in the comments on the article for greater than 64 values you will get a slightly different plan that adds a table of constants and a nested loops join into the mix.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270191

If you have a discrete list, then just use in . . .

where AID in (2, 3, 5, 6, 7, . . .)

And let the SQL engine worry about the optimization.

The biggest hit is likely to be the time to parse the query and sending a large query to the engine. If your list gets really long, then consider putting the list in a temporary table, building an index on the table, and doing a join.

You don't specify what database you are using, but this advice is pretty database-agnostic.

Upvotes: 3

Related Questions