I have this query and it runs less than 2 minutes on Test but on Prod it takes 10 minutes.
Both are the same version,CPU,RAM,OS the same.
I have four tempdb files on Prod but one one Test.
I listed the time it took when I run each query one at a time. When I created missing indexes(2), it made it worse on test but reduce the execution time by half on prod.
I also see two different exec plans on Prod and Test.
select distinct a11.APPLN_CODE APPLN_CODE
into #ZZMQ00
from BI_EDW.dbo.DIM_APPLICATION a11
where a11.APPLN_DATE_KEY >= 99990000
Prod rows 390233 , 0 sec
Test rows 390178, 0 sec
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
Test 49742 , 1 sec
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
Prod 4851 rows, 27 sec prod,
test 4859 rows , 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
Prod 4591 rows ,2 mintues:38 Seconds,
Test 4591 rows ,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
Prod 3752 rows, 2 minutes 17 Seconds
Test 3752 rows, 14 sec
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
Prod 2880 rows, 1 minutes and 38 seconds
Test 2880 rows, 12 Seconds
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
Prod 2029 rows , 1:01 minutes,
Test 2029 rows , 10 Seconds
Best Answer
Folks, I was able to resolve the issue by updating the statistics . I believe it is related to outdated stats and so the query optimizer getting not so efficient execution plan. Thanks