Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25

    Unanswered: Need help in optimizing SQL Script.

    Kudos to y'all!!! I have this SQL script...
    Code:
    SELECT * FROM OPENQUERY (liorder, '
    SELECT DISTINCT
           a.AUF_NR AS OrdNo,
           e.KU_NAME AS Customer,
           a.AUF_POS AS Pos,
           f.PC_PANE_NO AS Pane,
           f.PC_SGGL_SEQ AS Component,
           f.PC_SGGL_COD AS GlassCode,
           d.GL_BEZ AS GlassDesc,
           a.ANZ AS Qty,
           ((c.BREITE/1000*c.HOEHE/1000)*a.ANZ) AS SQM,
           (a.ANZ*c.SUM_BRUTTO) AS Val,
           (CASE
            WHEN(SELECT SUM(h.KF_FERT_QTY)
                 FROM LIPROD.KAPA_AUS_FERT h
                 WHERE a.AUF_NR = h.KF_ORDER_NO AND
                       a.AUF_POS = h.KF_ORDER_POS AND
                       f.PC_PANE_NO = h.KF_SCHEIB_NR AND
                       f.PC_SGGL_SEQ = CASE
                                       WHEN h.KF_SEQ_NR = 0 THEN 1
                                       ELSE h.KF_SEQ_NR
                                       END AND
                       h.KF_SCHR_NR IN (2, 402, 502, 602)) IS NULL THEN 0
            ELSE(SELECT SUM(h.KF_FERT_QTY)
                 FROM LIPROD.KAPA_AUS_FERT h
                 WHERE a.AUF_NR = h.KF_ORDER_NO AND
                       a.AUF_POS = h.KF_ORDER_POS AND
                       f.PC_PANE_NO = h.KF_SCHEIB_NR AND
                       f.PC_SGGL_SEQ = CASE
                                       WHEN h.KF_SEQ_NR = 0 THEN 1
                                       ELSE h.KF_SEQ_NR
                                       END AND
                       h.KF_SCHR_NR IN (2, 402, 502, 602))
            END) AS Done
    FROM LIORDER.AUF_STAT a,
         LIORDER.AUF_KOPF b,
         LIORDER.AUF_POS c,
         LIORDER.GLAS_DATEN d,
         LIORDER.KUST_ADR e,
         LIPROD.AUF_POS_COMP f
    WHERE EXISTS
          (SELECT g.AUF_NR
           FROM LIORDER.AUF_STAT g
           WHERE g.AUF_NR = a.AUF_NR AND
                 g.RG_OFFEN != 0) AND
          EXISTS
          (SELECT i.KF_ORDER_NO
           FROM LIPROD.KAPA_AUS_FERT i
           WHERE a.AUF_NR = i.KF_ORDER_NO AND
                 i.KF_SCHR_NR IN (2, 402, 502, 602)) AND
          a.AUF_NR = b.AUF_NR AND
          b.AUF_NR = c.AUF_NR AND
          c.AUF_NR = f.PC_ORDER_NO AND
          a.AUF_POS = c.AUF_POS AND
          c.AUF_POS = f.PC_ORDER_POS AND
          b.KUNR = e.KU_NR AND
          f.PC_SGGL_COD = d.IDNR AND
          a.HISTORY = 0 AND
          b.AUF_OFF = 0 AND
          c.VER_ART != ''V'' AND
          e.KU_VK_EK = 0 AND
          e.KU_NAME IS NOT NULL
    ORDER BY a.AUF_NR DESC,
             a.AUF_POS ASC')
    ...It is retrieving data from an Oracle linked server. But the execution time is so friggin' long! I tried running it and for around 30 minutes it hasn't shown any results. So I could even tell the exact time it would take to return results. Do you have any tips regarding query optimization? Thanks in advance.
    Pinoy ako, pinoy tayo! Ipagmalaki mo...

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    some suggestions to improve performance

    - dont use DISTINCT

    - use COALESCE instead of that horrible CASE ... IS NULL expression

    - don't use EXISTS

    - use JOIN syntax instead of table lists

    - make sure all join columns have indexes

    - don't use ORDER BY

    - consider pre-calculating some intermediate results into temp tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes. Use INNER JOIN instead of EXISTS().
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    Based on r937's some suggestions,i have done ur home work .U should consider his other suggestions also,like index,avoid order by,distinct if u can.
    Code:
    SELECT * FROM OPENQUERY (liorder,'
    SELECT DISTINCT
           a.AUF_NR AS OrdNo,
           e.KU_NAME AS Customer,
           a.AUF_POS AS Pos,
           f.PC_PANE_NO AS Pane,
           f.PC_SGGL_SEQ AS Component,
           f.PC_SGGL_COD AS GlassCode,
           d.GL_BEZ AS GlassDesc,
           a.ANZ AS Qty,
           ((c.BREITE/1000*c.HOEHE/1000)*a.ANZ) AS SQM,
           (a.ANZ*c.SUM_BRUTTO) AS Val,
     (
      SELECT COALESCE(SUM(h.KF_FERT_QTY),0)
            FROM LIPROD.KAPA_AUS_FERT h
            WHERE  a.AUF_NR = h.KF_ORDER_NO AND
                  a.AUF_POS = h.KF_ORDER_POS AND
                  f.PC_PANE_NO = h.KF_SCHEIB_NR AND
                  f.PC_SGGL_SEQ = CASE
                                      WHEN h.KF_SEQ_NR = 0 THEN 1
                                      ELSE h.KF_SEQ_NR
                                      END AND
                  h.KF_SCHR_NR IN (2, 402, 502, 602)
     ) as Done
    FROM LIORDER.AUF_STAT a      join 
         LIORDER.AUF_KOPF b  on a.AUF_NR = b.AUF_NR   join
         LIORDER.AUF_POS c   on b.AUF_NR = c.AUF_NR   join
         LIORDER.GLAS_DATEN d  on c.AUF_NR = f.PC_ORDER_NO  join
         LIORDER.KUST_ADR e  on b.KUNR = e.KU_NR   join 
         LIPROD.AUF_POS_COMP f  on f.PC_SGGL_COD = d.IDNR  join
         LIPROD.KAPA_AUS_FERT i  on a.AUF_NR = i.KF_ORDER_NO
    WHERE 
          RG_OFFEN != 0 and
          a.HISTORY = 0 AND
          b.AUF_OFF = 0 AND
          c.VER_ART != ''V'' AND
          e.KU_VK_EK = 0 AND
          e.KU_NAME IS NOT NULL
    ORDER BY a.AUF_NR DESC,
             a.AUF_POS ASC 
    ')
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Aug 2005
    Location
    Sharjah, UAE
    Posts
    25
    Thanks for the tips guys! But as you are aware, I'm trying to retreive records from an Oracle linked server. So caolesce won't work. But I've managed to find out the equivalent in Oracle though. As for the exists, On the SELECT clause, I'm trying to show some rows that might not exist in one of the tables (KAPA_AUS_FERT). Maybe I should try OUTER JOIN? Any more tips?
    Pinoy ako, pinoy tayo! Ipagmalaki mo...

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Since all the processing is taking place on the Oracle side, I suggest you post your question in the Oracle forum. They may have some tips for optimizing it particular to Oracle's environment.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but in answer to your last question, WHERE EXISTS is identical to an INNER JOIN.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    identical?

    what about the optimizer abandoning the subquery processing for the EXISTS as soon as it finds the first row? whereas with INNER JOIN, it retrieves all rows
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I meant logically identical, though in some cases the optimizer will arrive at the same query plan for both as well.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, considering that this thread was about optimization, i don't think that merely being logically equivalent is going to cut the mustard

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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