Is there anything I can do to improve the overall duration of this merge statement?
Can the duration of this merge speed up ?
The target table is partitioned (has indexed view on it which is forcing a wide update regardless etc)
Thank you
I can't immediately see a reason that this query could not use parallelism, but you could try compiling it with TF 8649 to check that. MERGE often produces inefficient parallel plans though – see later comments.
…or can the spills be prevented?
I don't see any spill warnings in the posted plan.
I believe the optimizer do check if the values building the index were actually modified even though the row is being modified and if not it does not modify the index.
Yes. The Filters before the various Index Updates and Inserts are checking to see if the indexed values have changed; if not, the change is skipped.
Can the duration of this merge speed up?
Probably. I have generally found that MERGE does not produce plans that perform well with larger sets, and have had success by rewriting UPSERT merges as two separate INSERT and UPDATE statements. This may seem counter-intuitive, but it has been my experience. It can also be easier to get 'natural' parallelism with the separate statements.
Side note: I very much prefer to express the test (x IS NULL AND y IS NOT NULL) OR (x IS NOT NULL AND y IS NULL) OR (x <> y) as NOT EXISTS (SELECT x INTERSECT SELECT y). This rewrite is more compact and often produces more efficient plans.
There are 9 merge statements towards the bottom all got converted to update + Insert (in the order) (table list MERGE tTax.LiProperty
MERGE tTax.PropertyLandDimensionDetail
MERGE tTax.FiProperty
MERGE tTax.PropertyLot
MERGE tTax.PropertyLegalPartyParsed
MERGE tTax.PropertyLegalPartyOrigName
MERGE tTax.PropertyLegalPartyName
MERGE tTax.PropertyLegalParty
MERGE tTax.Property
)
If more than 2 column to be compared in update I used the intersect to compare if not regular 3 step compare.
I noticed in some cases merge did parallelism and the update did not and the duration was higher because of that.
I have a difficulty making a decision right now use the merge or update / insert or is there more suggestions?
Thank you
If this is what you wanted then OK but if it isn't then see below.
I would change to
WHEN MATCHED AND(
ISNULL(t.AbsenteeOwnrIrisCd,") <> ISNULL(s.AbsenteeOwnrIrisCd,")
OR
ISNULL(t.CntyLegalTxt,") <> ISNULL(s.CntyLegalTxt,")
...........
)
val = "
val2 = NULL
as equal whereas they are not