Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56

    Exclamation Unanswered: 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' ) )
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

  2. #2
    Join Date
    Sep 2002
    Location
    Rio de Janeiro, Brazil
    Posts
    20

    Re: Query Performance

    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' ) )
    Oracle 9i & SQL Server 2000 - DBA
    Rio de janeiro - Brazil
    lsantos.rj@globo.com

  3. #3
    Join Date
    Sep 2002
    Location
    Atlanta, GA USA
    Posts
    19

    Arrow Re: Query Performance

    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?

  4. #4
    Join Date
    Sep 2002
    Location
    Brazil
    Posts
    56
    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!
    Diogo Hudson Dias
    DBA - SQL 6.5/7.0/2000
    ASBACE - ATP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •