相同的查询不同的服务器，不同的执行计划和时间

Question

我有这个查询，它运行不到2分钟的测试，但在刺激，它需要10分钟。

两者是相同的版本，CPU，RAM，操作系统相同。

我在Prod上有四个tempdb文件，只有一个测试。

我列出了每次运行每个查询所需的时间。当我创建缺失索引(2)时，它使测试时更糟，但在prod上将执行时间减少了一半。

我还看到了两个不同的执行计划的Prod和Test。

select  distinct a11.APPLN_CODE  APPLN_CODE
into #ZZMQ00
from    BI_EDW.dbo.DIM_APPLICATION  a11
where   a11.APPLN_DATE_KEY >=  99990000 

刺激行390233，0秒

测试行390178，0秒

select  distinct a12.APPLN_CODE  APPLN_CODE
into #ZZMQ01
from    BI_EDW.dbo.DIM_APPLICATION  a12
    join    BI_EDW.dbo.DIM_APPLICATION_STATUS   a13
      on    (a12.APPLN_STATUS_KEY = a13.STATUS_KEY)
where   ((not ((a12.APPLN_CODE)
 in (select ps21.APPLN_CODE
    from    #ZZMQ00 ps21)))
 and a13.STATUS_CD in ('DEP', 'SWD', 'SDE', 'SPD', 'MS')) 

Prod 49758，1 Sec

测试49742，1秒

select  a16.SCHOOL_KEY  SCHOOL_KEY,
    a15.DATE_KEY  DATE_KEY,
    a13.DGR_ADM_CAT_KEY  DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY  ACAD_REGULAR_TERM_KEY,
    a13.APPLN_ACAD_LVL_KEY  OBJ_KEY,
    count(distinct a11.APPLT_KEY)  WJXBFS1
into #ZZMD02
from    BI_EDW.dbo.DIM_APPLICATION  a11
    join    #ZZMQ01 pa12
      on    (a11.APPLN_CODE = pa12.APPLN_CODE)
    join    BI_EDW.dbo.DIM_APPLICATION  a13
      on    (a11.APPLN_KEY = a13.APPLN_KEY)
    join    BI_EDW.dbo.DIM_ACAD_TERM    a14
      on    (a11.APPLN_ACAD_TERM_KEY = a14.ACAD_TERM_KEY)
    join    BI_EDW.dbo.TRANS_TERM_STATUS_EFF_DATE_TO_DATE   a15
      on    (CONVERT(VARCHAR,a11.ROW_EFF_START_DTTM,112) = a15.EFFSTARTDATEKEY and 
    a11.APPLN_ACAD_TERM_KEY = a15.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_DEPARTMENT   a16
      on    (a11.APPLN_DEPT_KEY = a16.DEPT_KEY)
    join    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    a17
      on    (a14.ACAD_REGULAR_TERM_KEY = a17.ACAD_REGULAR_TERM_KEY)
    join    BI_EDW.dbo.DIM_APPLICATION_STATUS   a18
      on    (a13.APPLN_STATUS_KEY = a18.STATUS_KEY)
where   (((a14.ACAD_REGULAR_TERM_KEY)
 in (select r11.ACAD_REGULAR_TERM_KEY
    from    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    r11
    where   r11.ACTIVE in ('1')))
 and a17.ACTIVE in ('1')
 and a18.STATUS_CD in ('DEP', 'SWD', 'SDE', 'SPD', 'MS')
 and a15.DATE_KEY <=  convert(varchar,a11.ROW_EFF_END_DTTM,112)
 and ((a15.DATE_KEY between LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 0921 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END and LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 1001 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END
 and a17.ACAD_SEASON_SNAME = 'F')
 or (a17.ACAD_SEASON_SNAME = 'X'
 and a15.DATE_KEY between LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0621 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END and LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0701 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END)
 or (a17.ACAD_SEASON_SNAME = 'S'
 and a15.DATE_KEY between CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE()-9,112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0428 END and CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE(),112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0507 END)))
group by    a16.SCHOOL_KEY,
    a15.DATE_KEY,
    a13.DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY,
    a13.APPLN_ACAD_LVL_KEY 

戳4851行，27秒，

测试4859行，38 Sec

select  a17.SCHOOL_KEY  SCHOOL_KEY,
    (a12.DATE_KEY + 10000)  DATE_KEY,
    a16.DGR_ADM_CAT_KEY  DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY  ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY  OBJ_KEY,
    count(distinct a11.APPLT_KEY)  WJXBFS1
into #ZZMD03
from    BI_EDW.dbo.DIM_APPLICATION  a11
    join    BI_EDW.dbo.TRANS_TERM_STATUS_EFF_DATE_TO_DATE   a12
      on    (CONVERT(VARCHAR,a11.ROW_EFF_START_DTTM,112) = a12.EFFSTARTDATEKEY and 
    a11.APPLN_ACAD_TERM_KEY = a12.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_TERM    a13
      on    (a11.APPLN_ACAD_TERM_KEY = a13.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    a14
      on    (a13.ACAD_REGULAR_TERM_KEY = (a14.ACAD_REGULAR_TERM_KEY - 100))
    join    #ZZMQ01 pa15
      on    (a11.APPLN_CODE = pa15.APPLN_CODE)
    join    BI_EDW.dbo.DIM_APPLICATION  a16
      on    (a11.APPLN_KEY = a16.APPLN_KEY)
    join    BI_EDW.dbo.DIM_DEPARTMENT   a17
      on    (a11.APPLN_DEPT_KEY = a17.DEPT_KEY)
    join    BI_EDW.dbo.DIM_APPLICATION_STATUS   a18
      on    (a16.APPLN_STATUS_KEY = a18.STATUS_KEY)
where   (((a14.ACAD_REGULAR_TERM_KEY)
 in (select r11.ACAD_REGULAR_TERM_KEY
    from    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    r11
    where   r11.ACTIVE in ('1')))
 and a14.ACTIVE in ('1')
 and a18.STATUS_CD in ('DEP', 'SWD', 'SDE', 'SPD', 'MS')
 and a12.DATE_KEY <=  (convert(varchar,a11.ROW_EFF_END_DTTM,112) - 10000)
 and ((a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 0921 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 10000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 1001 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 10000)
 and a14.ACAD_SEASON_SNAME = 'F')
 or (a14.ACAD_SEASON_SNAME = 'X'
 and a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0621 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 10000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0701 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 10000))
 or (a14.ACAD_SEASON_SNAME = 'S'
 and a12.DATE_KEY between (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE()-9,112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0428 END - 10000) and (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE(),112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0507 END - 10000))))
group by    a17.SCHOOL_KEY,
    (a12.DATE_KEY + 10000),
    a16.DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY 

刺4591排,2分钟:38秒，

测试4591行,16 Sec

select  a17.SCHOOL_KEY  SCHOOL_KEY,
    (a12.DATE_KEY + 20000)  DATE_KEY,
    a16.DGR_ADM_CAT_KEY  DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY  ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY  OBJ_KEY,
    count(distinct a11.APPLT_KEY)  WJXBFS1
into #ZZMD04
from    BI_EDW.dbo.DIM_APPLICATION  a11
    join    BI_EDW.dbo.TRANS_TERM_STATUS_EFF_DATE_TO_DATE   a12
      on    (CONVERT(VARCHAR,a11.ROW_EFF_START_DTTM,112) = a12.EFFSTARTDATEKEY and 
    a11.APPLN_ACAD_TERM_KEY = a12.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_TERM    a13
      on    (a11.APPLN_ACAD_TERM_KEY = a13.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    a14
      on    (a13.ACAD_REGULAR_TERM_KEY = (a14.ACAD_REGULAR_TERM_KEY - 200))
    join    #ZZMQ01 pa15
      on    (a11.APPLN_CODE = pa15.APPLN_CODE)
    join    BI_EDW.dbo.DIM_APPLICATION  a16
      on    (a11.APPLN_KEY = a16.APPLN_KEY)
    join    BI_EDW.dbo.DIM_DEPARTMENT   a17
      on    (a11.APPLN_DEPT_KEY = a17.DEPT_KEY)
    join    BI_EDW.dbo.DIM_APPLICATION_STATUS   a18
      on    (a16.APPLN_STATUS_KEY = a18.STATUS_KEY)
where   (((a14.ACAD_REGULAR_TERM_KEY)
 in (select r11.ACAD_REGULAR_TERM_KEY
    from    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    r11
    where   r11.ACTIVE in ('1')))
 and a14.ACTIVE in ('1')
 and a18.STATUS_CD in ('DEP', 'SWD', 'SDE', 'SPD', 'MS')
 and a12.DATE_KEY <=  (convert(varchar,a11.ROW_EFF_END_DTTM,112) - 20000)
 and ((a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 0921 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 20000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 1001 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 20000)
 and a14.ACAD_SEASON_SNAME = 'F')
 or (a14.ACAD_SEASON_SNAME = 'X'
 and a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0621 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 20000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0701 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 20000))
 or (a14.ACAD_SEASON_SNAME = 'S'
 and a12.DATE_KEY between (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE()-9,112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0428 END - 20000) and (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE(),112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0507 END - 20000))))
group by    a17.SCHOOL_KEY,
    (a12.DATE_KEY + 20000),
    a16.DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY

击打3752行，2分17秒

测试3752行，14秒

select  a17.SCHOOL_KEY  SCHOOL_KEY,
    (a12.DATE_KEY + 30000)  DATE_KEY,
    a16.DGR_ADM_CAT_KEY  DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY  ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY  OBJ_KEY,
    count(distinct a11.APPLT_KEY)  WJXBFS1
into #ZZMD05
from    BI_EDW.dbo.DIM_APPLICATION  a11
    join    BI_EDW.dbo.TRANS_TERM_STATUS_EFF_DATE_TO_DATE   a12
      on    (CONVERT(VARCHAR,a11.ROW_EFF_START_DTTM,112) = a12.EFFSTARTDATEKEY and 
    a11.APPLN_ACAD_TERM_KEY = a12.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_TERM    a13
      on    (a11.APPLN_ACAD_TERM_KEY = a13.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    a14
      on    (a13.ACAD_REGULAR_TERM_KEY = (a14.ACAD_REGULAR_TERM_KEY - 300))
    join    #ZZMQ01 pa15
      on    (a11.APPLN_CODE = pa15.APPLN_CODE)
    join    BI_EDW.dbo.DIM_APPLICATION  a16
      on    (a11.APPLN_KEY = a16.APPLN_KEY)
    join    BI_EDW.dbo.DIM_DEPARTMENT   a17
      on    (a11.APPLN_DEPT_KEY = a17.DEPT_KEY)
    join    BI_EDW.dbo.DIM_APPLICATION_STATUS   a18
      on    (a16.APPLN_STATUS_KEY = a18.STATUS_KEY)
where   (((a14.ACAD_REGULAR_TERM_KEY)
 in (select r11.ACAD_REGULAR_TERM_KEY
    from    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    r11
    where   r11.ACTIVE in ('1')))
 and a14.ACTIVE in ('1')
 and a18.STATUS_CD in ('DEP', 'SWD', 'SDE', 'SPD', 'MS')
 and a12.DATE_KEY <=  (convert(varchar,a11.ROW_EFF_END_DTTM,112) - 30000)
 and ((a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 0921 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 30000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 1001 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 30000)
 and a14.ACAD_SEASON_SNAME = 'F')
 or (a14.ACAD_SEASON_SNAME = 'X'
 and a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0621 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 30000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0701 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 30000))
 or (a14.ACAD_SEASON_SNAME = 'S'
 and a12.DATE_KEY between (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE()-9,112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0428 END - 30000) and (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE(),112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0507 END - 30000))))
group by    a17.SCHOOL_KEY,
    (a12.DATE_KEY + 30000),
    a16.DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY 

戳2880行，1分38秒

测试2880行，12秒

select  a17.SCHOOL_KEY  SCHOOL_KEY,
    (a12.DATE_KEY + 40000)  DATE_KEY,
    a16.DGR_ADM_CAT_KEY  DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY  ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY  OBJ_KEY,
    count(distinct a11.APPLT_KEY)  WJXBFS1
into #ZZMD06
from    BI_EDW.dbo.DIM_APPLICATION  a11
    join    BI_EDW.dbo.TRANS_TERM_STATUS_EFF_DATE_TO_DATE   a12
      on    (CONVERT(VARCHAR,a11.ROW_EFF_START_DTTM,112) = a12.EFFSTARTDATEKEY and 
    a11.APPLN_ACAD_TERM_KEY = a12.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_TERM    a13
      on    (a11.APPLN_ACAD_TERM_KEY = a13.ACAD_TERM_KEY)
    join    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    a14
      on    (a13.ACAD_REGULAR_TERM_KEY = (a14.ACAD_REGULAR_TERM_KEY - 400))
    join    #ZZMQ01 pa15
      on    (a11.APPLN_CODE = pa15.APPLN_CODE)
    join    BI_EDW.dbo.DIM_APPLICATION  a16
      on    (a11.APPLN_KEY = a16.APPLN_KEY)
    join    BI_EDW.dbo.DIM_DEPARTMENT   a17
      on    (a11.APPLN_DEPT_KEY = a17.DEPT_KEY)
    join    BI_EDW.dbo.DIM_APPLICATION_STATUS   a18
      on    (a16.APPLN_STATUS_KEY = a18.STATUS_KEY)
where   (((a14.ACAD_REGULAR_TERM_KEY)
 in (select r11.ACAD_REGULAR_TERM_KEY
    from    BI_EDW.dbo.DIM_ACAD_REGULAR_TERM    r11
    where   r11.ACTIVE in ('1')))
 and a14.ACTIVE in ('1')
 and a18.STATUS_CD in ('DEP', 'SWD', 'SDE', 'SPD', 'MS')
 and a12.DATE_KEY <=  (convert(varchar,a11.ROW_EFF_END_DTTM,112) - 40000)
 and ((a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 0921 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 40000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0201) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 1001)) THEN 1001 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 40000)
 and a14.ACAD_SEASON_SNAME = 'F')
 or (a14.ACAD_SEASON_SNAME = 'X'
 and a12.DATE_KEY between (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0621 ELSE CONVERT(VARCHAR,GETDATE()-9,112)%10000 END - 40000) and (LEFT(a14.ACAD_REGULAR_TERM_KEY,4)*10000+CASE WHEN ((CONVERT(VARCHAR,GETDATE(),112)%10000 < 0301) OR (CONVERT(VARCHAR,GETDATE(),112)%10000 >= 0701)) THEN 0701 ELSE CONVERT(VARCHAR,GETDATE(),112)%10000 END - 40000))
 or (a14.ACAD_SEASON_SNAME = 'S'
 and a12.DATE_KEY between (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE()-9,112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0428 END - 40000) and (CASE WHEN CONVERT(VARCHAR,GETDATE(),112) BETWEEN (a14.ACAD_REGULAR_TERM_KEY/100-1)*10000+0701 AND (a14.ACAD_REGULAR_TERM_KEY/100)*10000+0507 THEN CONVERT(VARCHAR,GETDATE(),112) ELSE (a14.ACAD_REGULAR_TERM_KEY/100)*10000 + 0507 END - 40000))))
group by    a17.SCHOOL_KEY,
    (a12.DATE_KEY + 40000),
    a16.DGR_ADM_CAT_KEY,
    a14.ACAD_REGULAR_TERM_KEY,
    a16.APPLN_ACAD_LVL_KEY 

第2029排，1:01分钟，

测试2029行，10秒

Answer 1

各位，我能够通过更新统计数据来解决这个问题。我认为这与过时的统计数据有关，因此查询优化器的执行计划不那么有效。谢谢