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  |
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 #272349 is a reply to message #271215] |
Thu, 04 October 2007 12:00  |
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 |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 22 22:21:42 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
