SQL Server 2005执行计划

Question

我正在尝试对 SQL Server 2005 中运行缓慢的存储过程进行故障排除。我正在分析执行计划并看到 SORT 为 45%，但我没有使用 ORDER 子句。会是什么原因造成这种情况呢。

更新 SP（已清理，并对 OR 进行更改）

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Rpt_D]
@AsOfDate datetime,

@LEA int,
@SchoolName varchar(max),

@Grade varchar(8000),
@Gender varchar(8000),
@Race varchar(8000),
@UserID int
AS

SET NOCOUNT ON

Declare
@AsOfMonth int,
@AsOfDay int

SET @AsOfMonth = DATEPART(MONTH, @AsOfDate)
SET @AsOfDay = DATEPART(DAY, @AsOfDate)

CREATE TABLE #TempSchool
(
 SchoolESIID int,
 LEAESIID int
)
CREATE TABLE #TempRace
(
 Race varchar(60)
)
CREATE TABLE #TempGender
(
 GenderCode char(1)
)
CREATE TABLE #TempGrade
(
 GradeCode char(2)
)

DECLARE 
 @UserLevel nvarchar(10),
 @ESILEAList nvarchar(max),
 @ESISchoolList nvarchar(max)

EXEC Staging.dbo.GetUserFilter @userId = @userid, @userLevel = @Userlevel out, @ESILEAList = @ESILEAList out, @ESISchoolList = @ESISchoolList out

-- Parse parameters into tables
INSERT INTO #TempSchool
SELECT ParsedValue, PrntESIID 
FROM dbo.tfnParseStringIntoTable(@SchoolName, ',')
 INNER JOIN dbo.CALPADSOrg co ON ParsedValue = ChESIID

INSERT INTO #TempRace
SELECT ParsedValue FROM dbo.tfnParseStringIntoTable(@Race, ',')

INSERT INTO #TempGender
SELECT ParsedValue FROM dbo.tfnParseStringIntoTable(@Gender, ',')

