Go4Expert

Go4Expert (http://www.go4expert.com/)
-   SQL Server (http://www.go4expert.com/articles/sql-server/)
-   -   Tuning an SQL query (http://www.go4expert.com/articles/tuning-sql-query-t20211/)

sameer_havakajoka 27Nov2009 09:42

Tuning an SQL query
 

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: SQL

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: sql

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

shabbir 27Nov2009 10:36

Re: Tuning an SQL query
 
7 Mins is a huge time.

sameer_havakajoka 27Nov2009 11:42

Re: Tuning an SQL query
 
thanks dear admin

nimesh 27Nov2009 12:49

Re: Tuning an SQL query
 
Nice work

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

-Nimesh

shabbir 27Nov2009 13:49

Re: Tuning an SQL query
 
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

sameer_havakajoka 27Nov2009 15:12

Re: Tuning an SQL query
 
how to make it more readable?

shabbir 27Nov2009 17:08

Re: Tuning an SQL query
 
Quote:

Originally Posted by sameer_havakajoka (Post 60798)
how to make it more readable?

By Indentation.

sameer_havakajoka 2Dec2009 11:41

Re: Tuning an SQL query
 
Indentation?

nimesh 2Dec2009 21:20

Re: Tuning an SQL query
 
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: sql

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:

nimesh 2Dec2009 21:28

Re: Tuning an SQL query
 
You can make some more changes (which I don't prefer :))
like this,

Code: sql

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'


sameer_havakajoka 2Dec2009 22:05

Re: Tuning an SQL query
 
hmm nw i understand...thx

shabbir 2Dec2009 22:10

Re: Tuning an SQL query
 
Quote:

Originally Posted by sameer_havakajoka (Post 61122)
hmm nw i understand...thx

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

nimesh 2Dec2009 22:20

Re: Tuning an SQL query
 
I resolved the syntactical errors from 1st code, hopefully all

sameer_havakajoka 3Dec2009 10:46

Re: Tuning an SQL query
 
thank you nimesh

technica 3Dec2009 12:37

Re: Tuning an SQL query
 
Any tips on how to use joins and unions effectively in SQL queries when it comes to play with multiple tables?

shabbir 3Dec2009 12:43

Re: Tuning an SQL query
 
Updated the article with the contribution from nimesh.

sameer_havakajoka 3Dec2009 13:05

Re: Tuning an SQL query
 
thanks to you too dear admin

shabbir 7Dec2009 09:26

Re: Tuning an SQL query
 
Nominate this article for Article of the month - Nov 2009

rasd123 28Dec2009 05:43

Re: Tuning an SQL query
 
Thanks to share this information.

obama549 28Mar2011 21:04

Re: Tuning an SQL query
 
Indentation?

mitzanu 19May2011 14:31

Re: Tuning an SQL query
 
1 Attachment(s)
nice tuning


All times are GMT +5.5. The time now is 07:17.