OraFAQ Forum: Performance Tuning » query performance related

Home » RDBMS Server » Performance Tuning » query performance related

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query performance related [message #347920]

Mon, 15 September 2008 01:35

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

Report message to a moderator

Re: query performance related [message #348396 is a reply to message #347920]

Tue, 16 September 2008 11:01

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

Report message to a moderator

Re: query performance related [message #348402 is a reply to message #347920]

Tue, 16 September 2008 11:33

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

Report message to a moderator

Re: query performance related [message #348896 is a reply to message #348396]

Thu, 18 September 2008 04:22

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

Report message to a moderator

Re: query performance related [message #348912 is a reply to message #348896]

Thu, 18 September 2008 04:58

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.

Report message to a moderator

Re: query performance related [message #348927 is a reply to message #348912]

Thu, 18 September 2008 05:50

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

Report message to a moderator

Re: query performance related [message #348950 is a reply to message #348927]

Thu, 18 September 2008 06:30

Kriptas
Messages: 118
Registered: June 2008

Senior Member

show your new query

Report message to a moderator

Re: query performance related [message #348954 is a reply to message #348950]

Thu, 18 September 2008 06:45

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

Report message to a moderator

Re: query performance related [message #348959 is a reply to message #347920]

Thu, 18 September 2008 06:58

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

Report message to a moderator

Re: query performance related [message #348965 is a reply to message #348959]

Thu, 18 September 2008 07:39

basmraj
Messages: 8
Registered: September 2008

Junior Member

result is

n1 n2 n3 n4 n5
0 0 64 0 0

Report message to a moderator

Re: query performance related [message #348974 is a reply to message #348965]

Thu, 18 September 2008 07:54

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

Report message to a moderator

Previous Topic:	Help in tuning query
Next Topic:	dbms_stats

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 09:38:08 CST 2024