INSERT INTO #TempGrade
SELECT ParsedValue FROM dbo.tfnParseStringIntoTable(@Grade, ',')


 SELECT DISTINCT
  ser.ESIIDLeaRptng AS LEARptngEsiID,
  ser.EsiIDSchlAtndnc AS SchoolESIID, 
  resi.CDSEttyCode AS SchlAtndncCode,
  resi.CDSEttyName AS SchlAtndncName,
  ser.StuKey,
  s.StuIDStwdCal,
  ISNULL(sdr.StuLastOrSrnmLgl,'') + ', ' + ISNULL(sdr.StuFstNameLgl,'') + ' ' + ISNULL(sdr.StuMdlNameLgl,'') AS StudentName,
  ser.StuIDLcl,
  rg.GndrCode AS GndrCode,
  ISNULL(
   CASE
    WHEN sdr.StuHspncEnctyIndctr = 'Y' 
     THEN 'Hispanic'
    WHEN sdr.StuEnctyMsngIndctr = 'Y' OR sdr.StuRaceMsngIndctr = 'Y' 
     THEN 'Missing'
    WHEN srr.RaceCatg2Code IS NOT NULL
     THEN 'Multiple'
    ELSE srr.FedEnctyRaceCatgCode
   END, 'Missing') AS RaceEnthnicity,
  rgl.GrdLvlCode AS GrdLvlCode,
  ISNULL(
   CASE relass.EngLangAcqstnStatStCode
    WHEN 'EL' 
     THEN 'Y'
    ELSE 'N'
   END, 'N') AS EnglishLearner,
  ISNULL(
   CASE
    WHEN ISNULL(sdr.StuIneligSnorImgrntIndctr, 'Y') = 'N' 
      AND ISNULL(ssr.StuEnrldUSSchlLessThanThreCumltvYrsIndctr, 'N') = 'Y' 
      AND ISNULL(sdr.rptCntryCode, 'US') != 'US' 
      AND ISNULL(res.EnrlmtStatCode, '0') = '10' 
      AND ISNULL(
       CASE 
        WHEN sdr.StuBirMonth < @AsOfMonth
         THEN DATEDIFF(YEAR, sdr.StuBirDate, @AsOfDate)
        WHEN sdr.StuBirMonth = @AsOfMonth  AND sdr.StuBirDay <= @AsOfDay
         THEN DATEDIFF(YEAR, sdr.StuBirDate, @AsOfDate)
        ELSE DATEDIFF(YEAR, sdr.StuBirDate, GETDATE()) -1
       END,0) BETWEEN 3 AND 21 
      AND ISNULL(rgl.GrdLvlCode, 'AD') != 'AD'
     THEN 'Y'
    ELSE 'N'
   END, 'N') AS TitleIIIEligibleImmigrantFlag,
  ISNULL(
   CASE
    WHEN ISNULL(rep.EduPgmCode, 000) = 175 OR ISNULL(rhel.HighstEduLvlCode, 0) = 14
     THEN 'Y' 
    ELSE 'N'
   END, 'N') AS SocioEconomicallyDisadvantagedFlag,
  ISNULL(
   CASE
    WHEN relass.EngLangAcqstnStatStCode IN ('EL', 'RFEP') AND relatp.EngLangArtsTestProfcyCode = 'N' 
     THEN 'Y'
    ELSE 'N'
   END, 'N') AS LimitedEnglishProficientFlag,
  ISNULL(
   CASE rep.EduPgmCode
    WHEN '135' 
     THEN 'Y'
    ELSE 'N'
   END, 'N') AS TitleIPartCMigrantFlag,
  ISNULL(
   CASE rep.EduPgmCode
    WHEN '144' 
     THEN 'Y'
    ELSE 'N'
   END, 'N') AS SpecialEducationFlag ,
  ISNULL(
   CASE rep.EduPgmCode
    WHEN '127' 
     THEN 'Y'
    ELSE 'N'
   END, 'N') AS GiftedAndTalentedFlag
 From 
  dbo.StuEnrlmt ser
  INNER JOIN dbo.Stu s ON ser.StuKey = s.StuKey
  INNER JOIN #TempSchool ts ON ser.EsiIDSchlAtndnc = ts.SchoolESIID 
          AND (ser.EsiIDLEARptng = @LEA)
  INNER JOIN RefEductlSrvcInstn resi ON ts.SchoolESIID = resi.ESIID
  INNER JOIN dbo.RefEnrlmtStat res ON ser.EnrlmtStatKey = res.EnrlmtStatKey
  LEFT JOIN dbo.RefStuExitCatg rsec ON ser.StuExitCatgKey = rsec.StuExitCatgKey
  LEFT JOIN dbo.StuEngLangArt selar ON ser.StuKey = selar.StuKey 
          AND (selar.EfctvStartDate <= @AsOfDate)
          AND ((selar.EfctvEndDate IS NULL) OR (selar.EfctvEndDate >= @AsOfDate))
  LEFT JOIN dbo.RefEngLangAcqstnStatSt relass ON selar.EngLangAcqstnStatStKey = relass.EngLangAcqstnStatStKey
  LEFT JOIN dbo.RefEngLangArtsTestProfcy relatp ON selar.ElaTestProfcyDsgntnKey = relatp.EngLangArtsTestProfcyKey
  INNER JOIN dbo.StuDemo sdr ON ser.StuKey = sdr.StuKey
  INNER JOIN dbo.RefGndr rg ON sdr.GndrCodeKey = rg.GndrCodeKey
  LEFT JOIN dbo.StuStat ssr ON ser.StuKey = ssr.StuKey 
          AND (ssr.EfctvStartDate <= @AsOfDate)
          AND ((ssr.EfctvEndDate IS NULL) OR (ssr.EfctvEndDate >= @AsOfDate))
  INNER JOIN dbo.StuGrdLvl sglr ON ser.StuKey = sglr.StuKey
  INNER JOIN dbo.RefGrdLvl rgl ON sglr.GrdLvlKey = rgl.GrdLvlKey
  LEFT JOIN dbo.StuPgm spr ON ser.StuKey = spr.StuKey 
          AND (spr.StuEduPgmMbrshpCatgStartDate <= @AsOfDate )
          AND ((spr.StuEduPgmMbrshpCatgEndDate IS NULL) OR (spr.StuEduPgmMbrshpCatgEndDate >= @AsOfDate))
  LEFT JOIN dbo.RefEduPgm rep ON spr.EduPgmCodeKey = rep.EduPgmCodeKey
  LEFT JOIN dbo.StuPrntOrGrdn spogr ON ser.StuKey = spogr.StuKey 
          AND (spogr.EfctvStartDate <= @AsOfDate)
          AND ((spogr.EfctvEndDate IS NULL) OR (spogr.EfctvEndDate >= @AsOfDate))
  LEFT JOIN dbo.RefHighstEduLvl rhel ON spogr.PrntOrGrdnHighstEduLvlCodeKey = rhel.HighstEduLvlKey
  LEFT JOIN dbo.vwStuRaceRptng srr ON ser.StuKey = srr.StuKey 
          AND (srr.EfctvStartDate <= @AsOfDate)
          AND ((srr.EfctvEndDate IS NULL) OR (srr.EfctvEndDate >= @AsOfDate))
  INNER JOIN #TempRace tr ON 
   ISNULL(
     CASE
     WHEN sdr.StuHspncEnctyIndctr = 'Y' 
      THEN 'Hispanic'
     WHEN sdr.StuEnctyMsngIndctr = 'Y' OR sdr.StuRaceMsngIndctr = 'Y' 
      THEN 'Missing'
     WHEN srr.RaceCatg2Code IS NOT NULL
      THEN 'Multiple'
     ELSE srr.FedEnctyRaceCatgCode
    END, 'Missing') = tr.Race  
  INNER JOIN #TempGender tg ON rg.GndrCode = tg.GenderCode 
  INNER JOIN #TempGrade tgr ON rgl.GrdLvLCode = tgr.GradeCode  

 WHERE (ser.StuEsiRltnspExpctdSchlStartDate <= @AsOfDate)
  AND ((ser.WithdrlDate IS NULL) OR (ser.WithdrlDate >= @AsOfDate))
  AND (res.EnrlmtStatCode = '10')
  AND (ISNULL(rsec.StuExitCatgCode, 'N/A') != 'N470')
  AND (sdr.EfctvStartDate <= @AsOfDate)
  AND ((sdr.EfctvEndDate IS NULL) OR (sdr.EfctvEndDate >= @AsOfDate))
  AND (sglr.EfctvStartDate <= @AsOfDate)
  AND ((sglr.EfctvEndDate IS NULL) OR (sglr.EfctvEndDate >= @AsOfDate))
  AND ((spr.DeleteFlag IS NULL) OR (spr.DeleteFlag = 'N'))
  AND ((spogr.DeleteFlag IS NULL) OR (spogr.DeleteFlag = 'N'))
  AND ((sglr.DeleteFlag IS NULL) OR (sglr.DeleteFlag = 'N'))
  AND ((selar.DeleteFlag IS NULL) OR (selar.DeleteFlag = 'N'))
  AND ((sdr.DeleetFlag IS NULL) OR (sdr.DeleetFlag = 'N'))
  AND ((ser.DeleteFlag IS NULL) OR (ser.DeleteFlag = 'N'))

