Query Timout

OK...i have a query that looks something like what is below and is generated on the fly:

SELECT DISTINCT c.*
FROM SOMETHING c
WHERE ( X IN
 (SELECT Y
 FROM Z
 WHERE ZXY = '1') AND
 ( X IN
 (SELECT Y
 FROM Z
 WHERE ZXY = '4')OR
 ( X IN
 (SELECT Y
 FROM Z
 WHERE ZXY = '9')AND
 ( X IN
 (SELECT Y
 FROM Z
 WHERE ZXY = '7'))

as you can tell something like this will take a long time to process.....what is the best way to handle a query like such?...is cursor the way to go.....it's driving me nuts!

Treyanyone...anyone?|||OK...if nobody can help...can someone point me in the right direction?

Trey|||I possibly would create an index on your ZXY column and create a stored procedure like this to enhance performance:

CREATE PROCEDURE proc_DoSomething
AS
SELECT DISTINCT c.* FROM SOMETHING c
WHERE ( X IN (SELECT Y FROM Z WHERE ZXY IN ('1', '4', '7') OR ZXY = '9'))

Note that I've combined your subqueries involving AND , OR operators into one. I guess this should work well for you.|||I would use a stored procedure but...they query is created on the fly...and i don't know how many variables will be passed to the stored procedure...could be 1 or could be 20.

Trey|||Yeah, this is likely more an index issue. There is nothing in this query that seems to me that it should toake all that long, unless you have millions and millions of rows in your table. If you add some indices to the table, you should be fine...|||ok...then something like this should be written how...?

SELECT DISTINCT c.*
FROM dbo.def c
WHERE (id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4790') AND (transaction.type_id = '4706') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) OR
(id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4753') AND (transaction.type_id = '4706') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) OR
(id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4767') AND (transaction.type_id = '4706') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) OR
(id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4777') AND (transaction.type_id = '4707') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) AND (id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4769') AND (transaction.type_id = '4707') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) AND (id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4767') AND (transaction.type_id = '4707') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL))) AND (id IN
(SELECT def.id
FROM def INNER JOIN
transaction ON def.id = transaction.id
WHERE (transaction.reason_id = '4773') AND (transaction.type_id = '4707') AND
(transaction.created_date > '1/1/1990 12:00:00') AND (transaction.created_date < '3/16/2004 12:00:00') AND
(transaction.removed_verified_date IS NULL)))