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

  Exists (Select 1 From t1  ...etc... where ...etc... and fld1='X')
  Exists (Select 1 From t1  ...etc... where ...etc... and fld1='Y')
  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".

  • What indexes do you have? – Max Vernon Apr 29 '16 at 2:18
  • @MaxVernon : Not sure. At least the PK on each of the tables. Not sure if "fld1" is indexed. – mnemotronic Apr 29 '16 at 2:28
  • Can you post the execution plan? Also which version of SQL Server is this for? – James Anderson Apr 29 '16 at 7:41

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:

  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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.