SQL Server -- More efficient alternatives to repeating an (almost) identical subquery 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".
Hard to say for sure without seeing the full schema and queries, to work on the execution plans. But you could try:

Where
  (Select Count(Distinct fld1) From t1  ...etc... where ...etc... and fld1 In ('X', 'Y', 'Z')) = 3