Forcing execution plan with local join hintsBetter execution plan if parameter is stored in local variableSame schema, same data, same execution plan?Sort operator positioning in an execution planUnused Memory Grant on DeleteRow estimates always too lowWhy is selecting all resulting columns of this query faster than selecting the one column I care about?SQL Server chooses Nested Loop join with dimensional table and make seek for each rowForce SQL Engine to prepare Non-Null plan only for a query that use variableWarning in Execution PlanAzure SQL Server - big query choosing bad execution plan

How do I subvert the tropes of a train heist?

Apparent Ring of Craters on the Moon

What was this black-and-white film set in the Arctic or Antarctic where the monster/alien gets fried in the end?

I think I may have violated academic integrity last year - what should I do?

How is character development a major role in the plot of a story

The Passive Wisdom (Perception) score of my character on D&D Beyond seems too high

How current works

What F1 in name of seeds/varieties means?

Where can I find the list of all tendons in the human body?

Tic-Tac-Toe for the terminal

1960s sci-fi novella with a character who is treated as invisible by being ignored

What does the behaviour of water on the skin of an aircraft in flight tell us?

Terminology about G- simplicial complexes

How to properly maintain eye contact with people that have distinct facial features?

Leading and Suffering Numbers

Can a wire having a 610-670 THz (frequency of blue light) AC frequency supply, generate blue light?

What is the best linguistic term for describing the kw > p / gw > b change, and its usual companion s > h

Comment dit-on « I’ll tell you what » ?

What is the 中 in ダウンロード中?

Is there an explanation for Austria's Freedom Party virtually retaining its vote share despite recent scandal?

Modern approach to radio buttons

Is this story about US tax office reasonable?

What are these (utility?) boxes at the side of the house?

Is floating in space similar to falling under gravity?



Forcing execution plan with local join hints


Better execution plan if parameter is stored in local variableSame schema, same data, same execution plan?Sort operator positioning in an execution planUnused Memory Grant on DeleteRow estimates always too lowWhy is selecting all resulting columns of this query faster than selecting the one column I care about?SQL Server chooses Nested Loop join with dimensional table and make seek for each rowForce SQL Engine to prepare Non-Null plan only for a query that use variableWarning in Execution PlanAzure SQL Server - big query choosing bad execution plan






.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;








4















I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between good and bad execution plans). This way I get SQL Server to choose most of the plan, while enforcing the few joins I know are necessary to be able to finish the queries.



In the execution plans below, I want to enforce the join types to be somewhat the same and will therefore use local joins hints also for these. However, I was wondering if I am able to trigger other actions in the execution plans as well, such as:



List item



  • SORT(Distinct Sort)

  • Stram Aggregate(Aggregate)

Are these actions something I can choose, or are they dependent on join types/order choosed during the query?



Both plans are created by XML extracted from Query Store.



Good execution plan: https://www.brentozar.com/pastetheplan/?id=HyYMn7K2V



Bad execution plan: https://www.brentozar.com/pastetheplan/?id=Hka6i7Yh4










share|improve this question
























  • Hi, Query was slightly changed in february and execution time went from hours to seconds. It has degraded again without being changed. Project has ended and there is no funding to do the complete testing needed for a new rewriting of these steps in the jobs, hence that is not an alternative. I believe these issues are caused by skewed statistics due to failed maintenance job (full transaction LOG). This join should always be a hash join (300m+ rows compared with 300m+ rows). Ever increasing tables. I guess this is the main issue, but then again, I cannot change behaviour but need it to finish.

    – Creztian
    May 15 at 8:26







  • 1





    When looking at the statistics information in the query plan, the only difference is on the statistics on the 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)

    – Randi Vertongen
    May 15 at 8:37







  • 2





    I agree with Randi. Id start by updating stats and maybe rebuilding indexes. If yr using sql 2016+ query store might be useful in helping diagnose this. Altering indexes or maybe even dropping indexes may help. Id consider changing stats and indexes before altering a query.

    – Sir Swears-a-lot
    May 15 at 9:03


















4















