Hello
i have the following query, can you help me please to optimize it?
thank you
select K1.KOND_ID,
K1.DV_ID,
K1.AK_ID,
K1.KAG_ID,
K1.FOND_NR,
K1.VERM_ID,
K1.KONDART_ID,
K2.KONDART_NAME,
K1.KONDEINHEIT_ID,
K3.KONDEINHEIT_NAME,
K1.KOND_WERT,
CASE
WHEN K4.KOND_WERT IS NULL
THEN ISNULL(K5.KOND_WERT,0)
ELSE K4.KOND_WERT
END AS BEREINIGTE_EK,
CASE
WHEN K6.KOND_WERT IS NULL
THEN ISNULL(K7.KOND_WERT,0)
ELSE K6.KOND_WERT
END AS VK_MANDANT,
K1.KOND_VALID_FROM,
K1.KOND_VALID_TO,
K1.KOND_TIME_CREATED,
K1.KOND_LAST_MODIFIED,
K1.KOND_USER_CREATED,
K1.KOND_LAST_USER,
K1.KONDSTA_ID,
case
WHEN K1.KONDART_ID = 1
THEN 'NORMALE EK'
WHEN K1.KONDART_ID = 13 AND K1.FOND_NR IS NULL
THEN 'SOKO EK ASSETKLASSE'
WHEN K1.KONDART_ID = 13 AND K1.FOND_NR IS NOT NULL
THEN 'SOKO EK FONDS'
ELSE 'UPS'
END AS KOND_INFO
INTO #tmp_EK_KondData
FROM VBH_IO.DBO.VBH_KONDITION K1
LEFT OUTER JOIN VBH_IO.DBO.VBH_KONDITION K4
ON K1.AK_ID = K4.AK_ID
AND K1.DV_ID = K4.DV_ID
AND ISNULL(K1.KAG_ID,0) = ISNULL(K4.KAG_ID,0)
AND K1.PROVARTGRP_ID = K4.PROVARTGRP_ID
AND K4.KONDART_ID = 9
AND @dtStart BETWEEN K4.KOND_VALID_FROM
AND K4.KOND_VALID_TO
AND K4.KONDSTA_ID BETWEEN 100 AND 199
LEFT OUTER JOIN VBH_IO.DBO.VBH_KONDITION K5
ON K1.AK_ID = K5.AK_ID
AND ISNULL(K1.KAG_ID,0) = ISNULL(K5.KAG_ID,0)
AND K1.PROVARTGRP_ID = K5.PROVARTGRP_ID
AND K5.KONDART_ID = 9
AND K5.DV_ID IS NULL
AND @dtStart BETWEEN K5.KOND_VALID_FROM
AND K5.KOND_VALID_TO
AND K5.KONDSTA_ID BETWEEN 100 AND 199
LEFT OUTER JOIN VBH_IO.DBO.VBH_KONDITION K6
ON K1.AK_ID = K6.AK_ID
AND K1.DV_ID = K6.DV_ID
AND ISNULL(K1.KAG_ID,0) = ISNULL(K6.KAG_ID,0)
AND K1.PROVARTGRP_ID = K6.PROVARTGRP_ID
AND K6.KONDART_ID = 2
AND @D_START BETWEEN K6.KOND_VALID_FROM
AND K6.KOND_VALID_TO
AND K6.KONDSTA_ID BETWEEN 100 AND 199
LEFT OUTER JOIN VBH_IO.DBO.VBH_KONDITION K7
ON K1.AK_ID = K7.AK_ID
AND ISNULL(K1.KAG_ID,0) = ISNULL(K7.KAG_ID,0)
AND K1.PROVARTGRP_ID = K7.PROVARTGRP_ID
AND K7.KONDART_ID = 2
AND K7.DV_ID IS NULL
AND @D_START BETWEEN K7.KOND_VALID_FROM
AND K7.KOND_VALID_TO
AND K7.KONDSTA_ID BETWEEN 100 AND 199
INNER JOIN VBH_IO.DBO.VBH_KONDITIONSART K2
ON K1.KONDART_ID = K2.KONDART_ID
INNER JOIN VBH_IO.DBO.VBH_KONDITIONSWERT_EINHEIT K3
ON K1.KONDEINHEIT_ID = K3.KONDEINHEIT_ID
WHERE K1.KONDART_ID IN (1,13)
AND K1.PROVARTGRP_ID = 2
and ( K1.KOND_TIME_CREATED >= @D_START OR
K1.KOND_LAST_MODIFIED >= @D_START
)
and @D_START between K1.KOND_VALID_FROM and K1.KOND_VALID_TO
and K1.KONDSTA_ID between 100 and 199
;