DROP TABLE #TempSchool
DROP TABLE #TempRace
DROP TABLE #TempGender
DROP TABLE #TempGrade

vwStuRaceRptng 查看

SELECT     sr.StuRaceKey, sr.StuKey, rr1.RaceCatgCode, rr1.RaceCatgName, rferc1.FedEnctyRaceCatgKey, rferc1.FedEnctyRaceCatgCode, rferc1.FedEnctyRaceCatgName, 
                      rr2.RaceCatgCode AS RaceCatg2Code, rr2.RaceCatgName AS RaceCatg2Name, rferc2.FedEnctyRaceCatgKey AS FedEnctyRaceCatg2Key, 
                      rferc2.FedEnctyRaceCatgCode AS FedEnctyRaceCatg2Code, rferc2.FedEnctyRaceCatgName AS FedEnctyRaceCatg2Name, rr3.RaceCatgCode AS RaceCatg3Code, 
                      rr3.RaceCatgName AS RaceCatg3Name, rferc3.FedEnctyRaceCatgKey AS FedEnctyRaceCatg3Key, rferc3.FedEnctyRaceCatgCode AS FedEnctyRaceCatg3Code, 
                      rferc3.FedEnctyRaceCatgName AS FedEnctyRaceCatg3Name, rr4.RaceCatgCode AS RaceCatg4Code, rr4.RaceCatgName AS RaceCatg4Name, 
                      rferc4.FedEnctyRaceCatgKey AS FedEnctyRaceCatg4Key, rferc4.FedEnctyRaceCatgCode AS FedEnctyRaceCatg4Code, 
                      rferc4.FedEnctyRaceCatgName AS FedEnctyRaceCatg4Name, rr5.RaceCatgCode AS RaceCatg5Code, rr5.RaceCatgName AS RaceCatg5Name, 
                      rferc5.FedEnctyRaceCatgKey AS FedEnctyRaceCatg5Key, rferc5.FedEnctyRaceCatgCode AS FedEnctyRaceCatg5Code, 
                      rferc5.FedEnctyRaceCatgName AS FedEnctyRaceCatg5Name, sr.EfctvStartDate, sr.EfctvEndDate
