Hello,
I have the following query. can you help me please to optimize it?
DROP TABLE IF EXISTS #tmp_EK_KondData;
declare @dtStart datetime
set @dtStart= '01.11.2014'
select KO.KO_ID,
KO.DV_ID,
KO.AK_ID,
KO.KVG_ID,
KO.FO_ID,
KO.VER_ID,
KO.KOA_ID,
KOA.KOA_NAME,
KO.KOE_ID,
KOE.KOE_NAME,
KO.KO_WERT,
CASE
WHEN KO1.KO_WERT IS NULL
THEN ISNULL(KO2.KO_WERT,0)
ELSE KO1.KO_WERT
END AS BEREINIGTE_EK,
CASE
WHEN KO3.KO_WERT IS NULL
THEN ISNULL(KO4.KO_WERT,0)
ELSE KO3.KO_WERT
END AS VK_MANDANT,
KO.KO_GUELTIG_AB,
KO.KO_GUELTIG_BIS,
KO.KO_ANGELEGT_AM,
KO.KO_MODIFIZIERT_AM,
KO.KO_ANGELEGT_VON,
KO.KO_MODIFIZIERT_VON,
KO.STA_ID,
case
WHEN KO.KOA_ID = 1
THEN 'NORMALE EK'
WHEN KO.KOA_ID = 13 AND KO.FO_ID IS NULL
THEN 'SOKO EK ASSETKLASSE'
WHEN KO.KOA_ID = 13 AND KO.FO_ID IS NOT NULL
THEN 'SOKO EK FONDS'
ELSE 'UPS'
END AS KOND_INFO
INTO #tmp_EK_KondData
FROM STAMM.dbo.KONDITION KO
LEFT OUTER JOIN STAMM.dbo.KONDITION KO1
ON KO.AK_ID = KO1.AK_ID
AND KO.DV_ID = KO1.DV_ID
AND ISNULL(KO.KVG_ID,0) = ISNULL(KO1.KVG_ID,0)
AND KO.PAG_ID = KO1.PAG_ID
AND KO1.KOA_ID = 9
AND @dtStart BETWEEN KO1.KO_GUELTIG_AB
AND KO1.KO_GUELTIG_BIS
AND KO1.STA_ID BETWEEN 100 AND 199
LEFT OUTER JOIN STAMM.dbo.KONDITION KO2
ON KO.AK_ID = KO2.AK_ID
AND ISNULL(KO.KVG_ID,0) = ISNULL(KO2.KVG_ID,0)
AND KO.PAG_ID = KO2.PAG_ID
AND KO2.KOA_ID = 9
AND KO2.DV_ID IS NULL
AND @dtStart BETWEEN KO2.KO_GUELTIG_AB
AND KO2.KO_GUELTIG_BIS
AND KO2.STA_ID BETWEEN 100 AND 199
LEFT OUTER JOIN STAMM.dbo.KONDITION KO3
ON KO.AK_ID = KO3.AK_ID
AND KO.DV_ID = KO3.DV_ID
AND ISNULL(KO.KVG_ID,0) = ISNULL(KO3.KVG_ID,0)
AND KO.PAG_ID = KO3.PAG_ID
AND KO3.KOA_ID = 2
AND @dtStart BETWEEN KO3.KO_GUELTIG_AB
AND KO3.KO_GUELTIG_BIS
AND KO3.STA_ID BETWEEN 100 AND 199
LEFT OUTER JOIN STAMM.dbo.KONDITION KO4
ON KO.AK_ID = KO4.AK_ID
AND ISNULL(KO.KVG_ID,0) = ISNULL(KO4.KVG_ID,0)
AND KO.PAG_ID = KO4.PAG_ID
AND KO4.KOA_ID = 2
AND KO4.DV_ID IS NULL
AND @dtStart BETWEEN KO4.KO_GUELTIG_AB
AND KO4.KO_GUELTIG_BIS
AND KO4.STA_ID BETWEEN 100 AND 199
INNER JOIN STAMM.dbo.KONDITION_ART KOA
ON KO.KOA_ID = KOA.KOA_ID
INNER JOIN STAMM.dbo.KONDITION_EINHEIT KOE
ON KO.KOE_ID = KOE.KOE_ID
WHERE KO.KOA_ID IN (1,13)
AND KO.PAG_ID = 2
and ( KO.KO_ANGELEGT_AM >= @dtStart OR
KO.KO_MODIFIZIERT_AM >= @dtStart
)
and @dtStart between KO.KO_GUELTIG_AB and KO.KO_GUELTIG_BIS
and KO.STA_ID between 100 and 199
select * from #tmp_EK_KondData
-- UNTERTEILUNG IN SOKO UND NORMALE EK, WEGEN LEFT OUTER JOINS
SELECT
tmp1.DV_ID,
tmp1.VER_ID,
tmp1.AK_ID,
'' AS AK_CODE,
KVG.KVG_ID,
KVG.KVG_CODE,
KVG.KVG_NAME,
FO.FO_ID,
FO.FO_WKN,
FO.FO_BEZEICHNUNG,
tmp1.KOA_ID,
tmp1.KOA_NAME,
tmp1.KOE_ID,
tmp1.KOE_NAME,
tmp1.KO_WERT,
tmp1.BEREINIGTE_EK,
tmp1.VK_MANDANT,
tmp1.KO_GUELTIG_AB,
tmp1.KO_GUELTIG_BIS,
tmp1.KO_ANGELEGT_AM,
tmp1.KO_MODIFIZIERT_AM,
tmp1.KO_ANGELEGT_VON,
tmp1.KO_MODIFIZIERT_VON,
tmp1.KOND_INFO
FROM #tmp_EK_KondData tmp1
INNER JOIN STAMM.dbo.FONDS FO
ON tmp1.FO_ID = FO.FO_ID
INNER JOIN STAMM.dbo.KVG KVG
ON FO.KVG_ID = KVG.KVG_ID
WHERE tmp1.KOA_ID = 13
UNION ALL
SELECT
tmp1.DV_ID,
tmp1.VER_ID,
tmp1.AK_ID,
AKL.AK_CODE,
KVG.KVG_ID,
KVG.KVG_CODE,
KVG.KVG_NAME,
NULL AS FOND_NR,
'' AS FOND_WKN,
'' AS FOND_BEZEICHNUNG,
tmp1.KOA_ID,
tmp1.KOA_NAME,
tmp1.KOE_ID,
tmp1.KOE_NAME,
tmp1.KO_WERT,
tmp1.BEREINIGTE_EK,
tmp1.VK_MANDANT,
tmp1.KO_GUELTIG_AB,
tmp1.KO_GUELTIG_BIS,
tmp1.KO_ANGELEGT_AM,
tmp1.KO_MODIFIZIERT_AM,
tmp1.KO_ANGELEGT_VON,
tmp1.KO_MODIFIZIERT_VON,
tmp1.KOND_INFO
FROM #tmp_EK_KondData tmp1
INNER JOIN STAMM.dbo.ASSETKLASSE AKL
ON tmp1.AK_ID = AKL.AK_ID
INNER JOIN STAMM.dbo.ASSETKLASSE_FONDS_ZUORD AFZ
ON AKL.AK_ID = AFZ.AK_ID
AND @dtStart BETWEEN AFZ.AFZ_GUELTIG_AB
AND AFZ.AFZ_GUELTIG_BIS
AND AFZ.AFZ_AKTIV = 1
INNER JOIN STAMM.dbo.FONDS FO
ON AFZ.FO_ID = FO.FO_ID
INNER JOIN STAMM.dbo.KVG KVG
ON FO.KVG_ID = KVG.KVG_ID
WHERE tmp1.KOA_ID = 13
GROUP BY
tmp1.DV_ID,
tmp1.VER_ID,
tmp1.AK_ID,
AKL.AK_CODE,
KVG.KVG_ID,
KVG.KVG_CODE,
KVG.KVG_NAME,
tmp1.KOA_ID,
tmp1.KOA_NAME,
tmp1.KOE_ID,
tmp1.KOE_NAME,
tmp1.KO_WERT,
tmp1.BEREINIGTE_EK,
tmp1.VK_MANDANT,
tmp1.KO_GUELTIG_AB,
tmp1.KO_GUELTIG_BIS,
tmp1.KO_ANGELEGT_AM,
tmp1.KO_MODIFIZIERT_AM,
tmp1.KO_ANGELEGT_VON,
tmp1.KO_MODIFIZIERT_VON,
tmp1.KOND_INFO
UNION ALL
SELECT
tmp1.DV_ID,
tmp1.VER_ID,
tmp1.AK_ID,
AKL.AK_CODE,
KVG.KVG_ID,
KVG.KVG_CODE,
KVG.KVG_NAME,
NULL AS FOND_NR,
'' AS FOND_WKN,
'' AS FOND_BEZEICHNUNG,
tmp1.KOA_ID,
tmp1.KOA_NAME,
tmp1.KOE_ID,
tmp1.KOE_NAME,
tmp1.KO_WERT,
tmp1.BEREINIGTE_EK,
tmp1.VK_MANDANT,
tmp1.KO_GUELTIG_AB,
tmp1.KO_GUELTIG_BIS,
tmp1.KO_ANGELEGT_AM,
tmp1.KO_MODIFIZIERT_AM,
tmp1.KO_ANGELEGT_VON,
tmp1.KO_MODIFIZIERT_VON,
tmp1.KOND_INFO
FROM #tmp_EK_KondData tmp1
INNER JOIN STAMM.dbo.ASSETKLASSE AKL
ON tmp1.AK_ID = AKL.AK_ID
INNER JOIN STAMM.dbo.ASSETKLASSE_FONDS_ZUORD AFZ
ON AKL.AK_ID = AFZ.AK_ID
AND @dtStart BETWEEN AFZ.AFZ_GUELTIG_AB
AND AFZ.AFZ_GUELTIG_BIS
AND AFZ.AFZ_AKTIV = 1
INNER JOIN STAMM.dbo.FONDS FO
ON AFZ.FO_ID = FO.FO_ID
INNER JOIN STAMM.dbo.KVG KVG
ON FO.KVG_ID = KVG.KVG_ID
WHERE tmp1.KOA_ID = 1
GROUP BY
tmp1.DV_ID,
tmp1.VER_ID,
tmp1.AK_ID,
AKL.AK_CODE,
KVG.KVG_ID,
KVG.KVG_CODE,
KVG.KVG_NAME,
tmp1.KOA_ID,
tmp1.KOA_NAME,
tmp1.KOE_ID,
tmp1.KOE_NAME,
tmp1.KO_WERT,
tmp1.BEREINIGTE_EK,
tmp1.VK_MANDANT,
tmp1.KO_GUELTIG_AB,
tmp1.KO_GUELTIG_BIS,
tmp1.KO_ANGELEGT_AM,
tmp1.KO_MODIFIZIERT_AM,
tmp1.KO_ANGELEGT_VON,
tmp1.KO_MODIFIZIERT_VON,
tmp1.KOND_INFO