from JSHX_EM_StkRecordLine srl
left join JSHX_EM_StkRecord sr on srl.cHeadGUID=sr.cGUID
left join CM_Material cm on srl.cMatGUID=cm.cGUID
left join CM_Unit cu on srl.cUnitGUID = cu.cGUID
left join CM_Unit cMu ON srl.cMUnitGUID = cMu.cGUID
left join JSHX_EM_Area ea on srl.cMatArea=ea.cGUID
left join ST_MatPosition mp on srl.cGUID = mp.cStkLineID
left join cm_customer cc on sr.cCustGUID =cc.cGUID
where
iAuditStatus='1' and
srl.iOutQTY<srl.iQTY and
sr.cBusType='04001' and
cStkOutLineStatus='0' and
sr.cCustGUID ='533411669541064724' and
sr.cStoreGUID = '533411669541001619'红色是固定条件
蓝色是动态
如何优化 查询 目前五六万属于 查询需要十几秒 求高手支招
若建立索引如何建立。
left join JSHX_EM_StkRecord sr on srl.cHeadGUID=sr.cGUID
left join CM_Material cm on srl.cMatGUID=cm.cGUID
left join CM_Unit cu on srl.cUnitGUID = cu.cGUID
left join CM_Unit cMu ON srl.cMUnitGUID = cMu.cGUID
left join JSHX_EM_Area ea on srl.cMatArea=ea.cGUID
left join ST_MatPosition mp on srl.cGUID = mp.cStkLineID
left join cm_customer cc on sr.cCustGUID =cc.cGUID
where
iAuditStatus='1' and
srl.iOutQTY<srl.iQTY and
sr.cBusType='04001' and
cStkOutLineStatus='0' and
sr.cCustGUID ='533411669541064724' and
sr.cStoreGUID = '533411669541001619'红色是固定条件
蓝色是动态
如何优化 查询 目前五六万属于 查询需要十几秒 求高手支招
若建立索引如何建立。
select top 9999999
srl.cGUID,
srl.cMatGUID,
srl.cMatArea,
srl.cReelNum,
srl.cUnitGUID,
cast(srl.iQTY as decimal(21,3)) as iQTY,
cast((srl.iQTY-srl.iOutQTY) as decimal(21,3)) as iCurQuan,
srl. cMUnitGUID,
cast(srl.iUnitQTY as decimal(21,3)) as iUnitQTY,
srl.cRe,
sr.cBillCode,
CONVERT(varchar(100),sr.dDate, 23) as dDate,
cm.cMatCode,
cm.cMatName,
cm.cSpec,
cu.cName as cUnitName,
cMu.cName as cMUnitName,
ea.cName as cMatAreaName,
cc.cName as cCustName,
srl.cDefine7 as cDefine7,
srl.cDefine9 as cDefine9,
mp.cPositionGUID,cm.cDefine1
from JSHX_EM_StkRecordLine srl
left join JSHX_EM_StkRecord sr on srl.cHeadGUID=sr.cGUID
left join CM_Material cm on srl.cMatGUID=cm.cGUID
left join CM_Unit cu on srl.cUnitGUID = cu.cGUID
left join CM_Unit cMu ON srl.cMUnitGUID = cMu.cGUID
left join JSHX_EM_Area ea on srl.cMatArea=ea.cGUID
left join ST_MatPosition mp on srl.cGUID = mp.cStkLineID
left join cm_customer cc on sr.cCustGUID =cc.cGUID
where
iAuditStatus='1' and
srl.iOutQTY<srl.iQTY and
sr.cBusType='04001' and
cStkOutLineStatus='0' and
sr.cCustGUID ='533411669541064724' and
sr.cStoreGUID = '533411669541001619'
2、检查where里面的字段是否有索引?
你有装SQLServer Management studio吗?用那个生成执行计划好看点,你这个实在看不懂
如果用inner join可以将where中的筛选条件放到on处
--------------
然后on中的所有关联条件要建立索引,where中的筛选列要建立索引
select列表视情况而定,可以考虑覆盖索引
生成一个txt的看看
其他0楼所提的所有字段都有索引了吗?
1)不要有超过5个以上的表连接(JOIN)
2)考虑使用临时表或表变量存放中间结果。
3)少用子查询
4)视图嵌套不要过深用临时表存放中间结果集试试
ST_MatPosition(cStkLineID)
需要索引你的图不够全,更重要的没有看到总体来说,你的所有关联字段没有索引,所以出现很多聚集索引扫描或者表扫描
left join JSHX_EM_StkRecord sr on srl.cHeadGUID=sr.cGUID
应该改成
inner join JSHX_EM_StkRecord sr on srl.cHeadGUID=sr.cGUID
JSHX_EM_StkRecordLine srl、JSHX_EM_StkRecord
这两个表在where出现的字段没有索引(看图好像是聚集索引扫描),加上索引速度会有数量级的提高
cStkOutLineStatus='0' and
这两个字段哪个表的
在JSHX_EM_StkRecordLine srl、JSHX_EM_StkRecord
这两个表根据使用的字段建立组合索引,重复率低的字段排在前面
另外,这两个表需要关注数据量,数据量大的索引才有效果
http://blog.csdn.net/dba_huangzj/article/details/7643763
sr.iAuditStatus='1'
srl.cStkOutLineStatus='0'
这个条件的记录有多少
srl.cStkOutLineStatus有无索引
srl.cStkOutLineStatus='0'占大部分
cStkOutLineStatus
最好增加一个字段(sql server可以用计算字段,也可以考虑触发器维护这个字段)存放iQTY - iOutQTY的值,并建立索引,查询条件改为 这个字段 > 0
sr.cBusType='04001' and
sr.cCustGUID ='533411669541064724' and
sr.cStoreGUID = '533411669541001619'这四个字段都重复率很高吗,怎么全部是扫描,考虑建立一个四字段的组合索引