FROM         dbo.StuRace AS sr INNER JOIN
                      dbo.RefRace AS rr1 ON sr.RaceCatgKey = rr1.RaceCatgKey LEFT OUTER JOIN
                      dbo.RefRace AS rr2 ON sr.RaceCatg2Key = rr2.RaceCatgKey LEFT OUTER JOIN
                      dbo.RefRace AS rr3 ON sr.RaceCatg3Key = rr3.RaceCatgKey LEFT OUTER JOIN
                      dbo.RefRace AS rr4 ON sr.RaceCatg4Key = rr4.RaceCatgKey LEFT OUTER JOIN
                      dbo.RefRace AS rr5 ON sr.RaceCatg5Key = rr5.RaceCatgKey LEFT OUTER JOIN
                      dbo.RefFedEnctyRaceCatg AS rferc1 ON rr1.FedEnctyRaceCatgKey = rferc1.FedEnctyRaceCatgKey LEFT OUTER JOIN
                      dbo.RefFedEnctyRaceCatg AS rferc2 ON rr2.FedEnctyRaceCatgKey = rferc2.FedEnctyRaceCatgKey LEFT OUTER JOIN
                      dbo.RefFedEnctyRaceCatg AS rferc3 ON rr3.FedEnctyRaceCatgKey = rferc3.FedEnctyRaceCatgKey LEFT OUTER JOIN
                      dbo.RefFedEnctyRaceCatg AS rferc4 ON rr4.FedEnctyRaceCatgKey = rferc4.FedEnctyRaceCatgKey LEFT OUTER JOIN
                      dbo.RefFedEnctyRaceCatg AS rferc5 ON rr5.FedEnctyRaceCatgKey = rferc5.FedEnctyRaceCatgKey
WHERE     (ISNULL(sr.DeleteFlag, 'N') = 'N')

旧 SP

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATEPROCEDURE [dbo].[Rpt_D]
@AsOfDate datetime,
@LEA int,
@SchoolName varchar(max),
@Grade varchar(8000),
@Gender varchar(8000),
@Race varchar(8000),
@UserID int
AS
Declare
@AsOfMonth int,
@AsOfDay int

select @AsOfMonth = datepart(month, @AsOfDate)
select @AsOfDay = datepart(day, @AsOfDate)

Create table #TempSchool
(
 SchoolESIID int,
 LEAESIID int
)
Create table #TempRace
(
 Race varchar(60)
)
Create table #TempGender
(
 GenderCode char(1)
)
Create table #TempGrade
(
 GradeCode char(2)
)

declare 
 @UserLevel nVarchar(10),
 @ESILEAList nvarchar(max),
 @ESISchoolList nvarchar(max)

exec Staging.dbo.GetUserFilter @userId=@userid,@userLevel=@Userlevel out,@ESILEAList=@ESILEAList out,@ESISchoolList=@ESISchoolList out

-- Parse parameters into tables
Insert into #TempSchool
select 
 ParsedValue, PrntESIID 
from 
   dbo.tfnParseStringIntoTable(@SchoolName, ',')
inner join dbo.CALPADSOrg co on ParsedValue = ChESIID

Insert into #TempRace
select ParsedValue from dbo.tfnParseStringIntoTable(@Race, ',')

Insert into #TempGender
select ParsedValue from dbo.tfnParseStringIntoTable(@Gender, ',')

