Query Performance

Hi Guys,

I have a very big Database...
And This query below is taking up to 2 minutes to complete.

Some suggestions about how to make it better?

Select
s.InvcNbr Numero_nota,
v.cpnyid Cod_CRP,
v.cpnyname Nome_CRP,
i.invtid Cod_Produto,
i.descr Nome_Produto,
p.classid Cod_Classe,
p.descr Nome_Classe,
t.drcr Natureza,
t.acct Cod_Conta,
c.descr Nome_Conta,
t.sub Cod_SubConta,
su.descr Nome_SubConta,
s.custid Cod_Cliente,
s.billname Nome_Cliente,
sl.QtyPick Quantidade,
sl.CurySlsPrice Preco,
sl.curytotinvc Total,
s.OrdDate Data
from soshipline sl
left outer join soshipheader s
on ( s.shipperid = sl.shipperid and s.cpnyId = sl.cpnyId )
inner join vs_company v on s.cpnyid = v.cpnyid
inner join artran t on t.batnbr = s.arbatnbr and
t.cpnyid = s.cpnyid and
t.custid = s.custid and
t.invtid = sl.invtid
inner join inventory i on i.invtid = sl.invtid
inner join productclass p on p.classid = i.classid
inner join account c on c.acct = t.acct
inner join subacct su on su.sub = t.sub
where
t.acct like '3%'
and t.rlsed = 1
and s.User7 <> 'CANC'
and( rtrim( sl.shipperId ) + rtrim( sl.CpnyId ) + rtrim( sl.LineRef )
not In ( select rtrim( a.Origshipperid ) + rtrim( a.CpnyId ) + rTrim( a.LineRef )
from soshipLINE a inner Join soshipheader b on a.shipperid = b.shipperid
and a.cpnyId = b.CpnyId where b.user7 = 'CANC' ) )first of all, see your indexes

2-you have to many joins, look for if some tables you can make a subquery insted of a join, but only the tables that the where clause you can assure will seek by the PK, clustered index and will return only 1 row back.

3-you must have in mind that the principal select must limit your range of rows at maximun, so the subqueries will have less rows to look for.

4-try using set force plan if the principal query is not using the index u want to be used.

5-still try set showplan_all on for a better check of how the query is going to run.

hope i have helped you...

regards !!!

Originally posted by Diogo
Hi Guys,

I have a very big Database...
And This query below is taking up to 2 minutes to complete.

Some suggestions about how to make it better?

Select
s.InvcNbr Numero_nota,
v.cpnyid Cod_CRP,
v.cpnyname Nome_CRP,
i.invtid Cod_Produto,
i.descr Nome_Produto,
p.classid Cod_Classe,
p.descr Nome_Classe,
t.drcr Natureza,
t.acct Cod_Conta,
c.descr Nome_Conta,
t.sub Cod_SubConta,
su.descr Nome_SubConta,
s.custid Cod_Cliente,
s.billname Nome_Cliente,
sl.QtyPick Quantidade,
sl.CurySlsPrice Preco,
sl.curytotinvc Total,
s.OrdDate Data
from soshipline sl
left outer join soshipheader s
on ( s.shipperid = sl.shipperid and s.cpnyId = sl.cpnyId )
inner join vs_company v on s.cpnyid = v.cpnyid
inner join artran t on t.batnbr = s.arbatnbr and
t.cpnyid = s.cpnyid and
t.custid = s.custid and
t.invtid = sl.invtid
inner join inventory i on i.invtid = sl.invtid
inner join productclass p on p.classid = i.classid
inner join account c on c.acct = t.acct
inner join subacct su on su.sub = t.sub
where
t.acct like '3%'
and t.rlsed = 1
and s.User7 <> 'CANC'
and( rtrim( sl.shipperId ) + rtrim( sl.CpnyId ) + rtrim( sl.LineRef )
not In ( select rtrim( a.Origshipperid ) + rtrim( a.CpnyId ) + rTrim( a.LineRef )
from soshipLINE a inner Join soshipheader b on a.shipperid = b.shipperid
and a.cpnyId = b.CpnyId where b.user7 = 'CANC' ) )|||After you check all the indexes and the execution plan...

One area that may be slowing you down is this:

and( rtrim( sl.shipperId ) + rtrim( sl.CpnyId ) + rtrim( sl.LineRef )
not In ( select rtrim( a.Origshipperid ) + rtrim( a.CpnyId ) + rTrim( a.LineRef )
from soshipLINE a inner Join soshipheader b on a.shipperid = b.shipperid
and a.cpnyId = b.CpnyId where b.user7 = 'CANC' ) )

Try changing it to a NOT EXISTS test as follows:

and NOT EXISTS ( select *
from soshipLINE a inner Join soshipheader b on a.shipperid = b.shipperid and a.cpnyId = b.CpnyId and b.user7 = 'CANC'
where a.Origshipperid = sl.shipperId and a.CpnyId = sl.CpnyId
and a.LineRef = sl.LineRef )

btw... what version of SQL Server are you on?|||Hey Guys.
Thanx all!

I replace NOT IN with NOT EXISTS, and looke all steps you have suggested me.

Now,
The quey dont`t take 18 seconds!!

Thank Leandro and HueyStLoui!sql