1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

Tuning an SQL query

Discussion in 'SQL Server' started by sameer_havakajoka, Nov 27, 2009.

  1. sameer_havakajoka

    sameer_havakajoka New Member

    Introduction



    I had a complex SQL query that was used to extract data from my system and send it across via web request to an external system. The query was taking unduly longer time to run. I made few changes to the query structure and reduced it run time to 7 mins. A part of the main query that was actually causing the delay has been pasted in the sections below.

    Problem Description



    One of my external systems reported that one of the queries that run at my end have failed with the error below:

    ORA-20001: Error - WebService Request Failed. ORA-29276: transfer timeout.

    When I tried running the SELECT query in question at my end (on Toad), the query kept running after I aborted it after almost 90 minutes.

    The central part of the original query is as below. This part was causing the entire query to slow down. The tables TEAM, TIMESHEET, TIMEENTRY and ASSIGNMENT are considerably huge tables with billions of records. The explain plan showed a full table access on ASSIGNMENT and TIMESHEET tables.

    The code



    Problem
    Code:
    SELECT TP.START STARTED ,
    TP.FINISH FINISHED,
    TP.ID PERIOD,
    TM.PROJECTID PROJECTID,
    TM.ID TEAMID,
    TM.RESOURCEID RESOURCEID,
    SUM(TE.ACT) ACT 
    FROMTASK TK,
    TEAM TM,
    ASSIGNMENT ASM,
    TIMEENTRY TE,
    PERIOD TP,
    (SELECTMAX(A.ID)ID,PERIODID,RESOURCEID
    FROM TIMESHEET A, PERIOD B 
    WHERE STATUS IN(1,3,4)AND ADJUSTMENT =0
    ANDA.periodid = B.id
    ANDTRUNC(B.START)>=TRUNC(NEXT_DAY(NVL(NULL,SYSDATE)-28,'MONDAY'))
    GROUPBY PERIODID,RESOURCEID)TS 
    WHERE TM.PROJECTID IN(SELECT PRJ.ID
    FROM PROJECTS PRJ,
    CU_PROJECT CU,
    PRJ_ASSOC AS,
    PRJ_UNITS OBS,
    PRJ_TYPES TYPE
    WHEREAS.TABLE_NAME ='PROJECTS'
    ANDAS.RECORD_ID = PRJ.ID
    ANDAS.UNIT_ID = OBS.ID
    AND OBS.TYPE_ID=TYPE.ID
    ANDTYPE.NAME='TEST1'
    AND CU.ID=PRJ.ID
    AND CU.PRT_CODE='TEST2')
    AND TK.PROJECTID=TM.PROJECTID 
    AND ASM.TASKID=TK.ID
    AND ASM.RESOURCEID=TM.RESOURCEID
    AND TE.TIMESHEETID=TS.ID
    AND TS.PERIODID=TP.ID
    AND TE.ASSIGNID=ASM.ID
    ANDTRUNC(TP.START)>=TRUNC(NEXT_DAY(NVL(NULL,SYSDATE)-28,'MONDAY'))
    GROUPBY TP.START,TM.ID,TM.RESOURCEID,TM.PROJECTID,TP.ID,TP.FINISH
    I changed the query structure as below. This query took 7 mins to run, a considerable improvement from the old query:
    Code:
     
    WITH TS AS(SELECT/*+ materialize */MAX(A.ID)ID,PERIODID,RESOURCEID
    FROM TIMESHEET A, PERIOD B 
    WHERE STATUS IN(1,3,4)AND ADJUSTMENT =0
    ANDA.periodid = B.id
    ANDTRUNC(B.START)>=TRUNC(NEXT_DAY(NVL(NULL,SYSDATE)-28,'MONDAY'))
    GROUPBY PERIODID,RESOURCEID)
    SELECT TP.START STARTED ,
    TP.FINISH FINISHED,
    TP.ID PERIOD,
    TM.PROJECTID PROJECTID,
    TM.ID TEAMID,
    TM.RESOURCEID RESOURCEID,
    SUM(TE.ACT) ACT 
    FROMTASK TK,
    TEAM TM,
    ASSIGNMENT ASM,
    TIMEENTRY TE,
    PERIOD TP,
    (SELECT PRJ.ID
    FROM PROJECTS PRJ,
    CU_PROJECT CU,
    PRJ_ASSOC AS,
    PRJ_UNITS OBS,
    PRJ_TYPES TYPE
    WHEREAS.TABLE_NAME ='PROJECTS'
    ANDAS.RECORD_ID = PRJ.ID
    ANDAS.UNIT_ID = OBS.ID
    AND OBS.TYPE_ID=TYPE.ID
    ANDTYPE.NAME='TEST1'
    AND CU.ID=PRJ.ID
    AND CU.PRT_CODE='TEST2')S,
    WHERE TK.PROJECTID=TM.PROJECTID 
    AND ASM.TASKID=TK.ID
    AND ASM.RESOURCEID=TM.RESOURCEID
    AND S.ID= TM.PROJECTID
    AND TE.TIMESHEETID=TS.ID
    AND TS.RESOURCEID = TM.RESOURCEID
    AND TS.PERIODID=TP.ID
    AND TE.ASSIGNID=ASM.ID
    AND TP.START>=TRUNC(NEXT_DAY(NVL(NULL,SYSDATE)-28,'MONDAY'))
    GROUPBY TP.START,TM.ID,TM.RESOURCEID,TM.PROJECTID,TP.ID,TP.FINISH
    
    Using the WITH clause with materialize hint considerably improved the run time of the sub-query with a GROUP by clause. Moreover, the second sub-query which was joined in a WHERE clause earlier, was used as a table in the new query and this also helped increasing the performance.

    More readable code by Nimesh
    Code:
    SELECT TP.START STARTED
         , TP.FINISH FINISHED
         , TP.ID PERIOD
         , TM.PROJECTID PROJECTID
         , TM.ID TEAMID
         , TM.RESOURCEID RESOURCEID
         , SUM(TE.ACT) ACT
      FROM TASK TK
         , TEAM TM
         , ASSIGNMENT ASM
         , TIMEENTRY TE
         , PERIOD TP
         , (SELECT MAX(A.ID) ID
                 , PERIODID
                 , RESOURCEID
              FROM TIMESHEET A
                 , PERIOD B
             WHERE STATUS IN (1,3,4)
               AND ADJUSTMENT = 0
               AND A.periodid = B.id
               AND TRUNC(B.START) >= TRUNC(NEXT_DAY(NVL(NULL,SYSDATE)-28,'MONDAY'))
             GROUP BY PERIODID
                 , RESOURCEID
           ) TS
     WHERE TM.PROJECTID IN (SELECT PRJ.ID
                              FROM PROJECTS PRJ
                                 , CU_PROJECT CU
                                 , PRJ_ASSOC AS
                                 , PRJ_UNITS OBS
                                 , PRJ_TYPES TYPE
                             WHERE AS.TABLE_NAME = 'PROJECTS'
                               AND AS.RECORD_ID = PRJ.ID
                               AND AS.UNIT_ID = OBS.ID
                               AND OBS.TYPE_ID=TYPE.ID
                               AND TYPE.NAME='TEST1'
                               AND CU.ID=PRJ.ID
                               AND CU.PRT_CODE='TEST2'
                           )
       AND TK.PROJECTID = TM.PROJECTID 
       AND ASM.TASKID = TK.ID
       AND ASM.RESOURCEID = TM.RESOURCEID
       AND TE.TIMESHEETID = TS.ID
       AND TS.PERIODID = TP.ID
       AND TE.ASSIGNID = ASM.ID
       AND TRUNC(TP.START) >= TRUNC(NEXT_DAY(NVL(NULL,SYSDATE)-28,'MONDAY'))
     GROUP BY TP.START
         , TM.ID
         , TM.RESOURCEID
         , TM.PROJECTID
         , TP.ID
         , TP.FINISH
    
    Sameer
     
    nimesh likes this.
  2. shabbir

    shabbir Administrator Staff Member

    7 Mins is a huge time.
     
  3. sameer_havakajoka

    sameer_havakajoka New Member

    thanks dear admin
     
  4. nimesh

    nimesh New Member

    Nice work

    A kind request to format the codes in a more readable format, from next time. :)

    -Nimesh
     
  5. shabbir

    shabbir Administrator Staff Member

    Yes and if you plan to do it for the current one email me the content in text file. You know my email which is shabbir @ this domain
     
  6. sameer_havakajoka

    sameer_havakajoka New Member

    how to make it more readable?
     
  7. shabbir

    shabbir Administrator Staff Member

    By Indentation.
     
  8. sameer_havakajoka

    sameer_havakajoka New Member

    Indentation?
     
  9. nimesh

    nimesh New Member

    I follow this kind of Indentation, which is little different from what other use ;)
    And when writing the codes, use fixed with font so the indentation will appear properly.

    Code:
    SELECT TP.START STARTED
         , TP.FINISH FINISHED
         , TP.ID PERIOD
         , TM.PROJECTID PROJECTID
         , TM.ID TEAMID
         , TM.RESOURCEID RESOURCEID
         , SUM(TE.ACT) ACT
      FROM TASK TK
         , TEAM TM
         , ASSIGNMENT ASM
         , TIMEENTRY TE
         , PERIOD TP
         , (SELECT MAX(A.ID) ID
                 , PERIODID
                 , RESOURCEID
              FROM TIMESHEET A
                 , PERIOD B
             WHERE STATUS IN (1,3,4)
               AND ADJUSTMENT = 0
               AND A.periodid = B.id
               AND TRUNC(B.START) >= TRUNC(NEXT_DAY(NVL(NULL,SYSDATE)-28,'MONDAY'))
             GROUP BY PERIODID
                 , RESOURCEID
           ) TS
     WHERE TM.PROJECTID IN (SELECT PRJ.ID
                              FROM PROJECTS PRJ
                                 , CU_PROJECT CU
                                 , PRJ_ASSOC AS
                                 , PRJ_UNITS OBS
                                 , PRJ_TYPES TYPE
                             WHERE AS.TABLE_NAME = 'PROJECTS'
                               AND AS.RECORD_ID = PRJ.ID
                               AND AS.UNIT_ID = OBS.ID
                               AND OBS.TYPE_ID=TYPE.ID
                               AND TYPE.NAME='TEST1'
                               AND CU.ID=PRJ.ID
                               AND CU.PRT_CODE='TEST2'
                           )
       AND TK.PROJECTID = TM.PROJECTID 
       AND ASM.TASKID = TK.ID
       AND ASM.RESOURCEID = TM.RESOURCEID
       AND TE.TIMESHEETID = TS.ID
       AND TS.PERIODID = TP.ID
       AND TE.ASSIGNID = ASM.ID
       AND TRUNC(TP.START) >= TRUNC(NEXT_DAY(NVL(NULL,SYSDATE)-28,'MONDAY'))
     GROUP BY TP.START
         , TM.ID
         , TM.RESOURCEID
         , TM.PROJECTID
         , TP.ID
         , TP.FINISH
    
    Did you use the same query exactly, as I found lot of syntactical errors? :crazy:
     
  10. nimesh

    nimesh New Member

    You can make some more changes (which I don't prefer :))
    like this,

    Code:
    SELECT PRJ.ID
      FROM PROJECTS   PRJ
         , CU_PROJECT CU
         , PRJ_ASSOC  AS
         , PRJ_UNITS  OBS
         , PRJ_TYPES  TYPE
     WHERE AS.TABLE_NAME = 'PROJECTS'
       AND AS.RECORD_ID  = PRJ.ID
       AND AS.UNIT_ID    = OBS.ID
       AND OBS.TYPE_ID   = TYPE.ID
       AND TYPE.NAME     = 'TEST1'
       AND CU.ID         = PRJ.ID
       AND CU.PRT_CODE   = 'TEST2'
    
     
  11. sameer_havakajoka

    sameer_havakajoka New Member

    hmm nw i understand...thx
     
  12. shabbir

    shabbir Administrator Staff Member

    But what about the syntax error. Please look into it and email me the correct version
     
  13. nimesh

    nimesh New Member

    I resolved the syntactical errors from 1st code, hopefully all
     
  14. sameer_havakajoka

    sameer_havakajoka New Member

    thank you nimesh
     
  15. technica

    technica New Member

    Any tips on how to use joins and unions effectively in SQL queries when it comes to play with multiple tables?
     
  16. shabbir

    shabbir Administrator Staff Member

    Updated the article with the contribution from nimesh.
     
  17. sameer_havakajoka

    sameer_havakajoka New Member

    thanks to you too dear admin
     
  18. shabbir

    shabbir Administrator Staff Member

  19. rasd123

    rasd123 Banned

    Thanks to share this information.
     
  20. obama549

    obama549 New Member

    Indentation?
     

Share This Page