Insert into #TempGrade
select ParsedValue from dbo.tfnParseStringIntoTable(@Grade, ',')




 Select DISTINCT
  ser.ESIIDLeaRptng as LEARptngEsiID,
  ser.EsiIDSchlAtndnc as SchoolESIID, 
  resi.CDSEttyCode as SchlAtndncCode,
  resi.CDSEttyName as SchlAtndncName,
  ser.StuKey,
  s.StuIDStwdCal,
  isnull(sdr.StuLastOrSrnmLgl,'') + ', ' + isnull(sdr.StuFstNameLgl,'') + ' ' + isnull(sdr.StuMdlNameLgl,'') as StudentName,
  ser.StuIDLcl,
  rg.GndrCode as GndrCode,
  isnull(case
   when sdr.StuHspncEnctyIndctr = 'Y' then 'Hispanic'
   when sdr.StuEnctyMsngIndctr = 'Y' or sdr.StuRaceMsngIndctr = 'Y' then 'Missing'
   when srr.RaceCatg2Code is not null then 'Multiple'
   else srr.FedEnctyRaceCatgCode
  end, 'Missing') as RaceEnthnicity,
  rgl.GrdLvlCode as GrdLvlCode,
  isnull(
   case relass.EngLangAcqstnStatStCode
    when 'EL' then 'Y'
    else 'N'
   end, 'N') as EnglishLearner,
  isnull(
   case
   when 
    -- if a value is null, set it to any value that will evaluate to false in the expression
    -- only students with valid information should be counted as Title III Eligible Immigrants
    isnull(sdr.StuIneligSnorImgrntIndctr, 'Y') = 'N' AND
    isnull(ssr.StuEnrldUSSchlLessThanThreCumltvYrsIndctr, 'N') = 'Y' AND
    isnull(sdr.rptCntryCode, 'US') != 'US' AND
    isnull(res.EnrlmtStatCode, '0') = '10' AND
    -- Calculate age from birth date
    isnull(case 
    when sdr.StuBirMonth < @AsOfMonth
     then datediff(year, sdr.StuBirDate, @AsOfDate)
    when sdr.StuBirMonth = @AsOfMonth and sdr.StuBirDay <= @AsOfDay
     then datediff(year, sdr.StuBirDate, @AsOfDate)
    else datediff(year, sdr.StuBirDate, getdate()) -1
    end,0) between 3 and 21 AND
    isnull(rgl.GrdLvlCode, 'AD') != 'AD'
   then 'Y'
   else 'N'
   end, 'N') as TitleIIIEligibleImmigrantFlag,
  isnull(
   case
    when 
     isnull(rep.EduPgmCode, 000) = 175 OR
     isnull(rhel.HighstEduLvlCode, 0) = 14
    then 'Y' 
    else 'N'
   end, 'N') as SocioEconomicallyDisadvantagedFlag,
  isnull(case
     when relass.EngLangAcqstnStatStCode in ('EL', 'RFEP')  AND relatp.EngLangArtsTestProfcyCode = 'N' then 'Y'
     else 'N'
    end, 'N') as LimitedEnglishProficientFlag,
  isnull(case rep.EduPgmCode
     when '135' then 'Y'
     else 'N'
    end, 'N') as TitleIPartCMigrantFlag,
  isnull(case rep.EduPgmCode
     when '144' then 'Y'
     else 'N'
    end, 'N') as SpecialEducationFlag ,
  isnull(case rep.EduPgmCode
     when '127' then 'Y'
     else 'N'
    end, 'N') as GiftedAndTalentedFlag
 From 
  dbo.StuEnrlmt ser
  inner join dbo.Stu s on 
   ser.StuKey = s.StuKey
  inner join #TempSchool ts on 
   ser.EsiIDSchlAtndnc = ts.SchoolESIID and
   ser.EsiIDLEARptng = @LEA
  inner join RefEductlSrvcInstn resi on 
   ts.SchoolESIID = resi.ESIID
  inner join dbo.RefEnrlmtStat res on 
   ser.EnrlmtStatKey = res.EnrlmtStatKey
  left join dbo.RefStuExitCatg rsec on
   ser.StuExitCatgKey = rsec.StuExitCatgKey
  left join dbo.StuEngLangArt selar on 
   ser.StuKey = selar.StuKey and
   selar.EfctvStartDate <= @AsOfDate AND (selar.EfctvEndDate is null OR selar.EfctvEndDate >= @AsOfDate)
  left join dbo.RefEngLangAcqstnStatSt relass on 
   selar.EngLangAcqstnStatStKey = relass.EngLangAcqstnStatStKey
  left join dbo.RefEngLangArtsTestProfcy relatp on 
   selar.ElaTestProfcyDsgntnKey = relatp.EngLangArtsTestProfcyKey
  inner join dbo.StuDemo sdr on
   ser.StuKey = sdr.StuKey
  inner join dbo.RefGndr rg on 
   sdr.GndrCodeKey = rg.GndrCodeKey
  left join dbo.StuStat ssr on
   ser.StuKey = ssr.StuKey and
   ssr.EfctvStartDate   <= @AsOfDate AND (ssr.EfctvEndDate   is null OR ssr.EfctvEndDate >= @AsOfDate)   
  inner join dbo.StuGrdLvl sglr on
   ser.StuKey = sglr.StuKey
  inner join dbo.RefGrdLvl rgl on 
   sglr.GrdLvlKey = rgl.GrdLvlKey
  left join dbo.StuPgm spr on
   ser.StuKey = spr.StuKey AND
   spr.StuEduPgmMbrshpCatgStartDate   <= @AsOfDate AND (spr.StuEduPgmMbrshpCatgEndDate   is null OR spr.StuEduPgmMbrshpCatgEndDate >= @AsOfDate)  
  left join dbo.RefEduPgm rep on 
   spr.EduPgmCodeKey = rep.EduPgmCodeKey
  left join dbo.StuPrntOrGrdn spogr on 
   ser.StuKey = spogr.StuKey And
   spogr.EfctvStartDate <= @AsOfDate AND (spogr.EfctvEndDate is null OR spogr.EfctvEndDate >= @AsOfDate) 
  left join dbo.RefHighstEduLvl rhel on 
   spogr.PrntOrGrdnHighstEduLvlCodeKey = rhel.HighstEduLvlKey
  left join dbo.vwStuRaceRptng srr on
   ser.StuKey = srr.StuKey  and 
   srr.EfctvStartDate   <= @AsOfDate AND (srr.EfctvEndDate   is null OR srr.EfctvEndDate >= @AsOfDate)
  inner join #TempRace tr on 
   isnull(
   case
    when sdr.StuHspncEnctyIndctr = 'Y' then 'Hispanic'
    when sdr.StuEnctyMsngIndctr = 'Y' or sdr.StuRaceMsngIndctr = 'Y' then 'Missing'
    when srr.RaceCatg2Code is not null then 'Multiple'
    else srr.FedEnctyRaceCatgCode
   end, 'Missing') = tr.Race  
  inner join #TempGender tg on
   rg.GndrCode = tg.GenderCode 
  inner join #TempGrade tgr on 
   rgl.GrdLvLCode = tgr.GradeCode  

 Where
  -- Enrollments
  ser.StuEsiRltnspExpctdSchlStartDate <= @AsOfDate AND (ser.WithdrlDate is null OR ser.WithdrlDate >= @AsOfDate) AND
  res.EnrlmtStatCode = '10'  AND
  isnull(rsec.StuExitCatgCode, 'N/A') != 'N470' AND -- no shows are not considered in active enrollment numbers
  -- Effective date Comparisions. As of date should be between Effective Start and End date (end date can be null)
  sdr.EfctvStartDate   <= @AsOfDate AND (sdr.EfctvEndDate   is null OR sdr.EfctvEndDate >= @AsOfDate)   AND
  sglr.EfctvStartDate  <= @AsOfDate AND (sglr.EfctvEndDate  is null OR sglr.EfctvEndDate >= @AsOfDate)  AND
  -- Filter deleted records out
  (spr.DeleteFlag is null OR spr.DeleteFlag = 'N') AND
  (spogr.DeleteFlag is null OR spogr.DeleteFlag = 'N')  AND
  (sglr.DeleteFlag is null OR sglr.DeleteFlag = 'N') AND
  (selar.DeleteFlag is null OR selar.DeleteFlag = 'N') AND
  (sdr.DeleetFlag is null OR sdr.DeleetFlag = 'N') AND
  (ser.DeleteFlag is null OR ser.DeleteFlag = 'N') 


