TSQL infinite loop in cursor when joining temp table only

Question

I have a SP that updates prices in a table using a CURSOR and FETCH NEXT.

The cursor's query is a simple "select', and instead of a WHERE - I'm joining a function who brings all the related User IDs.

Now, due to the fact the my SP updates a lot of different tables (attached is only part of it), and in every update I use the JOIN of the users function - I wanted to save some time, and bring the Users list only once, at the beginning of the SP.

If I join my temp table (@t, which holds the User IDs) I get an infinite loop. At the end the prices will be infinite.

BUT - If I join the function itself, instead of the temp table that should have exactly the same values in it, everything is fine.

I commented the problematic JOIN.

-- This is my main object, who "holds" a lot of users
DECLARE @DistID INT = 123

DECLARE @t TABLE
(MainDistID int,
 DistID int,
 UserID int)

INSERT INTO @t

SELECT MainDistID,
       DistID,
       UserID
FROM [MyScheme].[FnGetAllDistAndUsers] (@DistID)

DECLARE @Bid INT -- Will contain the ID we need to update

DECLARE c CURSOR LOCAL FOR 
SELECT ID
FROM BillingDetails AS bd
-- BOTH JOINS SHOULD BE THE SAME:
--JOIN @t AS GUsers -- Infinite loop...
--  ON bd.UserID = GUsers.UserID
JOIN [MyScheme].[FnGetAllDistAndUsers] (@DistID) AS GUsers -- NO infinite loop
    ON bd.UserID = GUsers.UserID

OPEN c

FETCH NEXT FROM c
INTO @Bid

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE MyTable
    SET Price = Price * 2
    WHERE MyTableID = @Bid

    FETCH NEXT FROM c
    INTO @Bid
END

Thanks!

Mitch, I use the cursor to prevent locks (updating 100,000 rows). It is the most busy table on my DB. I don't use cursors for all the other updates in my SP. So I do need to use it. Thanks. — user3115892, Dec 20 '13 at 00:51
Have you tried running it with a PRINT in the loop to get some idea of where it goes wrong? You could toss in an extra WHERE condition, e.g. `UserId % 100 = 42` to reduce the number of rows for analysis. — HABO, Dec 20 '13 at 00:52
If you meant "I'm doing it in a batch to prevent Blocking" then see my first comment. — Mitch Wheat, Dec 20 '13 at 01:23
Mitch, I ran 2 tests: 1. Update all 100,000 rows in one batch/ action using a JOIN of the UserID. Completed in 35 seconds, but locked the table and I wasn't able to select/ insert from/ to it. 2. Update one row at a time using the cursor and fetch next. Completed in 5 minutes (yes), but didn't lock the table. How can I do this update without locking the table (and without using the cursor)? — user3115892, Dec 20 '13 at 09:15

score 0 · Answer 1 · answered Aug 25 '15 at 03:14

I think Mitch is right you shouldn't have to use a cursor. When doing updates it is fastest if you join on the primary key, so you could do something like this.

DECLARE @t TABLE (Bid int)

INSERT INTO @t
SELECT bd.ID
FROM [MyScheme].[FnGetAllDistAndUsers] u (@DistID)
INNER JOIN BillingDetails bd
    ON u.UserID = bd.UserID

UPDATE mt
SET Price = Price * 2
FROM MyTable mt
INNER JOIN @t t
    ON mt.MyTableID = t.Bid

TSQL infinite loop in cursor when joining temp table only

1 Answers1