Performance issue on While Loop

Question

Performance issue on While Loop

jscontreras

SSC-Addicted

Points: 430
More actions
June 2, 2003 at 11:24 am

#81641

Hi All,
I'm running into a performance issue with the following sp. It works fine but it's real slow. I added a cluster index to no avail. Any suggestions on how to skin this cat. This was originally using cursors which was taking 20+ hours. I'm using SQL Server 7. Thanks
CREATE PROCEDURE spTestWhileLoop
AS
SET NOCOUNT ON
DECLARE @acctNoID AS INT, @index_no AS VARCHAR(10), @index_no_record_no AS VARCHAR(11),@SQL AS VARCHAR(100),@strSQL AS VARCHAR(100)
SET @sql = 'SELECT ListAllLocs_V.AcctNoID AS ListAcctNoID,
RR_APPROVAL.* INTO TBLFMCRiskReporter
FROM ListAllLocs_V RIGHT OUTER JOIN
RR_APPROVAL ON
ListAllLocs_V.INDEX_NO = RR_APPROVAL.INDEX_NO AND
ListAllLocs_V.INDEX_RECORD_NO = RR_APPROVAL.INDEX_RECORD_NO
AND
ListAllLocs_V.AcctNoID = RR_APPROVAL.AcctNoID
WHERE (ListAllLocs_V.AcctNoID IS NULL)'
EXECUTE(@SQL);
SET @index_no_record_no =''
WHILE @index_no_record_no IS NOT NULL
BEGIN
SELECT @index_no_record_no = MIN(INDEX_NO_RECORD_NO),@acctNoID = MIN(AcctNoID), @index_no = MIN(INDEX_NO)
FROM TBLFMCRiskReporter WHERE INDEX_NO_RECORD_NO > @index_no_record_no ;
IF @index_no_record_no IS NOT NULL
BEGIN
--EXEC spFMRiskCompanionRRApprovalUpdate @acctNoID,@index_no,@index_record_no
SET @strSQL = 'Update RR_APPROVAL SET EXIST_FLG = 0 WHERE INDEX_NO_RECORD_NO=' + CHAR(39) + @index_no_record_no + CHAR(39) + ' AND INDEX_NO = ' + CHAR(39) + @index_no + CHAR(39) + ';'
PRINT @strSQL
END
END
JMC

JMC

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

tkbr0wn Ten Centuries Points: 1092 More actions · Answer 1

Your probably going to have to give some sample data for people to understand what you are looking for but I typed this up real quick. It may give you some other ideas but it may be totaly off.

UPDATE rr

SET EXIST_FLAG = 0

FROM RR_Approval AS rr

JOIN(

SELECTRR_APPROVAL.AcctNoID,

MIN(RR_APPROVAL.INDEX_NO_RECORD_NO) AS INDEX_NO_RECORD_NO,

MIN(RR_APPROVAL.INDEX_NO) AS INDEX_NO

FROMListAllLocs_V

RIGHT OUTER JOINRR_APPROVAL

ON ListAllLocs_V.INDEX_NO = RR_APPROVAL.INDEX_NO

AND ListAllLocs_V.INDEX_RECORD_NO = RR_APPROVAL.INDEX_RECORD_NO

AND ListAllLocs_V.AcctNoID = RR_APPROVAL.AcctNoID

WHERE (ListAllLocs_V.AcctNoID IS NULL)

GROUP BY RR_APPROVAL.AcctNoID

)ASdt

ONdt.AcctNoID = rr.AcctNoID

ANDrr.INDEX_NO_RECORD_NO = dt.INDEX_NO_RECORD_NO

ANDrr.INDEX_NO = dt.INDEX_NO

ANDdt.INDEX_NO_RECORD_NO IS NOT NULL

jscontreras SSC-Addicted Points: 430 More actions · Answer 2

Thanks for your insight. Sorry about being vague. Basically the sp gets ids required to update another table while processing through the entire recordset. I noticed something weird happened with the DTS that runs this cursor sp. Around 5/15 this job was running for about 30 minutes, however on 5/17 the job ran for almost 20 hours. I've checked indexing done explain plans and everything appears to be normal. Question what do I need to look at if it's not the sp or SQL? Where do I begin to investigate this weirdness?

Thanks

JMC

jscontreras SSC-Addicted Points: 430 More actions · Answer 3

tkbr0wn,

after getting nowhere I used your SQL and it took 24 seconds to run. I replaced the cursor in the stored proc and used your method. Will run as part of a DTS package to continue additional testing.

Thanks

JMC