Optimize query with many OR statements in WHERE clause

Question

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.

Answer 1

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..

Answer 2

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.)