SQL查询效率有关问题

SQL查询效率问题？

SQL code

select ww.*,qq.renshu from(select a.*,b.mingcheng,c.mingma,d.mingcheng as mch,e.mingcheng as pl,f.DFMingCheng as zj from Tbl_checkIn a,tbl_diqu b,tbl_ShouPiao c,tbl_YanPiaoDian d,tbl_PiaoLei e,Tbl_SFZ f where a.diqu=b.bianhao and a.KaHao=c.kahao  and a.CheckInNo=d.bianhao and left(a.zhengjian,6)=f.SFZBianHao and a.piaolei=e.bianhao and mingma>='55622'  and mingma<='55634' and convert(char(10),CheckInTime,120) >= '20120615' and convert(char(10),CheckInTime,120) <= '20120625') wwjoin (select zhengjian,sum(renshu) as renshu from tbl_checkin group by zhengjian) qq on ww.zhengjian =qq.zhengjian

请教各位老师，这条语句查询时间需要35至50秒左右，学艺不精不懂优化，麻烦老师指点，谢谢！

[解决办法]
日期连接（AND CONVERT(CHAR(10), CheckInTime, 120) >= '20120615'这块）可以把方式改一下
如CheckInTime>="2012-06-15 00:000:000"

LEFT(a.zhengjian, 6) 这块可以在表中加个字段，存储LEFT(a.zhengjian, 6)
[解决办法]
1.不确定你的字段哪个是索引，select查询索引应该放到第一的位置，而且聚集索引优先于非聚集索引。
2.查询条件的where排序，试一试替换一下条件的先后顺序，结果会不一样
3.如果不用执行计划的话，可以试试这个方法测试查询结果
set statistics io on
set statistics time on
select 语句...
set statistics io off
set statistics time off

查看更多 下一篇