I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between good and bad execution plans). This way I get SQL Server to choose most of the plan, while enforcing the few joins I know are necessary to be able to finish the queries.



In the execution plans below, I want to enforce the join types to be somewhat the same and will therefore use local joins hints also for these. However, I was wondering if I am able to trigger other actions in the execution plans as well, such as:



List item



  • SORT(Distinct Sort)

  • Stram Aggregate(Aggregate)

Are these actions something I can choose, or are they dependent on join types/order choosed during the query?



Both plans are created by XML extracted from Query Store.



Good execution plan: https://www.brentozar.com/pastetheplan/?id=HyYMn7K2V



Bad execution plan: https://www.brentozar.com/pastetheplan/?id=Hka6i7Yh4










share|improve this question
























  • Hi, Query was slightly changed in february and execution time went from hours to seconds. It has degraded again without being changed. Project has ended and there is no funding to do the complete testing needed for a new rewriting of these steps in the jobs, hence that is not an alternative. I believe these issues are caused by skewed statistics due to failed maintenance job (full transaction LOG). This join should always be a hash join (300m+ rows compared with 300m+ rows). Ever increasing tables. I guess this is the main issue, but then again, I cannot change behaviour but need it to finish.

    – Creztian
    May 15 at 8:26







  • 1





    When looking at the statistics information in the query plan, the only difference is on the statistics on the 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)

    – Randi Vertongen
    May 15 at 8:37







  • 2





    I agree with Randi. Id start by updating stats and maybe rebuilding indexes. If yr using sql 2016+ query store might be useful in helping diagnose this. Altering indexes or maybe even dropping indexes may help. Id consider changing stats and indexes before altering a query.

    – Sir Swears-a-lot
    May 15 at 9:03














4












4








4








I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between good and bad execution plans). This way I get SQL Server to choose most of the plan, while enforcing the few joins I know are necessary to be able to finish the queries.



In the execution plans below, I want to enforce the join types to be somewhat the same and will therefore use local joins hints also for these. However, I was wondering if I am able to trigger other actions in the execution plans as well, such as:



List item



  • SORT(Distinct Sort)

  • Stram Aggregate(Aggregate)

Are these actions something I can choose, or are they dependent on join types/order choosed during the query?



Both plans are created by XML extracted from Query Store.



Good execution plan: https://www.brentozar.com/pastetheplan/?id=HyYMn7K2V



Bad execution plan: https://www.brentozar.com/pastetheplan/?id=Hka6i7Yh4










share|improve this question
















I have several encounters with bad execution plans in batch jobs during a week, and to avoid forcing plans I have moved on to adding local join hints (when these join types are the difference between good and bad execution plans). This way I get SQL Server to choose most of the plan, while enforcing the few joins I know are necessary to be able to finish the queries.



In the execution plans below, I want to enforce the join types to be somewhat the same and will therefore use local joins hints also for these. However, I was wondering if I am able to trigger other actions in the execution plans as well, such as:



List item



  • SORT(Distinct Sort)

  • Stram Aggregate(Aggregate)

Are these actions something I can choose, or are they dependent on join types/order choosed during the query?



Both plans are created by XML extracted from Query Store.



Good execution plan: https://www.brentozar.com/pastetheplan/?id=HyYMn7K2V



Bad execution plan: https://www.brentozar.com/pastetheplan/?id=Hka6i7Yh4







sql-server t-sql join execution-plan






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited May 15 at 6:45







Creztian

















asked May 15 at 6:38









CreztianCreztian

584




584












  • Hi, Query was slightly changed in february and execution time went from hours to seconds. It has degraded again without being changed. Project has ended and there is no funding to do the complete testing needed for a new rewriting of these steps in the jobs, hence that is not an alternative. I believe these issues are caused by skewed statistics due to failed maintenance job (full transaction LOG). This join should always be a hash join (300m+ rows compared with 300m+ rows). Ever increasing tables. I guess this is the main issue, but then again, I cannot change behaviour but need it to finish.

    – Creztian
    May 15 at 8:26







  • 1





    When looking at the statistics information in the query plan, the only difference is on the statistics on the 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)

    – Randi Vertongen
    May 15 at 8:37







  • 2





    I agree with Randi. Id start by updating stats and maybe rebuilding indexes. If yr using sql 2016+ query store might be useful in helping diagnose this. Altering indexes or maybe even dropping indexes may help. Id consider changing stats and indexes before altering a query.

    – Sir Swears-a-lot
    May 15 at 9:03


















  • Hi, Query was slightly changed in february and execution time went from hours to seconds. It has degraded again without being changed. Project has ended and there is no funding to do the complete testing needed for a new rewriting of these steps in the jobs, hence that is not an alternative. I believe these issues are caused by skewed statistics due to failed maintenance job (full transaction LOG). This join should always be a hash join (300m+ rows compared with 300m+ rows). Ever increasing tables. I guess this is the main issue, but then again, I cannot change behaviour but need it to finish.

    – Creztian
    May 15 at 8:26







  • 1





    When looking at the statistics information in the query plan, the only difference is on the statistics on the 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)

    – Randi Vertongen
    May 15 at 8:37







  • 2





    I agree with Randi. Id start by updating stats and maybe rebuilding indexes. If yr using sql 2016+ query store might be useful in helping diagnose this. Altering indexes or maybe even dropping indexes may help. Id consider changing stats and indexes before altering a query.

    – Sir Swears-a-lot
    May 15 at 9:03

















Hi, Query was slightly changed in february and execution time went from hours to seconds. It has degraded again without being changed. Project has ended and there is no funding to do the complete testing needed for a new rewriting of these steps in the jobs, hence that is not an alternative. I believe these issues are caused by skewed statistics due to failed maintenance job (full transaction LOG). This join should always be a hash join (300m+ rows compared with 300m+ rows). Ever increasing tables. I guess this is the main issue, but then again, I cannot change behaviour but need it to finish.

– Creztian
May 15 at 8:26






Hi, Query was slightly changed in february and execution time went from hours to seconds. It has degraded again without being changed. Project has ended and there is no funding to do the complete testing needed for a new rewriting of these steps in the jobs, hence that is not an alternative. I believe these issues are caused by skewed statistics due to failed maintenance job (full transaction LOG). This join should always be a hash join (300m+ rows compared with 300m+ rows). Ever increasing tables. I guess this is the main issue, but then again, I cannot change behaviour but need it to finish.

– Creztian
May 15 at 8:26





1




1





When looking at the statistics information in the query plan, the only difference is on the statistics on the 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)

– Randi Vertongen
May 15 at 8:37






When looking at the statistics information in the query plan, the only difference is on the statistics on the 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)

– Randi Vertongen
May 15 at 8:37





2




2





I agree with Randi. Id start by updating stats and maybe rebuilding indexes. If yr using sql 2016+ query store might be useful in helping diagnose this. Altering indexes or maybe even dropping indexes may help. Id consider changing stats and indexes before altering a query.

– Sir Swears-a-lot
May 15 at 9:03






I agree with Randi. Id start by updating stats and maybe rebuilding indexes. If yr using sql 2016+ query store might be useful in helping diagnose this. Altering indexes or maybe even dropping indexes may help. Id consider changing stats and indexes before altering a query.

– Sir Swears-a-lot
May 15 at 9:03











1 Answer
1






active

oldest

votes


















7















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.






