June 21, 2013 at 6:39 am
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
June 21, 2013 at 7:05 am
Have you seen why it is hanging? Is it being blocked by another process?
June 21, 2013 at 7:12 am
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
June 21, 2013 at 7:16 am
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..
June 21, 2013 at 7:21 am
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.
June 21, 2013 at 7:28 am
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.
June 21, 2013 at 7:46 am
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'.
June 21, 2013 at 7:53 am
This help?
WHEN NOT MATCHED BY SOURCE AND
c_target.sessionid = @sessionid
AND c_target.offer_type = 'GWP'
THEN DELETE
June 21, 2013 at 8:22 am
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 🙁
June 21, 2013 at 8:30 am
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.
June 21, 2013 at 4:46 pm
Cowboy DBA (6/21/2013)
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.
Thanks Cowboy, I will give that try, however i did get it to work another way. The price is always a certain amount for 'GWP' so i check the price , similar to the check for GWP 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply