StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POINNER JOIN vs LEFT JOIN performance in SQL Server
text
Body
copied!<p>I've created SQL command that use INNER JOIN for 9 tables, anyway this command take a very long time (more than five minutes). So my folk suggest me to change INNER JOIN to LEFT JOIN because the performance of LEFT JOIN is better, at first time its despite what I know. After I changed, the speed of query is significantly improve.</p> <p>I would like to know why LEFT JOIN is faster than INNER JOIN?</p> <p>My SQL command look like below: <code>SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D</code> and so on</p> <p><strong>Update:</strong> This is brief of my schema.</p> <pre><code>FROM sidisaleshdrmly a -- NOT HAVE PK AND FK INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK ON a.CompanyCd = b.CompanyCd AND a.SPRNo = b.SPRNo AND a.SuffixNo = b.SuffixNo AND a.dnno = b.dnno INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine ON a.CompanyCd = h.CompanyCd AND a.sprno = h.AcctSPRNo INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix ON c.CompanyCd = h.CompanyCd AND c.FSlipNo = h.FSlipNo AND c.FSlipSuffix = h.FSlipSuffix INNER JOIN coMappingExpParty d -- NO PK AND FK ON c.CompanyCd = d.CompanyCd AND c.CountryCd = d.CountryCd INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd ON b.CompanyCd = e.CompanyCd AND b.ProductSalesCd = e.ProductSalesCd LEFT JOIN coUOM i -- PK = UOMId ON h.UOMId = i.UOMId INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd ON a.CompanyCd = j.CompanyCd AND b.BFStatus = j.BFStatus AND b.ProductSalesCd = j.ProductSalesCd INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd ON e.ProductGroup1Cd = g1.ProductGroup1Cd INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd ON e.ProductGroup1Cd = g2.ProductGroup1Cd </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload