June 11, 2015 at 9:34 am
Hi, Gurus, I know It's not enough information I am sending out. I can't create a sample data as well.
I really appreciate If someone can guide me or show me the right path to change below CURSOR to CTE or other Option. The reason I WAS using the cursor to avoid deadlock in the table.
any advice would be great appreciated.
Thank You.
Here is my Cursor
DECLARE Order_CUSTOMER_Cursor CURSOR
FOR
SELECT DISTINCT
MANAGE_O_id
,@MANAGE_O_id
from #tempM
OPEN MANAGE_O_D_Cursor
FETCH NEXT FROM Order_CUSTOMER_Cursor INTO @MANAGE_O_id,@CUSTOMER_O_id
while @@FETCH_STATUS = 0
BEGIN
DECLARE @BType INT
DECLARE @BC_Date DATETIME
IF (@C_O_id > 0 )
BEGIN
SELECT TOP 1
@BType = M_Order.BType
,@BC_Date = C_Order_D.Date
From M_Order WITH (NOLOCK)
INNER JOIN M_Order_D WITH (NOLOCK) ON M_Order_D.ID = M_Order.ID
INNER JOIN C_Order_D WITH (NOLOCK) ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @CUSTOMER_O_id
SELECT @RcOUNT = count(*)
From M_Order WITH (NOLOCK)
INNER JOIN C_Order_D WITH (NOLOCK) ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @CUSTOMER_O_id
END
ELSE
BEGIN
SELECT TOP 1
@BType = M_Order.BType
,@BC_Date = C_Order_D.Date
From M_Order WITH (NOLOCK)
INNER JOIN C_Order_D WITH (NOLOCK) ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @CUSTOMER_O_id
SELECT @RcOUNT = count(*)
From M_Order WITH (NOLOCK)
INNER JOIN C_Order_D WITH (NOLOCK) ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @CUSTOMER_O_id
END
IF @RcOUNT = 0
BEGIN
IF ( @C_O_id > 0 )
BEGIN
UPDATE Customer_D_Info
set type = 'Active'
END
END
FETCH NEXT FROM Order_CUSTOMER_Cursor INTO @MANAGE_O_id,@CUSTOMER_O_id
END;
CLOSE MANAGE_O_D_Cursor
DEALLOCATE MANAGE_O_D_Cursor
END
END
June 11, 2015 at 9:46 am
So what this is trying to do is update Customer_D_Info if no records found?
A lot is missing from the code, also things are defined and never used.
Might I suggest you state your objective and explain the criteria.
June 11, 2015 at 9:51 am
A couple of things right off the bat:
1.) You have no table structure defined for #tempM, nor any sample data for us to work with.
2.) You are declaring variables within your WHILE loop, which will error out on the 2nd pass through. Declare them entirely outside of the cursor.
3.) You appear to have other variables in numerous places (beginning with @) for which we have no definition or declaration.
4.) Please use the appropriate tag before any SQL code you post, which is a left bracket, followed by code="SQL", followed by a right bracket, and be
sure to add an end tag that includes left and right brackets and has /code in between.
Here's a re-post of your code with some of the fixing in place, and some indentation to make reading the logic and the code a tad easier:
DECLARE @BType INT;
DECLARE @BC_Date DATETIME;
DECLARE Order_CUSTOMER_Cursor CURSOR
FOR
SELECT DISTINCT MANAGE_O_id, @MANAGE_O_id
FROM #tempM
OPEN MANAGE_O_D_Cursor
FETCH NEXT FROM Order_CUSTOMER_Cursor INTO @MANAGE_O_id, @CUSTOMER_O_id
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@C_O_id > 0 )
BEGIN
SELECT TOP 1 @BType = M_Order.BType, @BC_Date = C_Order_D.Date
FROM M_Order WITH (NOLOCK)
INNER JOIN M_Order_D WITH (NOLOCK)
ON M_Order_D.ID = M_Order.ID
INNER JOIN C_Order_D WITH (NOLOCK)
ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @CUSTOMER_O_id
SELECT @RcOUNT = COUNT(*)
FROM M_Order WITH (NOLOCK)
INNER JOIN C_Order_D WITH (NOLOCK)
ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @CUSTOMER_O_id
END
ELSE
BEGIN
SELECT TOP 1 @BType = M_Order.BType, @BC_Date = C_Order_D.Date
FROM M_Order WITH (NOLOCK)
INNER JOIN C_Order_D WITH (NOLOCK)
ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @CUSTOMER_O_id
SELECT @RcOUNT = COUNT(*)
FROM M_Order WITH (NOLOCK)
INNER JOIN C_Order_D WITH (NOLOCK)
ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @CUSTOMER_O_id
END
IF @RcOUNT = 0
BEGIN
IF ( @C_O_id > 0 )
BEGIN
UPDATE Customer_D_Info
SET type = 'Active'
END
END
FETCH NEXT FROM Order_CUSTOMER_Cursor INTO @MANAGE_O_id, @CUSTOMER_O_id
END;
CLOSE MANAGE_O_D_Cursor
DEALLOCATE MANAGE_O_D_Cursor
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 11, 2015 at 9:56 am
Thanks for your reply. This is one part of BIGGG SP. If I paste the giant SP everyone will confuse. That's why I just mention the part that I want to replace.
1) Reading the data from #tempM table
2) Assigning the value to Variables
3) Updating the Rows as per Variables Values
#tempM table could have 200 rows or 2000 rows. Updating my table row by row.
I am sure, you still have a lot of questions in your mind. :w00t:
In this post just trying to replace the cursor to may be CTE or other option.
Please advise.
June 11, 2015 at 10:00 am
Thank You SSCommitted for your advice.
Thank You.
June 11, 2015 at 10:23 am
Try something like
WITH cte AS (
-- your counting code
)
UPDATE CDI
SET type = 'Active'
FROM CDI
INNER JOIN ON cte ON CDI.xxx = cte.xxx AND cte.Rccount = 0
WHERE @C_O_id >0;
Since I do not have a lot to go on this is the best I can do.
June 11, 2015 at 10:44 am
As written, your code's UPDATE does the same thing as the following:
UPDATE CDI
SET type = 'Active'
FROM Customer_D_Info AS CDI
WHERE @C_O_id > 0
AND NOT EXISTS (
SELECT 1
FROM M_Order WITH (NOLOCK)
INNER JOIN C_Order_D WITH (NOLOCK)
ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @MANAGE_O_id
)
Note that your cursor's query uses a variable as the 2nd field, and thus it's value is a constant throughout the cursor execution, so why you need to use a cursor at all is unknown. Your update will affect ALL records in Customer_D_Info regardless of the value of any variable save @MANAGE_O_id, which supplies the value to @CUSTOMER_O_id, which much of the rest of the cursor is based on. As this appears to be a constant, we have no viable way to fix or improve anything without knowing if that variable is actually an abstraction of something else more complex. Without the EXACT DETAIL of the abstraction (assuming it actually is one), there's no good advice that can be provided. You really can't just section out a portion of a complex stored proc that is dependent on other pieces of it and think that you can get some viable help. You state you're having a deadlock issue, but all your table references have NOLOCK hints, so I fail to see how that would occur. We really need to see a much wider swath of that proc to have any shot at helping, and there's just CANNOT BE ANY ABSTRACTION...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 11, 2015 at 10:50 am
DECLARE @BType INT;
DECLARE @BC_Date DATETIME;
DECLARE Order_CUSTOMER_Cursor CURSOR
FOR
SELECT DISTINCT MANAGE_O_id, @MANAGE_O_id
FROM #tempM
OPEN MANAGE_O_D_Cursor
FETCH NEXT FROM Order_CUSTOMER_Cursor INTO @MANAGE_O_id, @CUSTOMER_O_id
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@C_O_id > 0 )
BEGIN
SELECT TOP 1 @BType = M_Order.BType, @BC_Date = C_Order_D.Date
FROM M_Order WITH (NOLOCK)
INNER JOIN M_Order_D WITH (NOLOCK)
ON M_Order_D.ID = M_Order.ID
INNER JOIN C_Order_D WITH (NOLOCK)
ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @CUSTOMER_O_id
SELECT @RcOUNT = COUNT(*)
FROM M_Order WITH (NOLOCK)
INNER JOIN C_Order_D WITH (NOLOCK)
ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @CUSTOMER_O_id
END
ELSE
BEGIN
SELECT TOP 1 @BType = M_Order.BType, @BC_Date = C_Order_D.Date
FROM M_Order WITH (NOLOCK)
INNER JOIN C_Order_D WITH (NOLOCK)
ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @CUSTOMER_O_id
SELECT @RcOUNT = COUNT(*)
FROM M_Order WITH (NOLOCK)
INNER JOIN C_Order_D WITH (NOLOCK)
ON C_Order_D.MO = M_Order_D.ID
WHERE M_Order_D = @CUSTOMER_O_id
END
IF @RcOUNT = 0
BEGIN
IF ( @C_O_id > 0 )
BEGIN
UPDATE Customer_D_Info
SET type = @BType
END
END
FETCH NEXT FROM Order_CUSTOMER_Cursor INTO @MANAGE_O_id, @CUSTOMER_O_id
END;
CLOSE MANAGE_O_D_Cursor
DEALLOCATE MANAGE_O_D_Cursor
How about if my cursor like this. I think I got Update part. If I am updating my value from Variable that I am populating in Cursor?
Any Advice...
June 11, 2015 at 11:01 am
Some basic things here, probably we're missing something.
Your declaration of the cursor uses 1 variable and you only use the column on that variable within the cursor.
Basically, your code should do the same as the following.
UPDATE Customer_D_Info
set type = 'Active'
WHERE @C_O_id > 0
AND NOT EXISTS( SELECT 1
From M_Order M
INNER JOIN C_Order_D C ON C.MO = M.ID
WHERE M_Order_D = @MANAGE_O_id);
--Or basically
IF @C_O_id > 0
AND NOT EXISTS( SELECT 1
From M_Order M
INNER JOIN C_Order_D C ON C.MO = M.ID
WHERE M_Order_D = @MANAGE_O_id)
UPDATE Customer_D_Info
set type = 'Active';
By the way, that makes no sense because it will update the whole table or not. There's nothing that you can relate to the customer.
If you're using a variable as a column in your cursor, don't use it there, just keep the variable intact. In this case, you're assigning it to a variable with a different name (from MANAGE to CUSTOMER).
You shouldn't be using NOLOCK hints either unless that you want unreliable data.
Please share more details on what you're doing because this simply looks as a whole pile of mistakes.
June 11, 2015 at 11:07 am
It doesn't change two rather important things:
1.) The @CUSTOMER_O_id is still always going to be equal to @MANAGER_O_id, regardless of which record of the query you are processing.
2.) You still UPDATE every record in the CUSTOMER_D_Info table on each pass through the cursor.
Thus I have to ask, what, exactly, and in excruciating detail, is the objective? Also, why are you selecting a variable value that will be a constant into this? I wonder because maybe you think that if you SELECT DISTINCT MANAGER_O_id, @MANAGER_O_id FROM #tempM, that you'll be populating that variable ? That would NOT be the case. I can't
tell because you haven't gotten into that level of detail yet. Let us know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply