MERGE statement - WHEN NOT MATCHED DELETE takes ages

Question

Post reply

MERGE statement - WHEN NOT MATCHED DELETE takes ages

bugg

SSCertifiable

Points: 7567
More actions
July 19, 2013 at 8:58 am

#275434

I am using the MERGE functionality in SQL 2008.
My code works fine. However if I clean the buffers I've noticed that the WHEN NOT MATCHED DELETE statement adds an extra 8 seconds onto by sub 1 second query even if there is nothing to delete
Is this usually the case with the MERGE statement?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 1

Paul White posted an interesting item about MERGE here

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

Can you please provide your merge script that you have written,,,,,

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

bugg SSCertifiable Points: 7567 More actions · Answer 3

Mark-101232 (7/19/2013)
Paul White posted an interesting item about MERGE here

Thanks for that link having a good read

Voide Mr or Mrs. 500 Points: 548 More actions · Answer 4

Voide

Mr or Mrs. 500

Points: 548

July 19, 2013 at 9:16 am

#1634082

My code works fine.

So post it.

vultar Hall of Fame Points: 3271 More actions · Answer 5

How many rows are in the data sets being merged ?

I guessing the population of the cache is taking the additional 8 seconds, especially if the query usually runs sub second.

Cheers

Vultar

bugg SSCertifiable Points: 7567 More actions · Answer 6

Hi Guys, I will post my code on Monday. After reading that very informative post i do think I may have to go with a separate delete option.:unsure:

bugg SSCertifiable Points: 7567 More actions · Answer 7

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

bugg SSCertifiable Points: 7567 More actions · Answer 8

vultar (7/19/2013)
How many rows are in the data sets being merged ?
I guessing the population of the cache is taking the additional 8 seconds, especially if the query usually runs sub second.
Cheers
Vultar

The TARGET table has about 5 million rows in. I using a WITH statement to limit this result set in the TARGET table to help improve performance.

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 9

Can you post your DDL also?

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

bugg SSCertifiable Points: 7567 More actions · Answer 10

Matthew Darwin (7/22/2013)
Can you post your DDL also?

I found the issue the join in the TARGET on sku and giftsku was slowing it down.

I changed the joining column and it has improved performance greatly. 😀