I need to improve the efficiency of multiple subqueries within a WHERE clause. The context is a report generator on an existing product. The only input I can provide is the WHERE clause. I have 3 almost identical subqueries with only 1 difference between them. I need to verify that there is at least 1 record that matches each condition.
Here is the gist of the query
Where Exists (Select 1 From t1 ...etc... where ...etc... and fld1='X') AND Exists (Select 1 From t1 ...etc... where ...etc... and fld1='Y') AND Exists (Select 1 From t1 ...etc... where ...etc... and fld1='Z')
The "Select" in each of the 3 subqueries is identical. It does Join and has other fields in it's Where clause including a reference to the PK from the outer query : I'm only showing what's different.
There are about half a million records in t1. The query above has been running now for over 30 minutes. I need to get it down to 30 seconds.
My options are extremely limited -- No DECLARE, no temporary tables, no "OVER".