Sql-server – SQL Server — More efficient alternatives to repeating an (almost) identical subquery

sql serversubquerywhere

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

Best Answer

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

Related Question