Drop table #TempSchool
Drop table #TempRace
Drop table #TempGender
Drop table #TempGrade

感谢您的帮助。仅供参考，tfnParseStringIntoTable 函数返回一个基于逗号分隔的值列表的表。

Answer 1

    (spr.DeleteFlag is null OR spr.DeleteFlag = 'N') AND 
    (spogr.DeleteFlag is null OR spogr.DeleteFlag = 'N')  AND 
    (sglr.DeleteFlag is null OR sglr.DeleteFlag = 'N') AND 
    (selar.DeleteFlag is null OR selar.DeleteFlag = 'N') AND 
    (sdr.DeleetFlag is null OR sdr.DeleetFlag = 'N') AND 
    (ser.DeleteFlag is null OR ser.DeleteFlag = 'N')  

这始终是低效的代码。您需要停止在删除标志中允许空值。它应该有一个默认值“N”。这将加速您网站上排除已删除记录的所有查询（一旦您重写它们），因此应该非常优先修复。

顺便说一句，我没有看到任何对执行计划部分正在使用调用 StuRace 的表的引用。因此，我认为它要么在表值函数之一中，要么在视图中，或者更糟糕的是在调用视图的视图中。

Answer 2

CTE 版本

;WITH ser as
( SELECT DISTINCT
  *
 From 
  dbo.StuEnrlmt ser
 WHERE (ser.StuEsiRltnspExpctdSchlStartDate <= @AsOfDate)
  AND ((ser.DeleteFlag IS NULL) OR (ser.DeleteFlag = 'N'))
)
--Main select replacing FROM dbo.StueEnrlmt with FROM ser
Select DISTINCT
  ser.ESIIDLeaRptng as LEARptngEsiID,
  ser.EsiIDSchlAtndnc as SchoolESIID, 
  resi.CDSEttyCode as SchlAtndncCode,
  resi.CDSEttyName as SchlAtndncName,
  ser.StuKey,
  s.StuIDStwdCal,
  isnull(sdr.StuLastOrSrnmLgl,'') + ', ' + isnull(sdr.StuFstNameLgl,'') + ' ' + isnull(sdr.StuMdlNameLgl,'') as StudentName,
  ser.StuIDLcl,
  rg.GndrCode as GndrCode,
  isnull(case
   when sdr.StuHspncEnctyIndctr = 'Y' then 'Hispanic'
   when sdr.StuEnctyMsngIndctr = 'Y' or sdr.StuRaceMsngIndctr = 'Y' then 'Missing'
   when srr.RaceCatg2Code is not null then 'Multiple'
   else srr.FedEnctyRaceCatgCode
  end, 'Missing') as RaceEnthnicity,
  rgl.GrdLvlCode as GrdLvlCode,
  isnull(
   case relass.EngLangAcqstnStatStCode
    when 'EL' then 'Y'
    else 'N'
   end, 'N') as EnglishLearner,
  isnull(
   case
   when 
    -- if a value is null, set it to any value that will evaluate to false in the expression
    -- only students with valid information should be counted as Title III Eligible Immigrants
    isnull(sdr.StuIneligSnorImgrntIndctr, 'Y') = 'N' AND
    isnull(ssr.StuEnrldUSSchlLessThanThreCumltvYrsIndctr, 'N') = 'Y' AND
    isnull(sdr.rptCntryCode, 'US') != 'US' AND
    isnull(res.EnrlmtStatCode, '0') = '10' AND
    -- Calculate age from birth date
    isnull(case 
    when sdr.StuBirMonth < @AsOfMonth
     then datediff(year, sdr.StuBirDate, @AsOfDate)
    when sdr.StuBirMonth = @AsOfMonth and sdr.StuBirDay <= @AsOfDay
     then datediff(year, sdr.StuBirDate, @AsOfDate)
    else datediff(year, sdr.StuBirDate, getdate()) -1
    end,0) between 3 and 21 AND
    isnull(rgl.GrdLvlCode, 'AD') != 'AD'
   then 'Y'
   else 'N'
   end, 'N') as TitleIIIEligibleImmigrantFlag,
  isnull(
   case
    when 
     isnull(rep.EduPgmCode, 000) = 175 OR
     isnull(rhel.HighstEduLvlCode, 0) = 14
    then 'Y' 
    else 'N'
   end, 'N') as SocioEconomicallyDisadvantagedFlag,
  isnull(case
     when relass.EngLangAcqstnStatStCode in ('EL', 'RFEP')  AND relatp.EngLangArtsTestProfcyCode = 'N' then 'Y'
     else 'N'
    end, 'N') as LimitedEnglishProficientFlag,
  isnull(case rep.EduPgmCode
     when '135' then 'Y'
     else 'N'
    end, 'N') as TitleIPartCMigrantFlag,
  isnull(case rep.EduPgmCode
     when '144' then 'Y'
     else 'N'
    end, 'N') as SpecialEducationFlag ,
  isnull(case rep.EduPgmCode
     when '127' then 'Y'
     else 'N'
    end, 'N') as GiftedAndTalentedFlag
 From ser
  inner join dbo.Stu s on 
   ser.StuKey = s.StuKey
  inner join #TempSchool ts on 
   ser.EsiIDSchlAtndnc = ts.SchoolESIID and
   ser.EsiIDLEARptng = @LEA
  inner join RefEductlSrvcInstn resi on 
   ts.SchoolESIID = resi.ESIID
  inner join dbo.RefEnrlmtStat res on 
   ser.EnrlmtStatKey = res.EnrlmtStatKey
  left join dbo.RefStuExitCatg rsec on
   ser.StuExitCatgKey = rsec.StuExitCatgKey
  left join dbo.StuEngLangArt selar on 
   ser.StuKey = selar.StuKey and
   selar.EfctvStartDate <= @AsOfDate AND (selar.EfctvEndDate is null OR selar.EfctvEndDate >= @AsOfDate)
  left join dbo.RefEngLangAcqstnStatSt relass on 
   selar.EngLangAcqstnStatStKey = relass.EngLangAcqstnStatStKey
  left join dbo.RefEngLangArtsTestProfcy relatp on 
   selar.ElaTestProfcyDsgntnKey = relatp.EngLangArtsTestProfcyKey
  inner join dbo.StuDemo sdr on
   ser.StuKey = sdr.StuKey
  inner join dbo.RefGndr rg on 
   sdr.GndrCodeKey = rg.GndrCodeKey
  left join dbo.StuStat ssr on
   ser.StuKey = ssr.StuKey and
   ssr.EfctvStartDate   <= @AsOfDate AND (ssr.EfctvEndDate   is null OR ssr.EfctvEndDate >= @AsOfDate)   
  inner join dbo.StuGrdLvl sglr on
   ser.StuKey = sglr.StuKey
  inner join dbo.RefGrdLvl rgl on 
   sglr.GrdLvlKey = rgl.GrdLvlKey
  left join dbo.StuPgm spr on
   ser.StuKey = spr.StuKey AND
   spr.StuEduPgmMbrshpCatgStartDate   <= @AsOfDate AND (spr.StuEduPgmMbrshpCatgEndDate   is null OR spr.StuEduPgmMbrshpCatgEndDate >= @AsOfDate)  
  left join dbo.RefEduPgm rep on 
   spr.EduPgmCodeKey = rep.EduPgmCodeKey
  left join dbo.StuPrntOrGrdn spogr on 
   ser.StuKey = spogr.StuKey And
   spogr.EfctvStartDate <= @AsOfDate AND (spogr.EfctvEndDate is null OR spogr.EfctvEndDate >= @AsOfDate) 
  left join dbo.RefHighstEduLvl rhel on 
   spogr.PrntOrGrdnHighstEduLvlCodeKey = rhel.HighstEduLvlKey
  left join dbo.vwStuRaceRptng srr on
   ser.StuKey = srr.StuKey  and 
   srr.EfctvStartDate   <= @AsOfDate AND (srr.EfctvEndDate   is null OR srr.EfctvEndDate >= @AsOfDate)
  inner join #TempRace tr on 
   isnull(
   case
    when sdr.StuHspncEnctyIndctr = 'Y' then 'Hispanic'
    when sdr.StuEnctyMsngIndctr = 'Y' or sdr.StuRaceMsngIndctr = 'Y' then 'Missing'
    when srr.RaceCatg2Code is not null then 'Multiple'
    else srr.FedEnctyRaceCatgCode
   end, 'Missing') = tr.Race  
  inner join #TempGender tg on
   rg.GndrCode = tg.GenderCode 
  inner join #TempGrade tgr on 
   rgl.GrdLvLCode = tgr.GradeCode  

 Where
  -- Enrollments
  res.EnrlmtStatCode = '10'  AND
  isnull(rsec.StuExitCatgCode, 'N/A') != 'N470' AND -- no shows are not considered in active enrollment numbers
  -- Effective date Comparisions. As of date should be between Effective Start and End date (end date can be null)
  sdr.EfctvStartDate   <= @AsOfDate AND (sdr.EfctvEndDate   is null OR sdr.EfctvEndDate >= @AsOfDate)   AND
  sglr.EfctvStartDate  <= @AsOfDate AND (sglr.EfctvEndDate  is null OR sglr.EfctvEndDate >= @AsOfDate)  AND
  -- Filter deleted records out
  (spr.DeleteFlag is null OR spr.DeleteFlag = 'N') AND
  (spogr.DeleteFlag is null OR spogr.DeleteFlag = 'N')  AND
  (sglr.DeleteFlag is null OR sglr.DeleteFlag = 'N') AND
  (selar.DeleteFlag is null OR selar.DeleteFlag = 'N') AND
  (sdr.DeleetFlag is null OR sdr.DeleetFlag = 'N')    

