Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

MERGE statement - WHEN NOT MATCHED DELETE takes ages Expand / Collapse
Author
Message
Posted Friday, July 19, 2013 8:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 184, Visits: 652
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
Post #1475538
Posted Friday, July 19, 2013 9:06 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
Paul White posted an interesting item about MERGE here

____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1475542
Posted Friday, July 19, 2013 9:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 12:34 AM
Points: 1,888, Visits: 2,326
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/
Post #1475548
Posted Friday, July 19, 2013 9:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 184, Visits: 652
Mark-101232 (7/19/2013)
Paul White posted an interesting item about MERGE here


Thanks for that link having a good read
Post #1475549
Posted Friday, July 19, 2013 9:16 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 10:17 AM
Points: 31, Visits: 278

My code works fine.



So post it.
Post #1475551
Posted Friday, July 19, 2013 11:53 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 11:36 AM
Points: 234, Visits: 1,090
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
Post #1475623
Posted Saturday, July 20, 2013 3:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 184, Visits: 652
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.
Post #1475739
Posted Monday, July 22, 2013 1:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 184, Visits: 652
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
Post #1475883
Posted Monday, July 22, 2013 1:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:08 AM
Points: 184, Visits: 652
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.
Post #1475886
Posted Monday, July 22, 2013 3:56 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 146, Visits: 633
Can you post your DDL also?
Post #1475915
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse