Thread: How to save the results of replace(split_part(trim(both ' ' from "vx6000__12channel"),'__',2),'channel','myChannel') in my query into a temp variable?
How to save the results of replace(split_part(trim(both ' ' from "vx6000__12channel"),'__',2),'channel','myChannel') in my query into a temp variable?
From
zxo102 ouyang
Date:
Hi everyone,
I have a big query (see below attached) in which all where clauses have sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel').
The value of ec.instantance_flux is like the format: "vx6000__12channel". I want to replace "channel" with "myChannel": "vx6000__12myChannel".
Since the query take all most 40 minutes, I want to "optimize" it to see if i can get it done in shorter time. My question is: are there any way just do once:
replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel')
and save its result into "temp". The other where clause just use
sc.channel = temp
instead of doing
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel')
for 12 times in my query.
Thanks for your help in advance.
OUyang
#####################################################333
select rt_data.r_flowmeter_caliber as r_flowmeter_caliber,
rt_data.r_max01_sloc as r_max01_sloc,
rt_data.r_max01_sdata as r_max01_sdata,
rt_data.r_max01_sdate as r_max01_sdate,
rt_data.r_min01_sdata as r_min01_sdata,
rt_data.r_min01_sdate as r_min01_sdate,
rt_data.r_avg01_sdata as r_avg01_sdata,
acc_data.r_end_sdate as r_end_sdate,
acc_data.r_end_sdata as r_end_sdata,
acc_data.r_start_sdate as r_start_sdate,
acc_data.r_start_sdata as r_start_sdata,
acc_data.r_acc_sdata as r_acc_sdata
from
( select ec.flowmeter_caliber as r_flowmeter_caliber,
max01.r_sloc as r_max01_sloc,
round(max01.r_sdata*100)/100 as r_max01_sdata,
max01.r_sdate as r_max01_sdate,
round(min01.r_sdata*100)/100 as r_min01_sdata,
min01.r_sdate as r_min01_sdate,
round(avg01.r_sdata*100)/100 as r_avg01_sdata,
max01.r_channel as r_channel,
max01.r_sid as r_sid,
max01.r_sloc as r_sloc
from (
select max(rd01.sensor_data) as r_sdata,
sc.external_ins as r_sloc,
rd01.sensor_id as r_sid,
(select rd02.sensor_date
from record_data rd02,
sensor_cfg sc,
energy_classification02 ec
where rd02.sensor_id = rd01.sensor_id and
rd02.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd02.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel = rd02.sensor_channel and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
order by rd02.sensor_data DESC limit 1
) as r_sdate,
rd01.sensor_channel as r_channel
from record_data rd01,
sensor_cfg sc,
energy_classification02 ec
where rd01.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd01.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel = rd01.sensor_channel and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
group by rd01.sensor_id, sc.external_ins, rd01.sensor_channel
) max01,
( select min(rd01.sensor_data) as r_sdata,
rd01.sensor_id as r_sid,
(select rd02.sensor_date
from record_data rd02, sensor_cfg sc, energy_classification02 ec
where rd02.sensor_id= rd01.sensor_id and
rd02.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd02.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel = rd02.sensor_channel and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
order by rd02.sensor_data ASC limit 1
) as r_sdate,
rd01.sensor_channel as r_channel
from record_data rd01, sensor_cfg sc, energy_classification02 ec
where rd01.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd01.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel = rd01.sensor_channel and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
group by rd01.sensor_id, rd01.sensor_channel
) min01,
( select avg(rd01.sensor_data) as r_sdata,
rd01.sensor_id as r_sid,
(select rd02.sensor_date from record_data rd02, sensor_cfg sc, energy_classification02 ec
where rd02.sensor_id = rd01.sensor_id and
rd02.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd02.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel = rd02.sensor_channel and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
order by rd02.sensor_data ASC limit 1
) as r_sdate,
rd01.sensor_channel as r_channel
from record_data rd01, sensor_cfg sc, energy_classification02 ec
where rd01.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd01.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel=rd01.sensor_channel and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
group by rd01.sensor_id,rd01.sensor_channel
) avg01,
energy_classification02 ec,
sensor_cfg sc
where max01.r_sid=min01.r_sid and
min01.r_sid=avg01.r_sid and
max01.r_sid=sc.sensor_id and
sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel') and
sc.channel= min01.r_channel and
sc.channel=max01.r_channel and
sc.channel=avg01.r_channel and
sc.external_ins=ec.measure_name and
sc.remarks='I_VALUE' and
ec.flowmeter_caliber='I_FLOW'
) rt_data,
( select round(max01.r_sdata-min01.r_sdata)*100/100 as r_acc_sdata,
max01.r_sid as r_sid, max01.r_sloc as r_sloc,
max01.r_sdate as r_end_sdate,
max01.r_sdata as r_end_sdata,
min01.r_sdate as r_start_sdate,
min01.r_sdata as r_start_sdata
from (
select max(rd01.sensor_date) as r_sdate,
sc.external_ins as r_sloc,
rd01.sensor_id as r_sid,
(select rd02.sensor_data
from record_data rd02,
sensor_cfg sc,
energy_classification02 ec
where rd02.sensor_id = rd01.sensor_id and
rd02.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd02.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.flow_accumulation),'__',2),'channel','myChannel') and
sc.channel = rd02.sensor_channel and
sc.remarks='K_FLOW' and
ec.flowmeter_caliber='I_FLOW'
order by rd02.sensor_date DESC limit 1
) as r_sdata,
rd01.sensor_channel as r_channel
from record_data rd01,
sensor_cfg sc,
energy_classification02 ec
where rd01.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd01.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.flow_accumulation),'__',2),'channel','myChannel') and
sc.channel = rd01.sensor_channel and
sc.remarks='K_FLOW' and
ec.flowmeter_caliber='I_FLOW'
group by rd01.sensor_id, sc.external_ins, rd01.sensor_channel
) max01,
( select min(rd01.sensor_date) as r_sdate,
rd01.sensor_id as r_sid,
(select rd02.sensor_data
from record_data rd02,
sensor_cfg sc,
energy_classification02 ec
where rd02.sensor_id= rd01.sensor_id and
rd02.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd02.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.flow_accumulation),'__',2),'channel','myChannel') and
sc.channel = rd02.sensor_channel and
sc.remarks='K_FLOW' and
ec.flowmeter_caliber='I_FLOW'
order by rd02.sensor_date ASC limit 1
) as r_sdata,
rd01.sensor_channel as r_channel
from record_data rd01,
sensor_cfg sc,
energy_classification02 ec
where rd01.sensor_date between '2009-12-10' and '2009-12-12' and
sc.sensor_id = rd01.sensor_id and
sc.external_ins=ec.measure_name and
sc.channel = replace(split_part(trim(both ' ' from ec.flow_accumulation),'__',2),'channel','myChannel') and
sc.channel = rd01.sensor_channel and
sc.remarks='K_FLOW' and
ec.flowmeter_caliber='I_FLOW'
group by rd01.sensor_id, rd01.sensor_channel
) min01,
energy_classification02 ec,
sensor_cfg sc
where max01.r_sid=min01.r_sid and
max01.r_sid=sc.sensor_id and
sc.channel = replace(split_part(trim(both ' ' from ec.flow_accumulation),'__',2),'channel','myChannel') and
sc.channel= min01.r_channel and
sc.channel=max01.r_channel and
sc.external_ins=ec.measure_name and
sc.remarks='K_FLOW' and
ec.flowmeter_caliber='I_FLOW') acc_data
where acc_data.r_sloc = rt_data.r_sloc
order by r_max01_sloc desc
Re: How to save the results of replace(split_part(trim(both ' ' from "vx6000__12channel"),'__',2),'channel','myChannel') in my query into a temp variable?
From
Andy Colson
Date:
On 12/12/2009 09:59 AM, zxo102 ouyang wrote: > Hi everyone, > > I have a big query (see below attached) in which all where clauses > have sc.channel = replace(split_part(trim(both ' ' from > ec.instantance_flux),'__',2),'channel','myChannel'). > The value of ec.instantance_flux is like the format: > "vx6000__12channel". I want to replace "channel" with "myChannel": > "vx6000__12myChannel". > > Since the query take all most 40 minutes, I want to "optimize" it to see > if i can get it done in shorter time. My question is: are there any way > just do once: > replace(split_part(trim(both ' ' from > ec.instantance_flux),'__',2),'channel','myChannel') > and save its result into "temp". The other where clause just use > sc.channel = temp > instead of doing > sc.channel > = replace(split_part(trim(both ' ' from > ec.instantance_flux),'__',2),'channel','myChannel') > for 12 times in my query. > > Thanks for your help in advance. > > OUyang > Are you sure that is what is taking so much time? I'd bet its not. You should find the thing that is taking the most timeand optimize that. Have you run your query with explain analyze? If you post the output here (or use http://explain.depesz.com/) someone mightbe able to point you in a helpful direction. I'm not saying the replace(split_part... isn't worth optimizing, it probably is, I'm just saying, fix the slowest thing first. -Andy