Below is my code, is there anyway i can keep the MERGE but improve the delete performance? Is there a better alternative approach to doing a MERGE?
BEGIN TRY
BEGIN TRANSACTION
;WITH TARGET AS
(
SELECT k.sessionid,k.sku,k.qty,k.price,k.[weight],k.isoffer
,o.gift_sku
FROM cart k
inner join offer o on k.sku = o.gift_sku
where k.sessionid = @sessionid
)
MERGE TARGET
USING (SELECT
@sessionid as sessionid
,o.id
,o.offer_type
,o.gift_sku as sku
,CASE MAX(o.spend_qualifier)
WHEN NULL THEN MAX(o.qty_free) * (SUM(ko.qty)/MAX(o.qty_qualifier))
ELSE MAX(o.qty_free)
END as qty
,SUM(offer_p.price) as price
,SUM(offer_p.[weight]) as [weight]
FROM offer o
inner join @cart_offer ko on o.id = ko.offer_id
inner join products cartprod on ko.sku = cartprod.sku and cartprod.active = 1
inner join products offer_p on o.gift_sku = offer_p.sku and offer_p.active = 3
WHERE o.active = 1 and o.uk_only <= @uk_only and o.offer_type IN ('GWP','PWP')
and (exists (select 1 from offer_country oc where oc.offer_id = o.id and oc.shipcountries_protx = @country)
or not exists (select 1 from offer_country oc where o.id = oc.offer_id ))
GROUP BY o.id,o.offer_type ,o.gift_sku
HAVING (SUM(ko.qty) >= MAX(o.qty_qualifier) and MAX(o.qty_qualifier)> 0)
OR (SUM(ko.price*ko.qty) > MAX(o.spend_qualifier))
)SOURCE
ON TARGET.sessionid = SOURCE.sessionid
and TARGET.sku = SOURCE.sku
--remove from cart
WHEN NOT MATCHED BY SOURCE and TARGET.sessionid = @sessionid and TARGET.gift_sku IS NOT NULL
THEN DELETE
--matched but ofer_id not set for PWP
WHEN MATCHED AND (TARGET.isoffer <> SOURCE.id)
THEN UPDATE SET TARGET.isoffer = SOURCE.id
--not yet in cart
WHEN NOT MATCHED BY TARGET AND SOURCE.offer_type = 'GWP' THEN
INSERT (sessionid,sku,qty,price,[weight],isoffer)
VALUES (@sessionid,SOURCE.sku,SOURCE.qty,SOURCE.price,SOURCE.[weight],SOURCE.id); --add offer id to cart
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
SELECT 'Error:' + ERROR_MESSAGE() AS ErrorMessage;
END CATCH
Thanks