INNER JOIN vs LEFT JOIN performance in SQL Server -

i've created sql command use inner join 9 tables, anyway command take long time (more 5 minutes). folk suggest me change inner join left join because performance of left join better, @ first time despite know. after changed, speed of query improve.

i know why left join faster inner join?

my sql command below: select * inner join b on ... inner join c on ... inner join d , on

update: brief of schema.

from sidisaleshdrmly -- not have pk , fk     inner join sidisalesdetmly b -- table have no pk , fk         on a.companycd = b.companycd             , a.sprno = b.sprno             , a.suffixno = b.suffixno             , a.dnno = b.dnno     inner join exfslipdet h -- pk = companycd, fslipno, fslipsuffix, fslipline         on a.companycd = h.companycd            , a.sprno = h.acctsprno     inner join exfsliphdr c -- pk = companycd, fslipno, fslipsuffix         on c.companycd = h.companycd            , c.fslipno = h.fslipno             , c.fslipsuffix = h.fslipsuffix      inner join comappingexpparty d -- no pk , fk         on c.companycd = d.companycd            , c.countrycd = d.countrycd      inner join coproduct e -- pk = companycd, productsalescd         on b.companycd = e.companycd            , b.productsalescd = e.productsalescd      left join couom -- pk = uomid         on h.uomid = i.uomid      inner join coproductoldinformation j -- pk = companycd, bfstatus, speccd         on a.companycd = j.companycd             , b.bfstatus = j.bfstatus             , 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 

a left join absolutely not faster inner join. in fact, it's slower; definition, outer join (left join or right join) has work of inner join plus work of null-extending results. expected return more rows, further increasing total execution time due larger size of result set.

(and if left join were faster in specific situations due difficult-to-imagine confluence of factors, not functionally equivalent inner join, cannot go replacing instances of 1 other!)

most performance problems lie elsewhere, such not having candidate key or foreign key indexed properly. 9 tables quite lot joining slowdown literally anywhere. if post schema, might able provide more details.

---

edit:

reflecting further on this, think of 1 circumstance under left join might faster inner join, , when:

• some of tables very small (say, under 10 rows);
• the tables not have sufficient indexes cover query.

consider example:

create table #test1 (     id int not null primary key,     name varchar(50) not null ) insert #test1 (id, name) values (1, 'one') insert #test1 (id, name) values (2, 'two') insert #test1 (id, name) values (3, 'three') insert #test1 (id, name) values (4, 'four') insert #test1 (id, name) values (5, 'five')  create table #test2 (     id int not null primary key,     name varchar(50) not null ) insert #test2 (id, name) values (1, 'one') insert #test2 (id, name) values (2, 'two') insert #test2 (id, name) values (3, 'three') insert #test2 (id, name) values (4, 'four') insert #test2 (id, name) values (5, 'five')  select * #test1 t1 inner join #test2 t2 on t2.name = t1.name  select * #test1 t1 left join #test2 t2 on t2.name = t1.name  drop table #test1 drop table #test2 

if run , view execution plan, you'll see inner join query indeed cost more left join, because satisfies 2 criteria above. it's because sql server wants hash match inner join, nested loops left join; former normally faster, since number of rows tiny and there's no index use, hashing operation turns out expensive part of query.

you can see same effect writing program in favourite programming language perform large number of lookups on list 5 elements, vs. hash table 5 elements. because of size, hash table version slower. increase 50 elements, or 5000 elements, , list version slows crawl, because it's o(n) vs. o(1) hashtable.

but change query on id column instead of name , you'll see different story. in case, nested loops both queries, inner join version able replace 1 of clustered index scans seek - meaning literally an order of magnitude faster large number of rows.

so conclusion more or less mentioned several paragraphs above; indexing or index coverage problem, possibly combined 1 or more small tables. circumstances under sql server might choose worse execution plan inner join left join.