一些想法

您不需要创建所有这些临时表，您的函数将返回一个内存表，该表不会写入磁盘，应该会更快。例如，

更改

INNER JOIN #TempRace tr ON 

为

INNER JOIN dbo.tfnParseStringIntoTable(@Race, ',') tr ON 

并删除对 #TempRace 的所有引用。您可以对 #TempGender 和 #TempGrade 执行相同的操作。

有时，通过在执行所有联接之前选择主表的感兴趣元素，可以提高速度。要明白我的意思，请尝试以下操作并查看它是否有效：

;WITH ser as
( SELECT DISTINCT
  *
 From 
  dbo.StuEnrlmt ser
 WHERE (ser.StuEsiRltnspExpctdSchlStartDate <= @AsOfDate)
  AND ((ser.WithdrlDate IS NULL) OR (ser.WithdrlDate >= @AsOfDate))
  AND ((spr.DeleteFlag IS NULL) OR (spr.DeleteFlag = 'N'))
  AND ((sdr.DeleetFlag IS NULL) OR (sdr.DeleetFlag = 'N'))
  AND ((ser.DeleteFlag IS NULL) OR (ser.DeleteFlag = 'N'))
)
--Main select replacing FROM dbo.StueEnrlmt with FROM ser

另外：您可能希望将主选择的位置中的其他元素加入到此 CT​​E 中，以便您的主选择具有 NO where子句（res、rsec、sdr、sglr、spr、spogr、selar），那么您将做很多缓慢的事情 - 即仅针对您实际要使用的记录的 case 语句和联接。

