CODEWITHSUNDEEP
sqlsql-serversql-server-2008
watbywbarif
watbywbarif

Reputation: 7007

Optimize query with many OR statements in WHERE clause

What is the best way to write query which will give equivalent result to this:

SELECT X,Y,* FROM TABLE
WHERE (X = 1 AND Y = 2) OR (X = 2235 AND Y = 324) OR...

Table has clustered index (X, Y). Table is huge (milions) and there can be hundreds of OR statements.

Upvotes: 7

Views: 3582

Answers (4)

Manfred Wippel
Manfred Wippel

Reputation: 2066

if you are using too many or statements the execution plan wont use indexes. It is better to create multiple statements and merge the result using union all.

SELECT X,Y,* 
FROM TABLE
WHERE (X = 1 AND Y = 2) 
union all
SELECT X,Y,* 
FROM TABLE
WHERE (X = 2235 AND Y = 324) 
union all...

Upvotes: 0

Joe G Joseph
Joe G Joseph

Reputation: 24046

you can create another table with columns X and Y and insert the values in that table and and then join with the original table

create table XY_Values(X int, Y int)

Insert into XY_Values values
(1,2),
(2235,324),
...

Then

SELECT X,Y,* FROM TABLE T
join XY_Values V
on T.X=V.X
and T.Y=V.Y

You could create an index on (X,Y) on XY_Values , which will boost the performance

You could create XY_Values as a table variable also..

Upvotes: 5

Dumitrescu Bogdan
Dumitrescu Bogdan

Reputation: 7267

this will do better

select t.* 
from table t
join (select 1 as x,2 as y
      union
      ...) t1 on t.x=t1.x and t.y=t1.y

Upvotes: 3

András Ottó
András Ottó

Reputation: 7695

I think you can fill up a temp tables with the hundreds of X and Y values, and join them.

Like:

DECLARE @Temp TABLE
(
X int,
Y int
)

Prefill with this with your search requirements and join then.

(Or an other physical table which saves the search settings.)

Upvotes: 3

Related Questions