set difference in SQL query

Question

I'm trying to select records with a statement

SELECT * 
FROM A 
WHERE 
  LEFT(B, 5) IN 
    (SELECT * FROM 
       (SELECT LEFT(A.B,5), COUNT(DISTINCT A.C) c_count 
        FROM A 
        GROUP BY LEFT(B,5)
       ) p1 
       WHERE p1.c_count = 1
     ) 
     AND C IN 
        (SELECT * FROM 
            (SELECT A.C , COUNT(DISTINCT LEFT(A.B,5)) b_count 
             FROM A 
             GROUP BY C
            ) p2 
          WHERE p2.b_count = 1)

which takes a long time to run ~15 sec.

Is there a better way of writing this SQL?

Answer 1

Well, not sure what you're really trying to do here - but obviously, that LEFT(B, 5) expression keeps popping up. Since you're using a function, you're giving up any chance to use an index.

What you could do in your SQL Server table is to create a computed, persisted column for that expression, and then put an index on that:

ALTER TABLE A
   ADD LeftB5 AS LEFT(B, 5) PERSISTED

CREATE NONCLUSTERED INDEX IX_LeftB5 ON dbo.A(LeftB5)

Now use the new computed column LeftB5 instead of LEFT(B, 5) anywhere in your query - that should help to speed up certain lookups and GROUP BY operations.

Also - you have a GROUP BY C in there - is that column C indexed?

Answer 2

You don't need to return data from the nested subqueries. I'm not sure this will make a difference withiut indexing but it's easier to read.

And EXISTS/JOIN is probably nicer IMHO then using IN

SELECT * 
FROM
    A 
    JOIN
    (SELECT LEFT(B,5) AS b1
        FROM A 
        GROUP BY LEFT(B,5)
        HAVING COUNT(DISTINCT C) = 1
    ) t1 On LEFT(A.B, 5) = t1.b1
    JOIN
    (SELECT C AS C1
        FROM A 
        GROUP BY C
        HAVING COUNT(DISTINCT LEFT(B,5)) = 1
    ) t2 ON A.C = t2.c1

But you'll need a computed column as marc_s said at least

And 2 indexes: one on (computed, C) and another on (C, computed)