Home » RDBMS Server » Performance Tuning » query performance related
query performance related [message #347920] Mon, 15 September 2008 01:35 Go to next message
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 messageGo to next message
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 messageGo to next message
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 messageGo to next message
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 #348912 is a reply to message #348896] Thu, 18 September 2008 04:58 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
i show one solution in my post there...
in your query there is LOTS OF INLINE QUERIES ON EACH ROW...
to select 1 row you doing >70 index range scans (or index seeks depend on dd Very Happy )

examine my select, maybe this help you too.

you can join 2 tables with hash join, and only then filter data for virtual columns.

Re: query performance related [message #348927 is a reply to message #348912] Thu, 18 September 2008 05:50 Go to previous messageGo to next message
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 #348950 is a reply to message #348927] Thu, 18 September 2008 06:30 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
show your new query
Re: query performance related [message #348954 is a reply to message #348950] Thu, 18 September 2008 06:45 Go to previous messageGo to next message
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 messageGo to next message
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 #348965 is a reply to message #348959] Thu, 18 September 2008 07:39 Go to previous messageGo to next message
basmraj
Messages: 8
Registered: September 2008
Junior Member
result is


n1 n2 n3 n4 n5
0 0 64 0 0
Re: query performance related [message #348974 is a reply to message #348965] Thu, 18 September 2008 07:54 Go to previous message
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










Previous Topic: Help in tuning query
Next Topic: dbms_stats
Goto Forum:
  


Current Time: Thu Dec 08 10:30:13 CST 2016

Total time taken to generate the page: 0.07869 seconds