create or replace view pm_s_project as
select uniqueid,proj_code,
customer,
vehicle,engine,
sod,sop,emission,
transmission,immo,
new_component,
proj_type,
internal_order,
PH1,PH2,
APMS_B_NUMBER,
APMS_P_NUMBER,
APMS_PM
from
(
select
t.uniqueid as uniqueid,t.proj_code as proj_code ,t.customer as customer,
tt4.vehicle||tt3.vehicle||tt1.vehicle as vehicle,tt1.engine||tt4.engine as engine,tt1.sod || tt3.sod as sod,
tt4.sop||tt1.sop||tt3.sop as sop,tt4.emission||tt1.emmision||tt3.emmission as emission,
tt3.gear||tt4.transmission||tt1.transmission as transmission,tt3.immo||tt4.immo||tt1.immo as immo,
tt3.new_component||tt4.new_componet||tt1.new_component||tt2.new_component as new_component
,t.proj_type as proj_type,t.internal_order as internal_order,ppc.cname as ph1,ppc1.cname as ph2,
tt2.bnumber||t.apms_b_number as apms_b_number,tt2.pnumber||t.apms_0_number as apms_p_number,
pe1.login_name||tt1.login_name||tt2.pm as apms_pm
from
pm_project t left join
(
select ptp.project_code,ptp.new_component,ptp.immo,ptp.gear,ptp.emmission,ptp.sop,ptp.sod,ptp.vehicle
from pm_tcu_project ptp left join pm_tcu_component_project ptcp on ptp.project_code = ptcp.parentno
) tt3
on t.proj_code = tt3.project_code left join
(
select pep.project_code,pep.new_componet,pep.immo,pep.transmission,pep.emission,pep.sop,
pep.engine,pep.vehicle
from
pm_ex_project pep left join pm_ex_component_project pecp on pep.project_code = pecp.parentno
) tt4
on t.proj_code = tt4.project_code left join
(
select apf.piid,pe2.login_name,apf.new_component,apf.immo,apf.transmission,apf.emmision,apf.sop,apf.sod,
apf.engine,apf.vehicle
from
apms_prjinfo apf left join pm_employee pe2 on apf.pm = pe2.login_name
) tt1
on t.apms_sys_id = tt1.piid left join
(
select apl.new_component,apl.id,apl.pm,apl.pnumber,pe3.login_name,apl.bnumber
from
apms_projectlist apl left join pm_employee pe3 on apl.pm = pe3.login_name
) tt2
on t.apms_prj_id = tt2.id left join pm_product_component ppc on t.ph1 = ppc.uniqueid left join pm_product_component ppc1 on t.ph2 = ppc1.uniqueid left join pm_employee pe1 on t.proj_manager = pe1.uniqueid )
;查询效率实在太低了。谁能帮我改下。现在平均查询时间在10秒左右,希望能在3秒左右
select uniqueid,proj_code,
customer,
vehicle,engine,
sod,sop,emission,
transmission,immo,
new_component,
proj_type,
internal_order,
PH1,PH2,
APMS_B_NUMBER,
APMS_P_NUMBER,
APMS_PM
from
(
select
t.uniqueid as uniqueid,t.proj_code as proj_code ,t.customer as customer,
tt4.vehicle||tt3.vehicle||tt1.vehicle as vehicle,tt1.engine||tt4.engine as engine,tt1.sod || tt3.sod as sod,
tt4.sop||tt1.sop||tt3.sop as sop,tt4.emission||tt1.emmision||tt3.emmission as emission,
tt3.gear||tt4.transmission||tt1.transmission as transmission,tt3.immo||tt4.immo||tt1.immo as immo,
tt3.new_component||tt4.new_componet||tt1.new_component||tt2.new_component as new_component
,t.proj_type as proj_type,t.internal_order as internal_order,ppc.cname as ph1,ppc1.cname as ph2,
tt2.bnumber||t.apms_b_number as apms_b_number,tt2.pnumber||t.apms_0_number as apms_p_number,
pe1.login_name||tt1.login_name||tt2.pm as apms_pm
from
pm_project t left join
(
select ptp.project_code,ptp.new_component,ptp.immo,ptp.gear,ptp.emmission,ptp.sop,ptp.sod,ptp.vehicle
from pm_tcu_project ptp left join pm_tcu_component_project ptcp on ptp.project_code = ptcp.parentno
) tt3
on t.proj_code = tt3.project_code left join
(
select pep.project_code,pep.new_componet,pep.immo,pep.transmission,pep.emission,pep.sop,
pep.engine,pep.vehicle
from
pm_ex_project pep left join pm_ex_component_project pecp on pep.project_code = pecp.parentno
) tt4
on t.proj_code = tt4.project_code left join
(
select apf.piid,pe2.login_name,apf.new_component,apf.immo,apf.transmission,apf.emmision,apf.sop,apf.sod,
apf.engine,apf.vehicle
from
apms_prjinfo apf left join pm_employee pe2 on apf.pm = pe2.login_name
) tt1
on t.apms_sys_id = tt1.piid left join
(
select apl.new_component,apl.id,apl.pm,apl.pnumber,pe3.login_name,apl.bnumber
from
apms_projectlist apl left join pm_employee pe3 on apl.pm = pe3.login_name
) tt2
on t.apms_prj_id = tt2.id left join pm_product_component ppc on t.ph1 = ppc.uniqueid left join pm_product_component ppc1 on t.ph2 = ppc1.uniqueid left join pm_employee pe1 on t.proj_manager = pe1.uniqueid )
;查询效率实在太低了。谁能帮我改下。现在平均查询时间在10秒左右,希望能在3秒左右
^_^
不要有事没事用left join
涉及到SQL执行调优的,一般要给出
表的数据规模,表上的索引,
执行计划等。
能否考虑将放到存储过程来实现,
在存储过程中建立临时表,并为临时表建立相关索引