原始查询代码之前

需要注意的一件事是估计行数为 3，实际行数为 11601。

此外，它还表示 order by RaceCatg2Key - 您是否要加入此表？它可能会对其进行排序，以便可以进行连接。

如果您显示整个查询，我们可能有一些方法来加快速度。

Answer 3

从显示的执行计划的小片段中，我可以看到排序（尽管很慢）并不是问题的根源 - 估计行数为 3，但实际行数约为 11,000。

真正的问题是，SQL Server 严重错误地判断了它将要排序的记录数量，因此它以错误的顺序执行操作，并且排序的行数远远多于应有的行数。

有很多原因会导致 SQL Server 在不查看查询/执行计划的其余部分的情况下错误判断行数（例如过时的统计信息、参数嗅探、使用 SQL 函数等...）尽管我确信如果您发布完整的查询+执行计划，有人将能够发现它，但实际上不可能说出罪魁祸首！ :-)

更新：看看你的 SQL，我认为原因可能是这样的：

WHERE (ISNULL(sr.DeleteFlag, 'N') = 'N')

尝试用这个替换这个 - 我认为这是等效的，但我的大脑已经走了糊状！ :-)

WHERE sr.DeleteFlag IS NULL

原因是，尽管 SQL Server 保留了 DeleteFlag 列的统计信息，但它可能不够聪明，无法预测这个更复杂表达式的值。

说实话，这个可能性有点远，但目前我看不到任何其他潜在的罪魁祸首——这是我拥有的最好的！ :-)