Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MERGE statement - WHEN NOT MATCHED DELETE takes ages


MERGE statement - WHEN NOT MATCHED DELETE takes ages

Author
Message
bugg
bugg
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 967
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
Mark Cowne
Mark Cowne
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2095 Visits: 22803
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
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2427 Visits: 2763
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
bugg
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 967
Mark-101232 (7/19/2013)
Paul White posted an interesting item about MERGE here


Thanks for that link having a good read
Voide
Voide
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 280

My code works fine.



So post it.
vultar
vultar
Old Hand
Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)Old Hand (310 reputation)

Group: General Forum Members
Points: 310 Visits: 1213
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
bugg
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 967
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
bugg
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 967
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
bugg
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 967
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
Matthew Darwin
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 875
Can you post your DDL also?

Follow me on twitter @EvoDBA

Check out my blog Natural Selection DBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search