Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sorry for the vague headline question.

Essentially, and I know this is generally terrible practice, but I have query with nested subqueries as follows:

SELECT * 
FROM   ibt1 
WHERE  basetype = 60 
       AND baseentry IN (SELECT baseentry 
                         FROM   ibt1 
                         WHERE  basetype NOT IN ( 60, 15 ) 
                                AND batchnum IN (SELECT batchnum 
                                                 FROM   ibt1 
                                                 WHERE  baseentry = 14836 
                                                        AND basetype = 15)) 

This runs within a few seconds.

However, extending the query one step further as below, seems to not return at all (certainly not within 15 minutes).

SELECT * 
FROM   ibt1 
WHERE  batchnum IN (SELECT batchnum 
                    FROM   ibt1 
                    WHERE  basetype = 60 
                           AND baseentry IN (SELECT baseentry 
                                             FROM   ibt1 
                                             WHERE  basetype NOT IN ( 60, 15 ) 
                                                    AND batchnum IN (SELECT 
                                                        batchnum 
                                                                     FROM   ibt1 
                                                                     WHERE 
                                                        baseentry = 14836 
                                                        AND basetype = 15))) 

Neither of these queries returns any huge amount of data (to the point where I was able to check by copy-pasting the results of the first query into a list and running that instead).

Why does SQL Server choke on the second query when it runs the first query just fine?

EDIT: More data:

SELECT TOP 1 * 
FROM   ibt1 
WHERE  batchnum IN (SELECT TOP 1 batchnum 
                    FROM   ibt1 
                    WHERE  basetype = 60 
                           AND baseentry IN (SELECT TOP 1 baseentry 
                                             FROM   ibt1 
                                             WHERE  basetype NOT IN ( 60, 15 ) 
                                                    AND batchnum IN (SELECT TOP 1
                                                        batchnum 
                                                                     FROM   ibt1 
                                                                     WHERE 
                                                        baseentry = 14836 
                                                        AND basetype = 15)))

Returns nearly instantly (as expected).

However changing the innermost query to return 2 rows:

SELECT TOP 1 * 
FROM   ibt1 
WHERE  batchnum IN (SELECT TOP 1 batchnum 
                    FROM   ibt1 
                    WHERE  basetype = 60 
                           AND baseentry IN (SELECT TOP 1 baseentry 
                                             FROM   ibt1 
                                             WHERE  basetype NOT IN ( 60, 15 ) 
                                                    AND batchnum IN (SELECT TOP 2
                                                        batchnum 
                                                                     FROM   ibt1 
                                                                     WHERE 
                                                        baseentry = 14836 
                                                        AND basetype = 15)))

also takes a long time to return.

share|improve this question
    
You will have to compare the plans between the two to figure out why. That said, the first query have a predicate basetype = 60 at the outer most level that the second query does not have. How many rows does ibt1 have, and how many of those satisfies basetype = 60? –  Lennart Jun 10 at 3:27
    
704,000 and 213,000 respectively. However, adding the predicate basetype = 60 at the outermost level does nothing. (Or, at least, doesn't nearly match the performance I would expect). –  Hotchips Jun 10 at 3:52
    
Why are you using nested IN() instead of a simple join? What exactly does TOP 1 mean when you have no ORDER BY? Not that changing either of these things will speed anything up, but a JOIN would be much easier to troubleshoot IMHO, and better defining the TOP 1 would make your requirements clear and also make the results predictable. –  Aaron Bertrand Jun 10 at 4:06
    
I'm only using TOP 1 to see what happens - as it happens, when I use TOP 1 throughout (and an ORDER BY on the innermost query - my fault, bad edit), the query returns. If I use TOP 2, it stops working again. The reason I'm not using JOINs is twofold. The first is that I'm more comfortable doing it this way (my SSMS window currently has the first query, copy-pasted into the second query, copy-pasted into the third query, etc). The second is that I had similar performance issues with JOINS and wasn't seeing those issues - right up until this point. –  Hotchips Jun 10 at 4:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.