Home » RDBMS Server » Performance Tuning » query performance related
query performance related [message #347920] |
Mon, 15 September 2008 01:35 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
basmgokul
Messages: 2 Registered: April 2008
|
Junior Member |
|
|
hi,
My table contains the following columns:
member, rmember, units, subunits, reportnum, subunitsnum, firsthalf_lastyr, secondhalf_lastyr, thirdhalf_lastyr, fourthhalf_lastyr…..(total 46 columns)
I need to generate report like
rmember, units, subunits, reportnum, subunitsnum, firsthalf_lastyr, 1sthalf_units, 1sthalf_total…….(112 columns)
here,
1sthalf_units and 1sthalf_total are virtual columns.
I am using this select query to generate the virtual columns and its value:
Select rmember, units, subunits, reportnum, subunitsnum, firsthalf_lastyear,
case
when subunits='PSG' then (select firsthalf_lastyr from sam a where a.subunits='GRG' and a.rmember=b.rmember)
when subunits ='NGM' then (select firsthalf_lastyr from tsg a where a.subunits ='Services' and a.rmember =b.rmember)
when subunits='RVS' then (select firsthalf_lastyr from tsg a where a.subunits ='RVS' and a.rmember =b.rmember)
else NULL end as 1sthalf_units,
case
when subunits ='PSG' then (select firsthalf_lastyr from tsg a where a.subunits ='SAM' and a.member=b.rmember)
when subunits like 'R&D' and rmember in ('Region owned opex','% of revenue','Region opex','Region opex%') then
(select firsthalf_lastyr from tsg a where a.subunits like 'TSG' and a.rmember=b.rmember)
else NULL end as 1sthalf_total from sam b where reportnum<=8
likewise I need to create 76 virtual columns in this select query. So it takes 2-3 seconds.
please any one help me to reduce the execution time by any other concept to achieve the same result.
Thanks in advance
|
|
|
Re: query performance related [message #348396 is a reply to message #347920] |
Tue, 16 September 2008 11:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
>>likewise I need to create 76 virtual columns in this select query. So it takes 2-3 seconds.
all your query takes 2-3 seconds ? or selecting each report row takes 2-3 seconds?
please write more details about tables (row count, indexes and etc).
soon I write other query
|
|
|
Re: query performance related [message #348402 is a reply to message #347920] |
Tue, 16 September 2008 11:33 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
maybe this concept will help you :
SELECT rmember, units, subunits, reportnum, subunitsnum, firsthalf_lastyear,
MAX(case when (b.subunits='PSG' AND a.subunits='GRG')
OR (b.subunits ='NGM' AND a.subunits ='Services')
OR (b.subunits='RVS' AND a.subunits ='RVS')
then a.firsthalf_lastyr
else NULL
end) as 1sthalf_units,
MAX(case when (b.subunits ='PSG' AND a.subunits ='SAM')
OR (b.subunits = 'R&D'
AND b.rmember in ('Region owned opex','% of revenue','Region opex','Region opex%')
AND a.subunits like 'TSG')
else NULL
end) as 1sthalf_total
FROM sam b
LEFT JOIN tsg a ON a.rmember=b.rmember
GROUP BY rmember, units, subunits, reportnum, subunitsnum, firsthalf_lastyear
|
|
|
Re: query performance related [message #348896 is a reply to message #348396] |
Thu, 18 September 2008 04:22 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
basmraj
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Hi,
my query is:
i selected 6 columns from database table. then i wrote CASE for 2 virtual columns as q1py_bu and q1py_tot. like this i need to generate more than 70 columns this select statement. so it takes huge time for the query which i mentioned below.
select
Rhead,BU,Sub_bu,report_order,sub_bu_order,q1py,
case
when sub_bu=''ISS'' then (select q1py from sam a where a.sub_bu=''ESS'' and a.rhead=b.rhead)
when sub_bu=''TS'' then (select q1py from sam a where a.sub_bu=''Services'' and a.rhead=b.rhead)
when sub_bu=''SW'' then (select q1py from sam a where a.sub_bu=''SW'' and a.rhead=b.rhead)
else NULL
end as q1py_bu,
case
when sub_bu=''ISS'' then (select q1py from sam a where a.sub_bu=''TSG'' and a.rhead=b.rhead)
when sub_bu like ''R&D'' and rhead in (''Region owned opex'',''% of revenue'',''Region opex'',''Region opex%'') then
(select q1py from sam a where a.sub_bu like ''TSG'' and a.rhead=b.rhead)
else NULL
end as q1py_tot,
last_update from sam b where sub_bu<>bu and eport_order<=8;
Primary key columns are:
rhead, bu , sub_bu
Index:
clustered index (rhead, bu , sub_bu)
nonclustered index (report_order, bu , sub_bu)
So any one could you please help me in this by giving alternate solution for this(URGENT)
Thanks in advance
|
|
|
|
Re: query performance related [message #348927 is a reply to message #348912] |
Thu, 18 September 2008 05:50 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
basmraj
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
Hi,
I used the select query suggested by you. Execution time is very good compared to my sub-query, but the thing is i am not getting the values for the virtual columns (q1py_bu & q1py_tot).
I am getting null values for all the columns.
except the following conditon
(b.sub_bu='SW' AND a.sub_bu ='SW')
only for this condition i am geetting values for the virtual column q1py_bu
thanks
|
|
|
|
Re: query performance related [message #348954 is a reply to message #348950] |
Thu, 18 September 2008 06:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
basmraj
Messages: 8 Registered: September 2008
|
Junior Member |
|
|
SELECT a.rhead, a.bu, a.sub_bu, a.report_order, a.sub_bu_order, a.q1py,
MAX(case when (b.sub_bu='ISS' AND a.sub_bu='ESS')
OR (b.sub_bu ='TS' AND a.sub_bu ='Services')
OR (b.sub_bu='SW' AND a.sub_bu ='SW')
then a.q1py
else NULL
end) as q1py_bu,
MAX(case when (b.sub_bu ='ISS' AND a.sub_bu ='TSG')
OR (b.sub_bu = 'R&D' AND b.rhead in ('Region owned opex','% of revenue','Region opex','Region opex%')AND a.sub_bu like 'TSG')
then a.q1py
else NULL
end) as q1py_tot
FROM tsg a, tsg b where a.report_order<=8 and a.sub_bu<>a.bu
GROUP BY a.rhead,a.bu, a.sub_bu, a.report_order, a.sub_bu_order,a.q1py
|
|
|
Re: query performance related [message #348959 is a reply to message #347920] |
Thu, 18 September 2008 06:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
run this query and show me results
select sum (case b.sub_bu='ISS' AND a.sub_bu='ESS' then 1 else 0 end) n1,
sum (case b.sub_bu ='TS' AND a.sub_bu ='Services' then 1 else 0 end) n2,
sum (case b.sub_bu='SW' AND a.sub_bu ='SW' then 1 else 0 end) n3,
sum (case b.sub_bu ='ISS' AND a.sub_bu ='TSG' then 1 else 0 end) n4,
sum (case b.sub_bu = 'R&D' AND b.rhead in ('Region owned opex','% of revenue','Region opex','Region opex%') AND a.sub_bu = 'TSG' then 1 else 0 end) n5
FROM tsg a, tsg b where a.report_order<=8 and a.sub_bu<>a.bu
|
|
|
|
Re: query performance related [message #348974 is a reply to message #348965] |
Thu, 18 September 2008 07:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Kriptas
Messages: 118 Registered: June 2008
|
Senior Member |
|
|
so like you seee
there is no other combinations only (b.sub_bu='SW' AND a.sub_bu ='SW')
(case b.sub_bu='ISS' AND a.sub_bu='ESS' then 1 else 0 end) combination has 0 records
and you made one mistake in your select
in WHERE clause
FROM tsg a, tsg b where a.report_order<=8 and a.sub_bu<>a.bu
there must be join predicate
FROM tsg a, tsg b where a.report_order<=8 and a.sub_bu<>a.bu
AND a.rhead=b.rhead
and other thing
you wrote wrong alias before column, check them
must be (a.sub_bu='ISS' AND b.sub_bu='ESS')
and you writed (B.sub_bu='ISS' AND A.sub_bu='ESS')
correct all errors in script first
|
|
|
Goto Forum:
Current Time: Wed Sep 11 21:30:33 CDT 2024
|