在使用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
将自连接改为嵌套查询后,性能提升了很多:
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
编辑:孙小北
本文地址: https://www.xiaowangyun.com/wyblog/detail/?id=182
版权归属: www.xiaowangyun.com 转载时请以链接形式注明出处
0 条评论