I have to make a choice:

  • choice 1: using IN subselect
  • choice 2: using INNER JOIN

See the examples:

IN subselect:

SELECT contractServiceCode FROM contractServices
WHERE contractID IN (
    SELECT contractID FROM contracts
    WHERE companyID IN (
            SELECT companyID FROM tblcompanies
            WHERE informationProvider = 1000000


SELECT cs.contractServiceCode 
FROM contractServices as cs
INNER JOIN contracts c ON (c.contractID = cs.contractID)
INNER JOIN tblcompanies tc ON (tc.companyID = c.companyID)
    tc.informationProvider = 1000000

In terms of readability I would say that the INNER JOIN is more readable. But I'm not worried about readablity. In my case performance / CPU / memory usage are import.

So the question is:

Which choice is better in terms of performance?

  • What do your tests show? – mustaccio Mar 6 '19 at 15:29
  • @mustaccio I tested in workbench which shows the duration / fetch in sec. But they are about the same. BUT, this test wasn't tested with big data, and I want to know which of the 2 has a better performance in terms of big data. – Julian Mar 6 '19 at 15:53
  • Those two statement are not doing the same thing. Most modern optimizers will rewrite one to the other if they think that's possible and will improve performance – a_horse_with_no_name Mar 6 '19 at 17:25

The best thing to do would be to test them in a non-production environment with similar workload and look at the Execution plans for both. This is an advanced topic and I don't pretend to know everything about it but you might see noticeable execution time differences from the get-go in bottom right of SSMS. If not, looking at the plans, pay attention to 'scans' and 'key lookups' as those should be avoided whenever possible. What you generally want to see is a small number of seeks, which means it's going into the table with purpose and knows what it's looking for. Scans mean it's going row by row through the entire table so much slower. If one does a seek versus a scan, go with the seek!

You can also use commands like:

set statistics time ON;
set statistics IO ON;

And then run the queries individually to see the number of reads/writes as well as seeks and scans if you are uncomfortable with the execution plan approach. Here is a good link as an introduction and if you aren't familiar with Brent Ozar, I'd highly suggest checking out his free scripts and he's blogged a ton about perform tuning.

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.