原始语句
select t_dealinfojg.*,cp.*,yl.* from t_dealinfojg
left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino ,
t_stockin left join bs_comm as YL on si_ccid=YL.c_cid
where di_csiwd=si_cwd and di_isino=si_ino and di_csiwd<>'' and di_csiwd is not null and di_ddate>='2008-09-09 00:00:00'
and dcp_dstockin is null and di_bvalid=0 order by di_ddate,di_cwd,di_ino
查询时间50s,记录1580
第一次修改,去除t_stockin,因为此表记录多,并且此查询中多余
select t_dealinfojg.*,cp.*,yl.* from t_dealinfojg
left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino
left join bs_comm as YL on di_ccid=YL.c_cid
where di_csiwd<>'' and di_csiwd is not null and di_ddate>='2008-09-09 00:00:00'
and dcp_dstockin is null and di_bvalid=0 order by di_ddate,di_cwd,di_ino
查询时间20s
第二次修改,将left join 改成 inner join
select t_dealinfojg.*,cp.*,yl.* from t_dealinfojg
left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino
inner join bs_comm as YL on di_ccid=YL.c_cid
where di_csiwd<>'' and di_csiwd is not null and di_ddate>='2008-09-09 00:00:00'
and dcp_dstockin is null and di_bvalid=0 order by di_ddate,di_cwd,di_ino
查询时间10s
第三次修改,将查询字段数目减少到1/3
select t_dealinfojg.* from t_dealinfojg
left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino
inner join bs_comm as YL on di_ccid=YL.c_cid
where di_csiwd<>'' and di_csiwd is not null and di_ddate>='2008-09-09 00:00:00'
and dcp_dstockin is null and di_bvalid=0 order by di_ddate,di_cwd,di_ino
查询时间为2s
第四次修改,将查询字段数目还原,去除排序
select t_dealinfojg.*,cp.*,yl.* from t_dealinfojg
left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino
inner join bs_comm as YL on di_ccid=YL.c_cid
where di_csiwd<>'' and di_csiwd is not null and di_ddate>='2008-09-09 00:00:00'
and dcp_dstockin is null and di_bvalid=0
查询时间为7s
以下查询结果说明:inner join 与left join和查询字段数目多少对查询速度影响较大
select t_dealinfojg.*,cp.*,yl.* from t_dealinfojg
left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino ,
t_stockin left join bs_comm as YL on si_ccid=YL.c_cid
where di_csiwd=si_cwd and di_isino=si_ino and di_csiwd<>'' and di_csiwd is not null and di_ddate>='2008-09-09 00:00:00'
and dcp_dstockin is null and di_bvalid=0 order by di_ddate,di_cwd,di_ino
查询时间50s,记录1580
第一次修改,去除t_stockin,因为此表记录多,并且此查询中多余
select t_dealinfojg.*,cp.*,yl.* from t_dealinfojg
left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino
left join bs_comm as YL on di_ccid=YL.c_cid
where di_csiwd<>'' and di_csiwd is not null and di_ddate>='2008-09-09 00:00:00'
and dcp_dstockin is null and di_bvalid=0 order by di_ddate,di_cwd,di_ino
查询时间20s
第二次修改,将left join 改成 inner join
select t_dealinfojg.*,cp.*,yl.* from t_dealinfojg
left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino
inner join bs_comm as YL on di_ccid=YL.c_cid
where di_csiwd<>'' and di_csiwd is not null and di_ddate>='2008-09-09 00:00:00'
and dcp_dstockin is null and di_bvalid=0 order by di_ddate,di_cwd,di_ino
查询时间10s
第三次修改,将查询字段数目减少到1/3
select t_dealinfojg.* from t_dealinfojg
left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino
inner join bs_comm as YL on di_ccid=YL.c_cid
where di_csiwd<>'' and di_csiwd is not null and di_ddate>='2008-09-09 00:00:00'
and dcp_dstockin is null and di_bvalid=0 order by di_ddate,di_cwd,di_ino
查询时间为2s
第四次修改,将查询字段数目还原,去除排序
select t_dealinfojg.*,cp.*,yl.* from t_dealinfojg
left join (select * from t_dealinfocp,bs_comm as CpComm where dcp_ccid=CpComm.c_cid ) as Cp on di_cwd=dcp_cdiwd and di_ino=dcp_idino
inner join bs_comm as YL on di_ccid=YL.c_cid
where di_csiwd<>'' and di_csiwd is not null and di_ddate>='2008-09-09 00:00:00'
and dcp_dstockin is null and di_bvalid=0
查询时间为7s
以下查询结果说明:inner join 与left join和查询字段数目多少对查询速度影响较大