Home » RDBMS Server » Performance Tuning » How to simplify this pivot query
How to simplify this pivot query [message #269137] Thu, 20 September 2007 16:05 Go to next message
bztom33
Messages: 95
Registered: June 2005
Member
Hi,

I am trying to improve the speed of the following pivot query.
I would appreciate if anyone can provide an improve version of it.

thanks,

Tom

---Pivot query--------------
select dt1 date_time,value1,value2,flag,value3
from
(select date_time dt1,max(decode(custom_info.custom_id, (select custom_id from forecast_sales where store_num = 136 and Price_Tag= 'CID'), value,NULL)) value1 from custom_info, forecast_sales
where custom_info.custom_id = forecast_sales.custom_id and forecast_sales.Price_Tag = 'CID' and user_id = 1
and value !=-9999
and (custom_info.custom_id, op_date) in (select custom_info.custom_id, max(op_date)
from custom_info
where custom_info.custom_id = (select custom_id from forecast_sales where store_num = 136 and Price_Tag= 'CID' ) and
custom_info.op_date ='10/31/2006 08:02'
group by custom_info.custom_id
)
group by date_time) a,
(select date_time dt2,max(decode(custom_info.custom_id, (select custom_id from forecast_sales where store_num = 7 and Price_Tag= 'CID'), value,NULL)) value2,max(decode(custom_info.custom_id, (select custom_id from forecast_sales where store_num = 7 and Price_Tag= 'CID'), flag,NULL)) flag from custom_info, forecast_sales
where custom_info.custom_id = forecast_sales.custom_id and forecast_sales.Price_Tag ='CID' and user_id = 1
and value !=-9999
and (custom_info.custom_id, op_date) in (select custom_info.custom_id, max(op_date)
from custom_info
where custom_info.custom_id = (select custom_id from forecast_sales where store_num = 7 and Price_Tag= 'CID') 
and
custom_info.op_date ='10/31/2006 08:02'
group by custom_info.custom_id
)
group by date_time) b,
(select date_time dt3,max(decode(custom_info.custom_id, (select custom_id from forecast_sales where store_num = 135 and Price_Tag= 'CID'), value,NULL)) value3 from custom_info, forecast_sales
where custom_info.custom_id = forecast_sales.custom_id and forecast_sales.Price_Tag ='CID' and user_id = 1
and value !=-9999
and (custom_info.custom_id, op_date) in (select custom_info.custom_id, max(op_date)
from custom_info
where custom_info.custom_id = (select custom_id from forecast_sales where store_num = 135 and Price_Tag= 'CID')
and
custom_info.op_date ='10/31/2006 08:02'
group by custom_info.custom_id
)
group by date_time ) c
where a.dt1=b.dt2
and b.dt2=c.dt3  
order by dt1

[Updated on: Thu, 20 September 2007 16:28]

Report message to a moderator

Re: How to simplify this pivot query [message #269138 is a reply to message #269137] Thu, 20 September 2007 16:08 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Please read the Orafaq Forum Guide.
Format your query first then post it.
I think this question should goes to the Performance Tuning Forum, any moderator please transfer this.

Cheers
Soumen

[Updated on: Thu, 20 September 2007 16:09]

Report message to a moderator

Re: How to simplify this pivot query [message #269158 is a reply to message #269138] Thu, 20 September 2007 22:05 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Same advice I gave this guy, merge the scalar sub-queries into your FROM clause.

Ross Leishman
Re: How to simplify this pivot query [message #269856 is a reply to message #269158] Mon, 24 September 2007 21:11 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello bztom33,

following Soumen Kamilya advice... we would appreciate if you could post your code formatted.
Please read and apply this Forum Guide.
Click on this link below:
http://www.orafaq.com/forum/t/88153/0/

Besides posting your code passing thru a code formatter... please post the result of autotrace:
set autotrace traceonly explain


Regards,


mson77
Re: How to simplify this pivot query [message #270419 is a reply to message #269856] Wed, 26 September 2007 18:34 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
I simplified the above query. It's now about 5 folds increase in speed for the returned query.

Re: How to simplify this pivot query [message #271215 is a reply to message #270419] Sun, 30 September 2007 20:32 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello bztom33,

Would you consider to share your results here?
Maybe your final considerations can be improved even more based on the community's thought.

Regards,


mson77
Re: How to simplify this pivot query [message #272349 is a reply to message #271215] Thu, 04 October 2007 12:00 Go to previous messageGo to next message
bztom33
Messages: 95
Registered: June 2005
Member
Here's what I did...


select a.date_time, a.value, b.value, b.flag,c.value
from forecast_sales a, forecast_sales b, forecast_sales c
where a.op_date ='10/31/2006 08:02' 
and a.op_date = b.op_date
and a.op_date = c.op_date
and a.date_time = b.date_time
and a.date_time = c.date_time
and a.custom_id = 32  
and b.custom_id = 33  
and c.custom_id = 34  
and a.user_id = 1     
and a.user_id = b.user_id
and a.user_id = c.user_id
order by a.date_time

[Updated on: Thu, 04 October 2007 12:00]

Report message to a moderator

Re: How to simplify this pivot query [message #272350 is a reply to message #272349] Thu, 04 October 2007 12:10 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello bztom33,

Thank you for the feedback!
Then your final sql... formatted is:
001 SELECT  a.date_time, 
002         a.value, 
003         b.value, 
004         b.flag,
005         c.value 
006 FROM    forecast_sales a, 
007         forecast_sales b, 
008         forecast_sales c 
009 WHERE   a.op_date   ='10/31/2006 08:02' 
010     AND a.op_date   = b.op_date 
011     AND a.op_date   = c.op_date 
012     AND a.date_time = b.date_time 
013     AND a.date_time = c.date_time 
014     AND a.custom_id = 32 
015     AND b.custom_id = 33 
016     AND c.custom_id = 34 
017     AND a.user_id   = 1 
018     AND a.user_id   = b.user_id 
019     AND a.user_id   = c.user_id 
020 ORDER BY a.date_time  


And your initial untuned sql is:
001 SELECT  dt1 date_time,
002         value1,
003         value2,
004         flag,
005         value3 
006 FROM 
007         (SELECT date_time dt1,
008                 max(decode(custom_info.custom_id, 
009                 (SELECT custom_id 
010                 FROM    forecast_sales 
011                 WHERE   store_num = 136 
012                     AND Price_Tag = 'CID'
013                 )
014                 , value,NULL)) value1 
015         FROM    custom_info, 
016                 forecast_sales 
017         WHERE   custom_info.custom_id    = forecast_sales.custom_id 
018             AND forecast_sales.Price_Tag = 'CID' 
019             AND user_id                  = 1 
020             AND value                   !=-9999 
021             AND (custom_info.custom_id, 
022                 op_date) in 
023                 (SELECT custom_info.custom_id, 
024                         max(op_date) 
025                 FROM    custom_info 
026                 WHERE   custom_info.custom_id = 
027                         (SELECT custom_id 
028                         FROM    forecast_sales 
029                         WHERE   store_num = 136 
030                             AND Price_Tag = 'CID' 
031                         ) 
032                     AND custom_info.op_date ='10/31/2006 08:02' 
033                 GROUP BY custom_info.custom_id 
034                 ) 
035         GROUP BY date_time
036         ) 
037         a, 
038         (SELECT date_time dt2,
039                 max(decode(custom_info.custom_id, (SELECT custom_id FROM forecast_sales WHERE store_num = 7 AND Price_Tag= 'CID')
040                 , value,NULL)) value2,
041                 max(decode(custom_info.custom_id, (SELECT custom_id FROM forecast_sales WHERE store_num = 7 AND Price_Tag= 'CID')
042                 , flag,NULL)) flag 
043         FROM    custom_info, 
044                 forecast_sales 
045         WHERE   custom_info.custom_id    = forecast_sales.custom_id 
046             AND forecast_sales.Price_Tag ='CID' 
047             AND user_id                  = 1 
048             AND value                   !=-9999 
049             AND (custom_info.custom_id, 
050                 op_date) in 
051                 (SELECT custom_info.custom_id, 
052                         max(op_date) 
053                 FROM    custom_info 
054                 WHERE   custom_info.custom_id = (SELECT custom_id FROM forecast_sales WHERE store_num = 7 AND Price_Tag= 'CID') 
055                     AND custom_info.op_date   ='10/31/2006 08:02' 
056                 GROUP BY custom_info.custom_id 
057                 ) 
058         GROUP BY date_time
059         ) 
060         b, 
061         (SELECT date_time dt3,
062                 max(decode(custom_info.custom_id, 
063                 (SELECT custom_id 
064                 FROM    forecast_sales 
065                 WHERE   store_num = 135 
066                     AND Price_Tag = 'CID'
067                 )
068                 , value,NULL)) value3 
069         FROM    custom_info, 
070                 forecast_sales 
071         WHERE   custom_info.custom_id    = forecast_sales.custom_id 
072             AND forecast_sales.Price_Tag ='CID' 
073             AND user_id                  = 1 
074             AND value                   !=-9999 
075             AND (custom_info.custom_id, 
076                 op_date) in 
077                 (SELECT custom_info.custom_id, 
078                         max(op_date) 
079                 FROM    custom_info 
080                 WHERE   custom_info.custom_id = 
081                         (SELECT custom_id 
082                         FROM    forecast_sales 
083                         WHERE   store_num = 135 
084                             AND Price_Tag = 'CID'
085                         ) 
086                     AND custom_info.op_date ='10/31/2006 08:02' 
087                 GROUP BY custom_info.custom_id 
088                 ) 
089         GROUP BY date_time 
090         ) 
091         c 
092 WHERE   a.dt1=b.dt2 
093     AND b.dt2=c.dt3 
094 ORDER BY dt1

And we can see that you followed Ross Leishman advice about avoiding scalar subqueries.

Then the 5 folds increase in speed is ok?
Thank you,


mson77
Re: How to simplify this pivot query [message #339881 is a reply to message #272350] Sat, 09 August 2008 07:05 Go to previous message
bztom33
Messages: 95
Registered: June 2005
Member
It's ok, but there's always room for improvement.

[Updated on: Sat, 09 August 2008 18:41]

Report message to a moderator

Previous Topic: UNION and UNION ALL discrepancy
Next Topic: Performance issue
Goto Forum:
  


Current Time: Fri Dec 09 11:33:35 CST 2016

Total time taken to generate the page: 0.05136 seconds