sql语句求大神优化

[a6772529]

if :v_djbh = '%' and :z_xgdjbh = '%'
begin
select a.is_zx,c.djbh,a.rq,a.ywy,d.spsx,a.username,b.quyufl,b.kpy,b.danwbh,b.dwmch,b.lxr,a.bm,d.spbh,d.spmch,d.shpgg,d.tongym,d.dw,d.baozhgg,d.dzjgm,e.huowname,
c.shl,c.dj,c.hshj,c.je,c.shlv,c.hsje,c.she,c.pfpj,c.pfpj*c.shl as '牌价金额',c.pihao,c.baozhiqi,c.sxrq,c.jwh,c.baozhshl,c.lingsshl,d.jixing,d.leibie,a.jingd,
d.shengccj,d.shpchd,c.xgdjbh,a.is_xiug,c.beizhu,c.is_zdph,d.is_cg,c.chbdj,c.shl*(c.dj-c.chbdj) as ml,a.is_fp,d.laihxz
from pf_djhz a(nolock) join mchk b(nolock) on b.dwbh=a.dwbh
join pf_djmx c(nolock) on c.djbh=a.djbh
join spkfk d(nolock) on d.spid=c.spid
left join huoweizl e(nolock) on c.hw=e.hw
where c.djbh like 'XSG%' and b.ywy like :z_ywy and a.username like :z_czy
and a.rq between :v_start_rq and :end_rq
and b.danwbh like :vdwbh and b.dwmch like :vdwmc
and d.spbh like :z_spbh
and c.pihao like :z_pihao
and a.jianhy like :z_jhy
and d.spsx like :ccspsx
and a.is_zx='否'
and a.is_zhongyao like :v_zhongyao
and b.quyufl like :v_quyufl
and b.kpy like :v_kpy
and a.bendian = :p_bendian
end
else
begin
select c.djbh,a.rq,a.ywy,d.spsx,a.username,b.quyufl,b.kpy,b.danwbh,b.dwmch,d.spbh,d.spmch,d.shpgg,d.tongym,d.dw,d.baozhgg,d.dzjgm,e.huowname,
c.shl,c.dj,c.hshj,c.je,c.shlv,c.hsje,c.she,c.pihao,c.baozhiqi,c.sxrq,c.jwh,c.baozhshl,c.lingsshl,d.jixing,d.leibie,a.jingd,
d.shengccj,d.shpchd,c.xgdjbh,a.is_xiug,c.beizhu,c.chbdj,c.shl*(c.dj-c.chbdj) as ml,a.is_fp,d.laihxz
from pf_djhz a(nolock) join mchk b(nolock) on b.dwbh=a.dwbh
join pf_djmx c(nolock) on c.djbh=a.djbh
join spkfk d(nolock) on d.spid=c.spid
left join huoweizl e(nolock) on c.hw=e.hw
where c.djbh like 'XSG%'
and a.xgdjbh like :z_xgdjbh
and a.djbh like :v_djbh
and b.quyufl like :v_quyufl
and b.kpy like :v_kpy
and a.bendian = :p_bendian
end

:XXXX那些都是参数

[薛定谔的DBA]

不影响结果前提下，尽量用 左连接 left join

[gw6328]

引用 1 楼 kk185800961 的回复:

不影响结果前提下，尽量用 左连接 left join

你确定？不要误导哦

[LongRui888]

光看这些很难优化，你把执行计划贴出来看看吧

[Landa_Tiny_cui]

引用 4 楼 Landa_Ran 的回复:

[quote=引用 1 楼 kk185800961 的回复:] 不影响结果前提下，尽量用 左连接 left join

尽量用内连接[/quote] 如果内连接的话一旦有一个数据有误 就会全部查不到，尽量用左联接比较好

[许晨旭]

引用 5 楼 Landa_Tiny_cui 的回复:

[quote=引用 4 楼 Landa_Ran 的回复:] [quote=引用 1 楼 kk185800961 的回复:] 不影响结果前提下，尽量用 左连接 left join

尽量用内连接[/quote] 如果内连接的话一旦有一个数据有误 就会全部查不到，尽量用左联接比较好[/quote] 注意前提：不影响结果的情况下

[薛定谔的DBA]

第一张表能过滤的，左连接会先过滤。内连接可能会先把所有表连接算出结果集才过滤。这执行计划就不一样了。左连接多用嵌套，内连接如果用hash就消耗内存，当然系统自动优化可能都会用到两种情况

[许晨旭]

引用 1 楼 kk185800961 的回复:

不影响结果前提下，尽量用 左连接 left join

尽量用内连接