SQL Server -- More efficient alternatives to repeating an (almost) identical subquery

Question

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

Answer 1

If you're selecting from tables that include any single-value key (indexed) that is unique for the result (e.g. table1.id), perhaps the following will perform better for your data:

Where
  table1.id IN (
    Select table1.id
    From table1
    Join t1  ...etc...
    Where ...etc... AND fld1 IN ('X', 'Y', 'Z')
    Group by table1.id
    Having COUNT(DISTINCT fld1) = 3
    )

Answer 2

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