Hi Masters,
I found sort warning in my query that can fill up my tempdb very quick till i got space problem in the server. I am trying to solve it by update the statistics but is not working.
It seems that the database query engine is miss calculation and give the query small number of memory to be used. Below is the execution plan that i got from this query :
SELECT 39309, OGR_FID, RAW_CLEAN.PROVINSI, RAW_CLEAN.KABUPATEN, RAW_CLEAN.KECAMATAN, NULL
FROM
(
SELECT RAW_LEVEL_0.WOID, RAW_LEVEL_0.ROID, RAW_LEVEL_0.AOID, RAW_LEVEL_0.POSID, RAW_LEVEL_0.AROID, RAW_LEVEL_0.AREAID, RAW_LEVEL_0.TERITORY, RAW_LEVEL_0.DISTRICTID, RAW_LEVEL_0.ROUTE, RAW_LEVEL_0.PROVINSI, RAW_LEVEL_0.KABUPATEN, RAW_LEVEL_0.KECAMATAN,
RAW_LEVEL_0.KELURAHAN, RAW_LEVEL_0.FACT_CODE, RAW_LEVEL_0.VALUE FROM (
-- file FACT_BASE_RIS__NOTASI_SELECT.sql
SELECT (case when PP.I ='WO' then AGG.OPERATION end) WOID,
(case when PP.I ='RO' then AGG.ROID end) ROID,
(case when PP.I ='AO/ASO' then AGG.AOID end) AOID,
(case when PP.I ='POS' then AGG.POSID end) POSID,
(case when PP.I ='ARO' then AGG.AROID end) AROID,
--(case when PP.I ='OTL' or PP.I ='Teritory' or PP.I ='District' or PP.I ='Route' then RC.AREAID end) AREAID,
(case when PP.I in ('OTL','Teritory','District','Route') then RC.AREAID end) AREAID,
--(case when PP.I ='OTL' or PP.I ='Teritory' or PP.I ='District' or PP.I ='Route' then RC.TERITORY end) TERITORY,
(case when PP.I in ('OTL','Teritory','District','Route') then RC.TERITORY end) TERITORY,
--(case when PP.I ='OTL' or PP.I ='District' or PP.I ='Route' then RC.DISTRICTID end) DISTRICTID,
(case when PP.I in ('OTL','District','Route') then RC.DISTRICTID end) DISTRICTID,
--(case when PP.I ='OTL' or PP.I ='Route' then RC.ROUTE end) ROUTE,
(case when PP.I in ('OTL','Route') then RC.ROUTE end) ROUTE,
(case when PP.I in ('Provinsi','Kabupaten','Kecamatan','Kelurahan') then AGI.PROVINSI end) PROVINSI,
(case when PP.I in ('Kabupaten','Kecamatan','Kelurahan') then AGI.KABUPATEN end) KABUPATEN,
(case when PP.I in ('Kecamatan','Kelurahan') then AGI.KECAMATAN end) KECAMATAN,
(case when PP.I = 'Kelurahan' then AGI.KELURAHAN end) KELURAHAN,
PPF.FACT_CODE,
(case
when PPF.FACT_CODE = 'SK' then (case when PP.U = 1 then (case when PPF.CND_4 = 'outlet' then SUM(sum_of__SK) when PPF.CND_4 = '%' then (SUM(sum_of__SK)/nullif(SUM(sum_of__TOTALGG),0))*100
end)
when PP.U = 2 then (case when PPF.CND_4 = 'outlet' then AVG(avg_of__SK) when PPF.CND_4 = '%' then (AVG(avg_of__SK)/nullif(AVG(avg_of__TOTALGG),0))*100 end) end)
when PPF.FACT_CODE = 'SL' then (case when PP.U = 1 then (case when PPF.CND_4 = 'outlet' then SUM(sum_of__SL) when PPF.CND_4 = '%' then (SUM(sum_of__SL)/nullif(SUM(sum_of__TOTALGG),0))*100
end)
when PP.U = 2 then (case when PPF.CND_4 = 'outlet' then AVG(avg_of__SL) when PPF.CND_4 = '%' then (AVG(avg_of__AVG_SL)/nullif(AVG(avg_of__TOTALGG),0))*100 end) end)
when PPF.FACT_CODE = 'SD' then (case when PP.U = 1 then (case when PPF.CND_4 = 'outlet' then SUM(sum_of__SD) when PPF.CND_4 = '%' then (SUM(sum_of__SD)/nullif(SUM(sum_of__TOTALGG),0))*100
end)
when PP.U = 2 then (case when PPF.CND_4 = 'outlet' then AVG(avg_of__SD) when PPF.CND_4 = '%' then (AVG(avg_of__AVG_SD)/nullif(AVG(avg_of__TOTALGG),0))*100 end) end)
when PPF.FACT_CODE = 'OOS' then (case when PP.U = 1 then (case when PPF.CND_4 = 'outlet' then SUM(sum_of__OOS) when PPF.CND_4 = '%' then (SUM(sum_of__OOS)/nullif(SUM(sum_of__TOTALGG),0))*100
end)
when PP.U = 2 then (case when PPF.CND_4 = 'outlet' then AVG(avg_of__OOS) when PPF.CND_4 = '%' then (AVG(avg_of__AVG_OOS)/nullif(AVG(avg_of__TOTALGG),0))*100 end) end)
when PPF.FACT_CODE = 'TL' then (case when PP.U = 1 then (case when PPF.CND_4 = 'outlet' then SUM(sum_of__TL) when PPF.CND_4 = '%' then (SUM(sum_of__TL)/nullif(SUM(sum_of__TOTALGG),0))*100
end)
when PP.U = 2 then (case when PPF.CND_4 = 'outlet' then AVG(avg_of__TL) when PPF.CND_4 = '%' then (AVG(avg_of__AVG_TL)/nullif(AVG(avg_of__TOTALGG),0))*100 end) end)
when PPF.FACT_CODE = 'BPJ' then (case when PP.U = 1 then (case when PPF.CND_4 = 'outlet' then SUM(sum_of__BPJ) when PPF.CND_4 = '%' then (SUM(sum_of__BPJ)/nullif(SUM(sum_of__TOTALGG),0))*100
end)
when PP.U = 2 then (case when PPF.CND_4 = 'outlet' then AVG(avg_of__BPJ) when PPF.CND_4 = '%' then (AVG(avg_of__AVG_BPJ)/nullif(AVG(avg_of__TOTALGG),0))*100 end) end)
when PPF.FACT_CODE = 'SB' then (case when PP.U = 1 then (case when PPF.CND_4 = 'outlet' then SUM(sum_of__SB) when PPF.CND_4 = '%' then (SUM(sum_of__SB)/nullif(SUM(sum_of__TOTALGG),0))*100
end)
when PP.U = 2 then (case when PPF.CND_4 = 'outlet' then AVG(avg_of__SB) when PPF.CND_4 = '%' then (AVG(avg_of__AVG_SB)/nullif(AVG(avg_of__TOTALGG),0))*100 end) end)
when PPF.FACT_CODE = 'BD' then (case when PP.U = 1 then (case when PPF.CND_4 = 'outlet' then SUM(sum_of__BD) when PPF.CND_4 = '%' then (SUM(sum_of__BD)/nullif(SUM(sum_of__TOTALGG),0))*100
end)
when PP.U = 2 then (case when PPF.CND_4 = 'outlet' then AVG(avg_of__BD) when PPF.CND_4 = '%' then (AVG(avg_of__AVG_BD)/nullif(AVG(avg_of__TOTALGG),0))*100 end) end)
when PPF.FACT_CODE = 'EC' then (case when PP.U = 1 then (case when PPF.CND_4 = 'call' then SUM(sum_of__EC) when PPF.CND_4 = '%' then ((SUM(sum_of__SK)/nullif(SUM(sum_of__TOTALGG),0))+(SUM(sum_of__OOS)/nullif(SUM(sum_of__TOTALGG),0))+(SUM(sum_of__SB)/nullif(SUM(sum_of__TOTALGG),0)))*100
end)
when PP.U = 2 then (case when PPF.CND_4 = 'call' then AVG(avg_of__EC) when PPF.CND_4 = '%' then ((AVG(avg_of__SK)/nullif(AVG(avg_of__TOTALGG),0))+(AVG(avg_of__AVG_OOS)/nullif(AVG(avg_of__TOTALGG),0))+(AVG(avg_of__AVG_SB)/nullif(AVG(avg_of__TOTALGG),0)))*100
end) end)
when PPF.FACT_CODE = 'STK' then (case when PP.U = 1 then (case when PPF.CND_4 = 'pack' then SUM(sum_of__STOCK) end)
when PP.U = 2 then (case when PPF.CND_4 = 'pack' then AVG(avg_of__STOCK) end) end)
when PPF.FACT_CODE = 'ASTK' then (case when PP.U = 1 then (case when PPF.CND_4 = 'pack/call' then SUM(sum_of__STOCK)/nullif(SUM(sum_of__BD),0)
end)
when PP.U = 2 then (case when PPF.CND_4 = 'pack/call' then AVG(avg_of__STOCK)/nullif(AVG(avg_of__AVG_BD),0) end) end)
when PPF.FACT_CODE = 'BY' then (case when PP.U = 1 then (case when PPF.CND_4 = 'pack' then SUM(sum_of__BUY) end)
when PP.U = 2 then (case when PPF.CND_4 = 'pack' then AVG(avg_of__BUY) end) end)
when PPF.FACT_CODE = 'ABY' then (case when PP.U = 1 then (case when PPF.CND_4 = 'pack/call' then SUM(sum_of__BUY)/nullif((SUM(sum_of__SK)+SUM(sum_of__OOS)+SUM(sum_of__SB)),0)
end)
when PP.U = 2 then (case when PPF.CND_4 = 'pack/call' then AVG(avg_of__BUY)/nullif((AVG(avg_of__SK)+AVG(avg_of__AVG_OOS)+AVG(avg_of__AVG_SB)),0) end) end)
end) VALUE,
PPF.CND_2,
PPF.CND_3,
PPF.CND_4
FROM Gg.RIS_CALC RC
CROSS JOIN
Gg.PARAM_PRIMARY__FACTS PPF
INNER JOIN Gg.PARAM_PRIMARY PP
ON PPF.IDX = PP.IDX
-- subtitution --
-- file FACT_BASE_RIS_JOIN__A3_B1.sql
-- parameter PP.B=1 => prov
INNER JOIN Gg.AREA_GG_5_ARO AGG
ON ((RC.AREAID = AGG.AOID AND AGG.AOID = AGG.POSID) OR
(RC.AREAID = AGG.POSID AND AGG.AOID <> AGG.POSID) OR
(RC.AREAID = AGG.AROID AND AGG.AOID <> AGG.AROID))
INNER JOIN Gg.AREA_GG_INDONESIA AGI
ON RC.AREAID = AGI.AREAID
INNER JOIN Gg.PARAM_PRIMARY__SCOPE_AREA_INDONESIA PPSAI
ON PPSAI.IDX = PP.IDX AND
PPSAI.PROVINSI = AGI.PROVINSI
-- end file
-- subtitution --
WHERE
PP.IDX = 39309 AND RC.PRODUCT_ID = PP.J AND
PPF.FACT_CODE IN ('SK' , 'SL' , 'SD' , 'OOS' , 'TL' , 'BPJ' , 'SB' , 'BD' , 'EC' , 'STK' , 'ASTK' , 'BY' , 'ABY' ) AND
PERIOD = 'L1W'
GROUP BY
(case when PP.I ='WO' then AGG.OPERATION end),
(case when PP.I ='RO' then AGG.ROID end),
(case when PP.I ='AO/ASO' then AGG.AOID end),
(case when PP.I ='POS' then AGG.POSID end),
(case when PP.I ='ARO' then AGG.AROID end),
--(case when PP.I ='OTL' or PP.I ='Teritory' or PP.I ='District' or PP.I ='Route' then RC.AREAID end),
(case when PP.I in ('OTL','Teritory','District','Route') then RC.AREAID end),
--(case when PP.I ='OTL' or PP.I ='Teritory' or PP.I ='District' or PP.I ='Route' then RC.TERITORY end),
(case when PP.I in ('OTL','Teritory','District','Route') then RC.TERITORY end),
--(case when PP.I ='OTL' or PP.I ='District' or PP.I ='Route' then RC.DISTRICTID end),
(case when PP.I in ('OTL','District','Route') then RC.DISTRICTID end),
--(case when PP.I ='OTL' or PP.I ='Route' then RC.ROUTE end),
(case when PP.I in ('OTL','Route') then RC.ROUTE end),
(case when PP.I in ('Provinsi','Kabupaten','Kecamatan','Kelurahan') then AGI.PROVINSI end),
(case when PP.I in ('Kabupaten','Kecamatan','Kelurahan') then AGI.KABUPATEN end),
(case when PP.I in ('Kecamatan','Kelurahan') then AGI.KECAMATAN end),
(case when PP.I = 'Kelurahan' then AGI.KELURAHAN end),
PP.U,PPF.FACT_CODE, PPF.CND_1, PPF.CND_2, PPF.CND_3, PPF.CND_4
HAVING
-- end file
SUM(sum_of__BUY) > 100) AS RAW_LEVEL_0
) AS RAW_CLEAN
INNER JOIN Gg.AREA_INDONESIA_KECAMATAN_SPATIAL AIKS
ON RAW_CLEAN.PROVINSI = AIKS.PROVINSI AND
RAW_CLEAN.KABUPATEN = AIKS.KABUPATEN AND
RAW_CLEAN.KECAMATAN = AIKS.KECAMATAN
-- subtitution --
-- subtitution --
WHERE 1 = 1
GROUP BY OGR_FID, RAW_CLEAN.PROVINSI, RAW_CLEAN.KABUPATEN, RAW_CLEAN.KECAMATAN
Please help me guys. Thanks.