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 DELETE from TARGET when not in SOURCE 'hanging' Expand / Collapse
Author
Message
Posted Friday, June 21, 2013 6:39 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 3:49 AM
Points: 184, Visits: 653
My code runs fine until I try Delete an entry that is in my target table but not in my source. The insert & update all work.

    MERGE dbo.cart AS c_target
USING (SELECT
@sessionid as sessionid
,ISNULL(o.free_subsku,0) as subsku
,o.free_sku as mainsku
,ko.cartrsn
,o.qty_free * ko.qty as qty
,COALESCE(sp.price,p.price) as price ,COALESCE(sp.[weight],p.[weight]) as [weight]
FROM offer o
inner join @cart_offer ko on o.id = ko.offer_id
inner join products p on o.free_sku = p.sku
left join subproducts sp on o.free_subsku = sp.sku

WHERE o.active = 1 and o.offer_type = 'GWP'
)
as c_source
ON c_target.sessionid = c_source.sessionid
and c_target.mainsku = c_source.mainsku
and c_target.subsku = c_source.subsku


--Issue here?!?!
WHEN NOT MATCHED BY SOURCE AND c_target.sessionid = @sessionid
THEN DELETE


WHEN MATCHED AND c_target.qty <> c_source.qty THEN
UPDATE
SET c_target.qty = c_source.qty
,c_target.price = c_source.price
,c_target.[weight] = c_source.[weight]

WHEN NOT MATCHED BY TARGET THEN

INSERT (sessionid,subsku,mainsku,qty,price,[weight])
VALUES (@sessionid,c_source.subsku,c_source.mainsku,c_source.qty,c_source.price,c_source.[weight]);


I know I should attach some test data, but can anyone see anything obviously wrong in my code?
Also cart table has about 5mil rows in it.

thanks
Post #1466158
Posted Friday, June 21, 2013 7:04 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:52 PM
Points: 99, Visits: 2,197
Have you seen why it is hanging? Is it being blocked by another process?
Post #1466165
Posted Friday, June 21, 2013 7:12 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 3:49 AM
Points: 184, Visits: 653
Cowboy DBA (6/21/2013)
Have you seen why it is hanging? Is it being blocked by another process?


No its not getting blocked its on my dev SQL database.
However I've just spotted that the update doesn't work either. Only the insert
Post #1466166
Posted Friday, June 21, 2013 7:16 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 3:49 AM
Points: 184, Visits: 653
bugg (6/21/2013)
Cowboy DBA (6/21/2013)
Have you seen why it is hanging? Is it being blocked by another process?


No its not getting blocked its on my dev SQL database.
However I've just spotted that the update doesn't work either. Only the insert


Actually update is working, just delete is hanging..
Post #1466169
Posted Friday, June 21, 2013 7:20 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:52 PM
Points: 99, Visits: 2,197
When you say "hanging" what do you mean? I take it as there is something blocking which in turn implies that the update would not complete.

Like I said, check any blocking SPIDs and also check the query plan of your statement.
Post #1466170
Posted Friday, June 21, 2013 7:28 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:52 PM
Points: 99, Visits: 2,197
You might be suffering from parallelism. If you run this


sp_who2

And see several rows of the same SPID then there might be a parallel query issue

Then try this:

MERGE dbo.cart AS c_target
USING (SELECT
@sessionid as sessionid
,ISNULL(o.free_subsku,0) as subsku
,o.free_sku as mainsku
,ko.cartrsn
,o.qty_free * ko.qty as qty
,COALESCE(sp.price,p.price) as price ,COALESCE(sp.[weight],p.[weight]) as [weight]
FROM offer o
inner join @cart_offer ko on o.id = ko.offer_id
inner join products p on o.free_sku = p.sku
left join subproducts sp on o.free_subsku = sp.sku

WHERE o.active = 1 and o.offer_type = 'GWP'
)
as c_source
ON c_target.sessionid = c_source.sessionid
and c_target.mainsku = c_source.mainsku
and c_target.subsku = c_source.subsku


--Issue here?!?!
WHEN NOT MATCHED BY SOURCE AND c_target.sessionid = @sessionid
THEN DELETE


WHEN MATCHED AND c_target.qty <> c_source.qty THEN
UPDATE
SET c_target.qty = c_source.qty
,c_target.price = c_source.price
,c_target.[weight] = c_source.[weight]

WHEN NOT MATCHED BY TARGET THEN

INSERT (sessionid,subsku,mainsku,qty,price,[weight])
VALUES (@sessionid,c_source.subsku,c_source.mainsku,c_source.qty,c_source.price,c_source.[weight])
OPTION (MAXDOP 1) ;

And see if that helps.

Would still like to understand what you mean by "hanging" though.
Post #1466178
Posted Friday, June 21, 2013 7:46 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 3:49 AM
Points: 184, Visits: 653
Apologies I probably didn't make my self clear, the query wasn't hanging but was taking an age to execute to the point i would stop the query.

I think it was because it was trying to delete everything in my target table that didn't have a match to the source.
After creating my target table using a CTE
 WITH k_target AS 
(
SELECT *
FROM Kart
WHERE sessionid = @sessionid
)
MERGE k_target

it worked but not as expected. It deleted everything that wasn't in my source for that sessionid. However the other records for that sessionid I want to keep as they are not of offer_type 'GWP'.
Post #1466188
Posted Friday, June 21, 2013 7:53 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:52 PM
Points: 99, Visits: 2,197
This help?
WHEN NOT MATCHED BY SOURCE AND 
c_target.sessionid = @sessionid
AND c_target.offer_type = 'GWP'

THEN DELETE

Post #1466194
Posted Friday, June 21, 2013 8:22 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 3:49 AM
Points: 184, Visits: 653
Cowboy DBA (6/21/2013)
This help?
WHEN NOT MATCHED BY SOURCE AND 
c_target.sessionid = @sessionid
AND c_target.offer_type = 'GWP'

THEN DELETE



Unfortunately the offer_type doesn't exist in the target table
Post #1466211
Posted Friday, June 21, 2013 8:30 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 1:52 PM
Points: 99, Visits: 2,197
Assuming I've understood what you're on about you might have to do your delete as:
DELETE FROM dbo.cart C
LEFT OUTER JOIN

(SELECT
@sessionid as sessionid
,ISNULL(o.free_subsku,0) as subsku
,o.free_sku as mainsku
,ko.cartrsn
,o.qty_free * ko.qty as qty
,COALESCE(sp.price,p.price) as price ,COALESCE(sp.[weight],p.[weight]) as [weight]
FROM offer o
inner join @cart_offer ko on o.id = ko.offer_id
inner join products p on o.free_sku = p.sku
left join subproducts sp on o.free_subsku = sp.sku

WHERE o.active = 1 and o.offer_type = 'GWP'
) D
ON C.sessionid = D.sessionid
and C.mainsku = D.mainsku
and C.subsku = D.subsku
WHERE D.sessionid IS NULL
AND C.sessionid = @sessionid

As I said earlier, you need to look at your query plan to ascertain what is going on.
Post #1466214
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse