2018-05-23 孙小北

SQL Serever 查询数据超时

在使用Sql Server时,查询数据超时,30000条数据竟然消耗50s


案例一:原SQL语句如下,分析发现是视图PeFilter第三个自连接没有数据导致的(注释部分语句),具体为什么会这样还没搞清,注释掉这一行明显快了很多。

select pf.* ,vpa.ApplicantName,vpa.BatchName,vpa.GlOrgID		
    ,vpa.PeOrgID,vpa.OrgName,vpa.PePositionID,vpa.PositionName,vpa.PositionSn		
    ,vpa.PID,vpa.Phone		
    ,prebds.Name PreStatusName,nextbds.Name NextStatusName		
    ,vpa.Sex,vpa.Birthday,vpa.NativePlace,vpa.RegistrationName,vpa.NationName,vpa.PoliticsStatusName
    ,vpa.FirstSchool,vpa.FirstEducationCD,vpa.FirstEducationName,vpa.HighestSchool,vpa.HighestEducationCD,vpa.HighestEducationName
    ,vpa.FirstMajor,vpa.HighestMajor,vpa.Company		
    ,pfw.Score WrittenScore,pfw.RankNo WrittenRankNo
    ,pfi.Score InterviewScore,pfi.RankNo InterviewRankNo		
    --,pft.Score TryScore,pft.RankNo TryRankNo
    ,(select TicketSn from ViPeTicket wpt where wpt.PeApplicationID=pf.PeApplicationID and wpt.PeExamTypeCD='WRITTEN') WrittenTicketSn
    ,(select TicketSn from ViPeTicket wpt where wpt.PeApplicationID=pf.PeApplicationID and wpt.PeExamTypeCD='INTERVIEW')InterviewTicketSn
      from PeFilter pf
        left join ViPeApplication vpa on pf.PeApplicationID=vpa.PeApplicationID	
        left join BsDataStatus prebds on prebds.BsDataStatusCD=pf.PreStatusCD	
        left join BsDataStatus nextbds on nextbds.BsDataStatusCD=pf.NextStatusCD
        left join PeFilter pfw on pfw.PeApplicationID=pf.PeApplicationID and pfw.PreStatusCD='Filter_TOWRITTEN'and pfw.IsDeleted=0	
        left join PeFilter pfi on pfi.PeApplicationID=pf.PeApplicationID and pfi.PreStatusCD='Filter_TOINTERVIEW'and pfi.IsDeleted=0	
        --left join PeFilter pft on pft.PeApplicationID=pf.PeApplicationID and pft.PreStatusCD='Filter_TOTRY' and pft.IsDeleted=0	
        where pf.IsDeleted=0	and  pf.PeBatchID=109

1.png   QQ截图20180603111117.png

将自连接改为嵌套查询后,性能提升了很多:

select pf.* ,vpa.ApplicantName,vpa.BatchName,vpa.GlOrgID
    ,vpa.PeOrgID,vpa.OrgName,vpa.PePositionID,vpa.PositionName,vpa.PositionSn		
    ,vpa.PID,vpa.Phone		
    ,prebds.Name PreStatusName,nextbds.Name NextStatusName		
    ,vpa.Sex,vpa.Birthday,vpa.NativePlace,vpa.RegistrationName,vpa.NationName,vpa.PoliticsStatusName		
    ,vpa.FirstSchool,vpa.FirstEducationCD,vpa.FirstEducationName,vpa.HighestSchool,vpa.HighestEducationCD,vpa.HighestEducationName		
    ,vpa.FirstMajor,vpa.HighestMajor,vpa.Company		
    ,(select pfw.Score from PeFilter pfw where pfw.PeApplicationID=pf.PeApplicationID and pfw.PreStatusCD='Filter_TOWRITTEN'and pfw.IsDeleted=0) WrittenScore		
    ,(select pfw.RankNo from PeFilter pfw where pfw.PeApplicationID=pf.PeApplicationID and pfw.PreStatusCD='Filter_TOWRITTEN'and pfw.IsDeleted=0) WrittenRankNo		
    --,pfw.Score WrittenScore,pfw.RankNo WrittenRankNo		
    ,(select pfi.Score from PeFilter pfi where pfi.PeApplicationID=pf.PeApplicationID and pfi.PreStatusCD='Filter_TOINTERVIEW'and pfi.IsDeleted=0) InterviewScore		
    ,(select pfi.RankNo from PeFilter pfi where pfi.PeApplicationID=pf.PeApplicationID and pfi.PreStatusCD='Filter_TOINTERVIEW'and pfi.IsDeleted=0) InterviewRankNo		
    -- ,pfi.Score InterviewScore,pfi.RankNo InterviewRankNo		
    ,(select pft.Score from PeFilter pft where pft.PeApplicationID=pf.PeApplicationID and pft.PreStatusCD='Filter_TOTRY' and pft.IsDeleted=0) TryScore		
    ,(select pft.RankNo from PeFilter pft where pft.PeApplicationID=pf.PeApplicationID and pft.PreStatusCD='Filter_TOTRY' and pft.IsDeleted=0) TryRankNo		
    --,pft.Score TryScore,pft.RankNo TryRankNo		
    ,(select TicketSn from ViPeTicket wpt where wpt.PeApplicationID=pf.PeApplicationID and wpt.PeExamTypeCD='WRITTEN') WrittenTicketSn		
    ,(select TicketSn from ViPeTicket wpt where wpt.PeApplicationID=pf.PeApplicationID and wpt.PeExamTypeCD='INTERVIEW')InterviewTicketSn	
    from PeFilter pf	left join ViPeApplication vpa on pf.PeApplicationID=vpa.PeApplicationID	left join BsDataStatus prebds on prebds.BsDataStatusCD=pf.PreStatusCD	
    left join BsDataStatus nextbds on nextbds.BsDataStatusCD=pf.NextStatusCD		
    --left join PeFilter pfw on pfw.PeApplicationID=pf.PeApplicationID and pfw.PreStatusCD='Filter_TOWRITTEN'and pfw.IsDeleted=0	
    --left join PeFilter pfi on pfi.PeApplicationID=pf.PeApplicationID and pfi.PreStatusCD='Filter_TOINTERVIEW'and pfi.IsDeleted=0	
    --left join PeFilter pft on pft.PeApplicationID=pf.PeApplicationID and pft.PreStatusCD='Filter_TOTRY' and pft.IsDeleted=0	
    where pf.IsDeleted=0	
    and  pf.PeBatchID=109

3.png



编辑:孙小北

本文地址: https://www.xiaowangyun.com/wyblog/detail/?id=182

版权归属: www.xiaowangyun.com   转载时请以链接形式注明出处

0 条评论

快来评论

物以类聚

最新评论

2017-10-06

一辈子不长,只有珍惜了,才不至于后悔。

2017-10-06

懂得感恩,才能走得更远。

标签云

归档

取消

感谢您的支持,您的每一次打赏都是一次鼓励!

扫码支持
每一次支持,都是不懈的动力

打开支付宝扫一扫,即可进行扫码打赏哦