I want to enforce the join types to be somewhat the same and will
therefore use local joins hints also for these
Adding join hints should be a last resort. There should be ways to rewrite the query / add indexes to get a more consistent result.
These plans are also estimated execution plans, in this case only you knows how well / bad the actual query will perform.
If the issue is parameter sniffing, OPTION(RECOMPILE)
would be the easiest solution.
Is the LEFT JOIN
only used for filtering? A NOT EXISTS
might work better to filter earlier.
Having said all that, with the limited information given, here are some possible, quick rewrites.
Rewrite #1 LEFT JOIN
to NOT EXISTS
The OPTION(RECOMPILE)
is added to get better estimates based on the parameters provided.
INSERT INTO dbo.cte_MDTForsikringssum
SELECT DISTINCT
mdtp.AvtaleNummer
,mdtp.MedlemskapNummer
,mdtp.Dekningstype
,mdtp.StartAlder
,mdtp.OpphorsAlder
,mdtp.PeriodeStartDato AS GjelderFraDato
,NULL GjelderTilDato
,mdtp.AjourholdDato AS EndretDato
,CONVERT(DECIMAL(18,8), 0) AS Forsikringssum
,0 AS Avkortningsfaktor
,0 AS PensjonsgivendeGrunnlag
,0 AS Folketrygd
,mdtp.Kjorenr_k
FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full mdtp
INNER JOIN
(SELECT
AvtaleNummer,
MedlemskapNummer,
Dekningstype,
StartAlder,
OpphorsAlder,
YEAR(PeriodeStartDato) AS PeriodeStartDatoAar,
MONTH(PeriodeStartDato) AS PeriodeStartDatoManed,
MAX(AjourholdDato) AS maxAjourholdDato
FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
WHERE --PeriodeStartDato < @dato--GETDATE()
( -- 27.03.2019 Endret WHERE betingelser lik neste step for å minske datamengden i TEMP tabell
(PeriodeStartDato BETWEEN @StartDato AND @SluttDato)
OR (Kjorenr_k = @kjorenr AND PeriodeStartDato < @dato)
)
AND KVID_Kontotype IN (189 --ArligInnskudd
,412,413 --AdmRes
,190,407,408,409,410,411,591) --Risiko
GROUP BY
AvtaleNummer,
MedlemskapNummer,
Dekningstype,
StartAlder,
OpphorsAlder,
YEAR(PeriodeStartDato),
MONTH(PeriodeStartDato)
) ajourholdD
ON ajourholdD.AvtaleNummer = mdtp.AvtaleNummer
AND ajourholdD.MedlemskapNummer = mdtp.MedlemskapNummer
AND ajourholdD.Dekningstype = mdtp.Dekningstype
AND ajourholdD.StartAlder = mdtp.StartAlder
AND ajourholdD.OpphorsAlder = mdtp.OpphorsAlder
AND ajourholdD.PeriodeStartDatoAar = YEAR(mdtp.PeriodeStartDato)
AND ajourholdD.PeriodeStartDatoManed = MONTH(mdtp.PeriodeStartDato)
AND ajourholdD.maxAjourholdDato = mdtp.AjourholdDato
WHERE mdtp.PeriodeStartDato <= @dato
AND NOT EXISTS
(
SELECT * FROM
dbo.cte_MDTForsikringssum dest
WHERE dest.AvtaleNummer = mdtp.AvtaleNummer
AND dest.MedlemskapNummer = mdtp.MedlemskapNummer
AND dest.Dekningstype = mdtp.Dekningstype
AND dest.StartAlder = mdtp.StartAlder
AND dest.OpphorsAlder = mdtp.OpphorsAlder
AND dest.GjelderFraDato = mdtp.PeriodeStartDato
AND dest.EndretDato = mdtp.AjourholdDato
)
OPTION(RECOMPILE);
Rewrite #2 Also removing the OR
by using UNION
INSERT INTO dbo.cte_MDTForsikringssum
SELECT DISTINCT
mdtp.AvtaleNummer
,mdtp.MedlemskapNummer
,mdtp.Dekningstype
,mdtp.StartAlder
,mdtp.OpphorsAlder
,mdtp.PeriodeStartDato AS GjelderFraDato
,NULL GjelderTilDato
,mdtp.AjourholdDato AS EndretDato
,CONVERT(DECIMAL(18,8), 0) AS Forsikringssum
,0 AS Avkortningsfaktor
,0 AS PensjonsgivendeGrunnlag
,0 AS Folketrygd
,mdtp.Kjorenr_k
FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full mdtp
INNER JOIN
(
SELECT
AvtaleNummer,
MedlemskapNummer,
Dekningstype,
StartAlder,
OpphorsAlder,
YEAR(PeriodeStartDato) AS PeriodeStartDatoAar,
MONTH(PeriodeStartDato) AS PeriodeStartDatoManed,
MAX(AjourholdDato) AS maxAjourholdDato
FROM
(
SELECT
AvtaleNummer,
MedlemskapNummer,
Dekningstype,
StartAlder,
OpphorsAlder,
PeriodeStartDato,
AjourholdDato
FROM
[BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
WHERE --PeriodeStartDato < @dato--GETDATE()
( -- 27.03.2019 Endret WHERE betingelser lik neste step for å minske datamengden i TEMP tabell
(PeriodeStartDato BETWEEN @StartDato AND @SluttDato)
)
AND KVID_Kontotype IN (189 --ArligInnskudd
,412,413 --AdmRes
,190,407,408,409,410,411,591) --Risiko
UNION
SELECT
AvtaleNummer,
MedlemskapNummer,
Dekningstype,
StartAlder,
OpphorsAlder,
PeriodeStartDato,
AjourholdDato
FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
WHERE
(Kjorenr_k = @kjorenr AND PeriodeStartDato < @dato)
AND KVID_Kontotype IN (189 --ArligInnskudd
,412,413 --AdmRes
,190,407,408,409,410,411,591) --Risiko
) AS A
GROUP BY
AvtaleNummer,
MedlemskapNummer,
Dekningstype,
StartAlder,
OpphorsAlder,
YEAR(PeriodeStartDato),
MONTH(PeriodeStartDato)
)
ajourholdD
ON ajourholdD.AvtaleNummer = mdtp.AvtaleNummer
AND ajourholdD.MedlemskapNummer = mdtp.MedlemskapNummer
AND ajourholdD.Dekningstype = mdtp.Dekningstype
AND ajourholdD.StartAlder = mdtp.StartAlder
AND ajourholdD.OpphorsAlder = mdtp.OpphorsAlder
AND ajourholdD.PeriodeStartDatoAar = YEAR(mdtp.PeriodeStartDato)
AND ajourholdD.PeriodeStartDatoManed = MONTH(mdtp.PeriodeStartDato)
AND ajourholdD.maxAjourholdDato = mdtp.AjourholdDato
WHERE mdtp.PeriodeStartDato <= @dato
AND NOT EXISTS
(
SELECT * FROM
dbo.cte_MDTForsikringssum dest
WHERE dest.AvtaleNummer = mdtp.AvtaleNummer
AND dest.MedlemskapNummer = mdtp.MedlemskapNummer
AND dest.Dekningstype = mdtp.Dekningstype
AND dest.StartAlder = mdtp.StartAlder
AND dest.OpphorsAlder = mdtp.OpphorsAlder
AND dest.GjelderFraDato = mdtp.PeriodeStartDato
AND dest.EndretDato = mdtp.AjourholdDato
)
OPTION(RECOMPILE);
Rewrite #3 Adding an additional temp table to store the inner join
By adding a temp table to split up the queries, the optimizer might get better estimates on the final query.
SELECT
AvtaleNummer,
MedlemskapNummer,
Dekningstype,
StartAlder,
OpphorsAlder,
YEAR(PeriodeStartDato) AS PeriodeStartDatoAar,
MONTH(PeriodeStartDato) AS PeriodeStartDatoManed,
MAX(AjourholdDato) AS maxAjourholdDato
INTO #TEMP
FROM
(
SELECT
AvtaleNummer,
MedlemskapNummer,
Dekningstype,
StartAlder,
OpphorsAlder,
PeriodeStartDato,
AjourholdDato
FROM
[BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
WHERE --PeriodeStartDato < @dato--GETDATE()
( -- 27.03.2019 Endret WHERE betingelser lik neste step for å minske datamengden i TEMP tabell
(PeriodeStartDato BETWEEN @StartDato AND @SluttDato)
)
AND KVID_Kontotype IN (189 --ArligInnskudd
,412,413 --AdmRes
,190,407,408,409,410,411,591) --Risiko
UNION
SELECT
AvtaleNummer,
MedlemskapNummer,
Dekningstype,
StartAlder,
OpphorsAlder,
PeriodeStartDato,
AjourholdDato
FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full
WHERE
(Kjorenr_k = @kjorenr AND PeriodeStartDato < @dato)
AND KVID_Kontotype IN (189 --ArligInnskudd
,412,413 --AdmRes
,190,407,408,409,410,411,591) --Risiko
) AS A
INSERT INTO dbo.cte_MDTForsikringssum
SELECT DISTINCT
mdtp.AvtaleNummer
,mdtp.MedlemskapNummer
,mdtp.Dekningstype
,mdtp.StartAlder
,mdtp.OpphorsAlder
,mdtp.PeriodeStartDato AS GjelderFraDato
,NULL GjelderTilDato
,mdtp.AjourholdDato AS EndretDato
,CONVERT(DECIMAL(18,8), 0) AS Forsikringssum
,0 AS Avkortningsfaktor
,0 AS PensjonsgivendeGrunnlag
,0 AS Folketrygd
,mdtp.Kjorenr_k
FROM [BMPERSIST].vips.impMedlemskapDekningTrinnPremie_full mdtp
INNER JOIN
(
SELECT *
FROM #TEMP
) ajourholdD
ON ajourholdD.AvtaleNummer = mdtp.AvtaleNummer
AND ajourholdD.MedlemskapNummer = mdtp.MedlemskapNummer
AND ajourholdD.Dekningstype = mdtp.Dekningstype
AND ajourholdD.StartAlder = mdtp.StartAlder
AND ajourholdD.OpphorsAlder = mdtp.OpphorsAlder
AND ajourholdD.PeriodeStartDatoAar = YEAR(mdtp.PeriodeStartDato)
AND ajourholdD.PeriodeStartDatoManed = MONTH(mdtp.PeriodeStartDato)
AND ajourholdD.maxAjourholdDato = mdtp.AjourholdDato
WHERE mdtp.PeriodeStartDato <= @dato
AND NOT EXISTS
(
SELECT * FROM
dbo.cte_MDTForsikringssum dest
WHERE dest.AvtaleNummer = mdtp.AvtaleNummer
AND dest.MedlemskapNummer = mdtp.MedlemskapNummer
AND dest.Dekningstype = mdtp.Dekningstype
AND dest.StartAlder = mdtp.StartAlder
AND dest.OpphorsAlder = mdtp.OpphorsAlder
AND dest.GjelderFraDato = mdtp.PeriodeStartDato
AND dest.EndretDato = mdtp.AjourholdDato
)
OPTION(RECOMPILE);
DROP TABLE #TEMP;
Closing note
If there is more information, like the table definitions and some sample data, more can be done, these three rewrites simply look like the fastest and easiest wins at the time of writing.
cte_MDTForsikringssum
table being 100% on the 'good plan' and 42,..% on the 'bad plan'SamplingPercent="42.3373
vs.SamplingPercent="100"
Next to that, the compiled parameters are different: E.G for@kjorenr = 89025
for the 'good plan' and@kjorenr = 89110
for the 'bad plan' The date timeframe is also ~3 days for the bad plan's compiled parameters while the good plan only has a timeframe of ~1 day. (@SluttDato
&@StartDato
)