Need help on "WHILE @@ROWCOUNT > 0"

[2122002]

Dear Experts,

Below store procedure (code extracted) keep running and takes more than 2 hours, and never terminated: Can some please have a look and guide me through whatever I am doing wrong.

I have approx 750,000 records on tables "tblMergedPatients" and approx 68,000 in table "tblPatients".

---------------------------------
Declare @AddPat int

-- Create Map of all merged patients

SELECT M1.PATNT_REFNO, M1.PREV_PATNT_REFNO
INTO #MergeMap
FROM tblMergedPatients M1
INNER JOIN tblPatients P1
ON M1.PREV_PATNT_REFNO = P1.PATNT_REFNO
WHERE M1.PATNT_REFNO <> M1.PREV_PATNT_REFNO
AND P1.MERGE_MINOR_FLAG = 'Y'
GROUP BY M1.PATNT_REFNO, M1.PREV_PATNT_REFNO


WHILE @@ROWCOUNT > 0
BEGIN

UPDATE M1
SET PATNT_REFNO = M2.PATNT_REFNO
FROM #MergeMap M1, tblMergedPatients M2, tblPatients P1
WHERE M1.PATNT_REFNO = m2.PREV_PATNT_REFNO
AND M1.PATNT_REFNO = P1.PATNT_REFNO
AND P1.MERGE_MINOR_FLAG = 'Y'

END

---------------------------------------

Thank you.

Dan

 

[memdiscount]

Hi;

Why the While Loop is used here in this UPDATE statement. This should update the selected resultset in one batch without using WHILE LOOP.

1- Remove WHILE LOOP and then run.

2- If you want to update records if the first select statement returns some records then change WHILE to IF.

I hope it will resolve the problem.

Thanks

 

[2122002]

The reason I've used WHILE LOOP is to check and test if any row(s) is affected in the tenporary table (#MergeMap) in the first part of my code.

I have change "WHILE@@ROWCOUNT > 0" to "IF @@ROWCOUNT > 0" and I can confirmed that it is working.

Many thanks.


Dan

 

[memdiscount]

Hi ;

Which means the post helped you and your problem is solved? Very good, I am happy to know that.

Thanks

 

[SQLSister]

Let me explain why that change worked. The while loop actually ran the update statement repeatedly becasue there was no way for @@rowcount to ever get to zero. Never use a while loop without a way to change the variable after each time the code is executed. However a while loop was never a good idea even if you had decremeted the number of records. In that case you would have run the update the same number of times as there were recrods in the table when it only needed to be run once. The if statement only runs it once and is a better choice.

You need to stop thinking in terms of using loops for inserts, updates and deletes. SQL server is optimized for set-based operations. Loops and cursors are alawys far slower than a set-based query.

Also please get away from using that old syntax and use the inner join, left join, etc syntax. It is easier to read and the old sysntax does not give correct results when you want to use a left join so it is better to code using the syntax that will always work. Also separating the joins from the real where clause is helpful to the person trying to maintain the code.



"NOTHING is more important in a database than integrity." ESquared

 

[2122002]

Thanks for participating in this Forum! it really helps.

I have another question: While checking the rest part of my store procedures, again, I have used "WHILE" in below codes:

=====================================================
- * Shared Contacts *

-- Create ShareGroup which groups any contacts for the
--- same referral that run during that the same time.

Declare @Group int
Declare @Count int
Declare @Run int

SELECT -1 ShareGroup, -1 ShareCount, 0 FirstCount, SCHDL_REFNO, START_DTTM, END_DTTM, PATNT_REFNO, REFRL_REFNO, PROCA_REFNO
INTO #ShareGroup
FROM tblSchedules
WHERE START_DTTM is not null
AND REFRL_REFNO is not null
AND SCTYP_REFNO = 1468
AND (ARRIVED_DTTM is not null
OR SCOCM_REFNO = 1457)
ORDER BY START_DTTM

SELECT @Group = 0

WHILE (SELECT MIN(ShareGroup) FROM #ShareGroup) = -1
BEGIN

SELECT @Count = 1
SELECT @Run = 0

UPDATE #ShareGroup
SET ShareGroup = @Group, ShareCount = @Run
FROM #ShareGroup INNER JOIN (SELECT REFRL_REFNO, Min(START_DTTM) MinStart
FROM #ShareGroup
WHERE ShareGroup = -1
GROUP BY REFRL_REFNO) P1
ON #ShareGroup.REFRL_REFNO = P1.REFRL_REFNO
AND START_DTTM = MinStart

WHILE @Count > 0

BEGIN

SELECT @Run = @Run + 1

UPDATE #ShareGroup
SET ShareGroup = @Group, ShareCount = @Run
FROM #ShareGroup INNER JOIN (SELECT REFRL_REFNO, Max(END_DTTM) MaxEnd
FROM #ShareGroup
WHERE ShareGroup = @Group
GROUP BY REFRL_REFNO) P1
ON #ShareGroup.REFRL_REFNO = P1.REFRL_REFNO
AND START_DTTM < MaxEnd
WHERE ShareGroup = -1

SELECT @Count = @@ROWCOUNT

END

SELECT @Group = @Group + 1

END

===========================================

If I change the 'WHILE' part of it, for example to below codes, using 'IF'statement, will it work?? I have not tested it with my datbase but I need your professional expetise:


IF(SELECT MIN(ShareGroup) FROM #ShareGroup) = -1

SELECT @Count = 1
SELECT @Run = 0

UPDATE #ShareGroup
SET ShareGroup = @Group, ShareCount = @Run
FROM #ShareGroup INNER JOIN (SELECT REFRL_REFNO, Min(START_DTTM) MinStart
FROM #ShareGroup
WHERE ShareGroup = -1
GROUP BY REFRL_REFNO) P1
ON #ShareGroup.REFRL_REFNO = P1.REFRL_REFNO
AND START_DTTM = MinStart

IF @Count > 0

SELECT @Run = @Run + 1

UPDATE #ShareGroup
SET ShareGroup = @Group, ShareCount = @Run
FROM #ShareGroup INNER JOIN (SELECT REFRL_REFNO, Max(END_DTTM) MaxEnd
FROM #ShareGroup
WHERE ShareGroup = @Group
GROUP BY REFRL_REFNO) P1
ON #ShareGroup.REFRL_REFNO = P1.REFRL_REFNO
AND START_DTTM < MaxEnd
WHERE ShareGroup = -1

SELECT @Count = @@ROWCOUNT
SELECT @Group = @Group + 1
=================================================




Dan

 

[SQLSister]

what are you trying to achieve? Give us a sample of the data before the update statment and what you would like it to look like after the update statement.

"NOTHING is more important in a database than integrity." ESquared