IN subselects vs INNER JOIN (performance)

Question

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
    )
) 

INNER JOIN's:

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)
WHERE 
    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?

Answer 1

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;
GO;

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.