share|improve this answer

























  • Great answer! Amazing performance gain for solution 1 and 2 (I cannot test 3 in same environment with fresh data). I thought NOT EXISTS and LEFT JOIN behaved differently on handling NULL values, but it seems like I have been confused with NOT IN and NOT EXISTS. Can you confirm that results of LEFT JOIN and NOT EXISTS will be the same in all scenarioes?

    – Creztian
    May 15 at 10:05







  • 1





    @Creztian Great! The NULL values remark is true for the NOT IN but you do make a good point. If a different column than one of the join columns was used to check for the IS NULL the result could be different. Even ifAvtaleNummer column has NULL values as data, then these are filtered out by the LEFT JOIN before applying the filter. As a result, In this case the NOT EXISTS does the same filtering but can apply it earlier in the execution plan. More information on this in this excellent article by Aaron Bertrand.

    – Randi Vertongen
    May 15 at 10:18







  • 3





    UNION ALL is faster than UNION, since UNION does an implicid DISTINCT, so you should prefer this (except you know that there are duplicates and want to get rid of them, but since there is already a group by in the sub statements, you would need to GROUP (and SUM) again in the outer query

    – Thomas Franz
    May 15 at 12:41











Your Answer








StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













draft saved

draft discarded


















StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f238194%2fforcing-execution-plan-with-local-join-hints%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









7















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.






share|improve this answer

























  • Great answer! Amazing performance gain for solution 1 and 2 (I cannot test 3 in same environment with fresh data). I thought NOT EXISTS and LEFT JOIN behaved differently on handling NULL values, but it seems like I have been confused with NOT IN and NOT EXISTS. Can you confirm that results of LEFT JOIN and NOT EXISTS will be the same in all scenarioes?

    – Creztian
    May 15 at 10:05







  • 1





    @Creztian Great! The NULL values remark is true for the NOT IN but you do make a good point. If a different column than one of the join columns was used to check for the IS NULL the result could be different. Even ifAvtaleNummer column has NULL values as data, then these are filtered out by the LEFT JOIN before applying the filter. As a result, In this case the NOT EXISTS does the same filtering but can apply it earlier in the execution plan. More information on this in this excellent article by Aaron Bertrand.

    – Randi Vertongen
    May 15 at 10:18







  • 3





    UNION ALL is faster than UNION, since UNION does an implicid DISTINCT, so you should prefer this (except you know that there are duplicates and want to get rid of them, but since there is already a group by in the sub statements, you would need to GROUP (and SUM) again in the outer query

    – Thomas Franz
    May 15 at 12:41















7















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.






share|improve this answer

























  • Great answer! Amazing performance gain for solution 1 and 2 (I cannot test 3 in same environment with fresh data). I thought NOT EXISTS and LEFT JOIN behaved differently on handling NULL values, but it seems like I have been confused with NOT IN and NOT EXISTS. Can you confirm that results of LEFT JOIN and NOT EXISTS will be the same in all scenarioes?

    – Creztian
    May 15 at 10:05







  • 1





    @Creztian Great! The NULL values remark is true for the NOT IN but you do make a good point. If a different column than one of the join columns was used to check for the IS NULL the result could be different. Even ifAvtaleNummer column has NULL values as data, then these are filtered out by the LEFT JOIN before applying the filter. As a result, In this case the NOT EXISTS does the same filtering but can apply it earlier in the execution plan. More information on this in this excellent article by Aaron Bertrand.

    – Randi Vertongen
    May 15 at 10:18







  • 3





    UNION ALL is faster than UNION, since UNION does an implicid DISTINCT, so you should prefer this (except you know that there are duplicates and want to get rid of them, but since there is already a group by in the sub statements, you would need to GROUP (and SUM) again in the outer query

    – Thomas Franz
    May 15 at 12:41













7












7








7








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.






share|improve this answer
















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.







share|improve this answer














share|improve this answer



share|improve this answer








edited May 15 at 13:10

























answered May 15 at 8:59









Randi VertongenRandi Vertongen

5,8362927




5,8362927












  • Great answer! Amazing performance gain for solution 1 and 2 (I cannot test 3 in same environment with fresh data). I thought NOT EXISTS and LEFT JOIN behaved differently on handling NULL values, but it seems like I have been confused with NOT IN and NOT EXISTS. Can you confirm that results of LEFT JOIN and NOT EXISTS will be the same in all scenarioes?

    – Creztian
    May 15 at 10:05







  • 1





    @Creztian Great! The NULL values remark is true for the NOT IN but you do make a good point. If a different column than one of the join columns was used to check for the IS NULL the result could be different. Even ifAvtaleNummer column has NULL values as data, then these are filtered out by the LEFT JOIN before applying the filter. As a result, In this case the NOT EXISTS does the same filtering but can apply it earlier in the execution plan. More information on this in this excellent article by Aaron Bertrand.

    – Randi Vertongen
    May 15 at 10:18







  • 3





    UNION ALL is faster than UNION, since UNION does an implicid DISTINCT, so you should prefer this (except you know that there are duplicates and want to get rid of them, but since there is already a group by in the sub statements, you would need to GROUP (and SUM) again in the outer query

    – Thomas Franz
    May 15 at 12:41

















  • Great answer! Amazing performance gain for solution 1 and 2 (I cannot test 3 in same environment with fresh data). I thought NOT EXISTS and LEFT JOIN behaved differently on handling NULL values, but it seems like I have been confused with NOT IN and NOT EXISTS. Can you confirm that results of LEFT JOIN and NOT EXISTS will be the same in all scenarioes?

    – Creztian
    May 15 at 10:05







  • 1





    @Creztian Great! The NULL values remark is true for the NOT IN but you do make a good point. If a different column than one of the join columns was used to check for the IS NULL the result could be different. Even ifAvtaleNummer column has NULL values as data, then these are filtered out by the LEFT JOIN before applying the filter. As a result, In this case the NOT EXISTS does the same filtering but can apply it earlier in the execution plan. More information on this in this excellent article by Aaron Bertrand.

    – Randi Vertongen
    May 15 at 10:18







  • 3





    UNION ALL is faster than UNION, since UNION does an implicid DISTINCT, so you should prefer this (except you know that there are duplicates and want to get rid of them, but since there is already a group by in the sub statements, you would need to GROUP (and SUM) again in the outer query

    – Thomas Franz
    May 15 at 12:41
















Great answer! Amazing performance gain for solution 1 and 2 (I cannot test 3 in same environment with fresh data). I thought NOT EXISTS and LEFT JOIN behaved differently on handling NULL values, but it seems like I have been confused with NOT IN and NOT EXISTS. Can you confirm that results of LEFT JOIN and NOT EXISTS will be the same in all scenarioes?

– Creztian
May 15 at 10:05






Great answer! Amazing performance gain for solution 1 and 2 (I cannot test 3 in same environment with fresh data). I thought NOT EXISTS and LEFT JOIN behaved differently on handling NULL values, but it seems like I have been confused with NOT IN and NOT EXISTS. Can you confirm that results of LEFT JOIN and NOT EXISTS will be the same in all scenarioes?

– Creztian
May 15 at 10:05





1




1





@Creztian Great! The NULL values remark is true for the NOT IN but you do make a good point. If a different column than one of the join columns was used to check for the IS NULL the result could be different. Even ifAvtaleNummer column has NULL values as data, then these are filtered out by the LEFT JOIN before applying the filter. As a result, In this case the NOT EXISTS does the same filtering but can apply it earlier in the execution plan. More information on this in this excellent article by Aaron Bertrand.

– Randi Vertongen
May 15 at 10:18






@Creztian Great! The NULL values remark is true for the NOT IN but you do make a good point. If a different column than one of the join columns was used to check for the IS NULL the result could be different. Even ifAvtaleNummer column has NULL values as data, then these are filtered out by the LEFT JOIN before applying the filter. As a result, In this case the NOT EXISTS does the same filtering but can apply it earlier in the execution plan. More information on this in this excellent article by Aaron Bertrand.

– Randi Vertongen
May 15 at 10:18





3




3





UNION ALL is faster than UNION, since UNION does an implicid DISTINCT, so you should prefer this (except you know that there are duplicates and want to get rid of them, but since there is already a group by in the sub statements, you would need to GROUP (and SUM) again in the outer query

– Thomas Franz
May 15 at 12:41





UNION ALL is faster than UNION, since UNION does an implicid DISTINCT, so you should prefer this (except you know that there are duplicates and want to get rid of them, but since there is already a group by in the sub statements, you would need to GROUP (and SUM) again in the outer query

– Thomas Franz
May 15 at 12:41

















draft saved

draft discarded
















































Thanks for contributing an answer to Database Administrators Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid


  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f238194%2fforcing-execution-plan-with-local-join-hints%23new-answer', 'question_page');

);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Wikipedia:Vital articles Мазмуну Biography - Өмүр баян Philosophy and psychology - Философия жана психология Religion - Дин Social sciences - Коомдук илимдер Language and literature - Тил жана адабият Science - Илим Technology - Технология Arts and recreation - Искусство жана эс алуу History and geography - Тарых жана география Навигация менюсу

Club Baloncesto Breogán Índice Historia | Pavillón | Nome | O Breogán na cultura popular | Xogadores | Adestradores | Presidentes | Palmarés | Historial | Líderes | Notas | Véxase tamén | Menú de navegacióncbbreogan.galCadroGuía oficial da ACB 2009-10, páxina 201Guía oficial ACB 1992, páxina 183. Editorial DB.É de 6.500 espectadores sentados axeitándose á última normativa"Estudiantes Junior, entre as mellores canteiras"o orixinalHemeroteca El Mundo Deportivo, 16 setembro de 1970, páxina 12Historia do BreogánAlfredo Pérez, o último canoneiroHistoria C.B. BreogánHemeroteca de El Mundo DeportivoJimmy Wright, norteamericano do Breogán deixará Lugo por ameazas de morteResultados de Breogán en 1986-87Resultados de Breogán en 1990-91Ficha de Velimir Perasović en acb.comResultados de Breogán en 1994-95Breogán arrasa al Barça. "El Mundo Deportivo", 27 de setembro de 1999, páxina 58CB Breogán - FC BarcelonaA FEB invita a participar nunha nova Liga EuropeaCharlie Bell na prensa estatalMáximos anotadores 2005Tempada 2005-06 : Tódolos Xogadores da Xornada""Non quero pensar nunha man negra, mais pregúntome que está a pasar""o orixinalRaúl López, orgulloso dos xogadores, presume da boa saúde económica do BreogánJulio González confirma que cesa como presidente del BreogánHomenaxe a Lisardo GómezA tempada do rexurdimento celesteEntrevista a Lisardo GómezEl COB dinamita el Pazo para forzar el quinto (69-73)Cafés Candelas, patrocinador del CB Breogán"Suso Lázare, novo presidente do Breogán"o orixinalCafés Candelas Breogán firma el mayor triunfo de la historiaEl Breogán realizará 17 homenajes por su cincuenta aniversario"O Breogán honra ao seu fundador e primeiro presidente"o orixinalMiguel Giao recibiu a homenaxe do PazoHomenaxe aos primeiros gladiadores celestesO home que nos amosa como ver o Breo co corazónTita Franco será homenaxeada polos #50anosdeBreoJulio Vila recibirá unha homenaxe in memoriam polos #50anosdeBreo"O Breogán homenaxeará aos seus aboados máis veteráns"Pechada ovación a «Capi» Sanmartín e Ricardo «Corazón de González»Homenaxe por décadas de informaciónPaco García volve ao Pazo con motivo do 50 aniversario"Resultados y clasificaciones""O Cafés Candelas Breogán, campión da Copa Princesa""O Cafés Candelas Breogán, equipo ACB"C.B. Breogán"Proxecto social"o orixinal"Centros asociados"o orixinalFicha en imdb.comMario Camus trata la recuperación del amor en 'La vieja música', su última película"Páxina web oficial""Club Baloncesto Breogán""C. B. Breogán S.A.D."eehttp://www.fegaba.com

What should I write in an apology letter, since I have decided not to join a company after accepting an offer letterShould I keep looking after accepting a job offer?What should I do when I've been verbally told I would get an offer letter, but still haven't gotten one after 4 weeks?Do I accept an offer from a company that I am not likely to join?New job hasn't confirmed starting date and I want to give current employer as much notice as possibleHow should I address my manager in my resignation letter?HR delayed background verification, now jobless as resignedNo email communication after accepting a formal written offer. How should I phrase the call?What should I do if after receiving a verbal offer letter I am informed that my written job offer is put on hold due to some internal issues?Should I inform the current employer that I am about to resign within 1-2 weeks since I have signed the offer letter and waiting for visa?What company will do, if I send their offer letter to another company