Performance of a cursor - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Performance of a cursor

Author

Topic

bheemsen
Starting Member

5 Posts

Posted - 2002-08-02 : 13:24:56

Hi,

I have a following cursor coded in a stored procedure. This part of the procedure is taking long hours to complete.

DECLARE FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSOR CURSOR LOCAL FAST_FORWARD
FOR
SELECT wire_cntr_name, living_unit_id, wtn, id
FROM ff_lu_wtn
Where wire_cntr_id = @v_wire_center and id NOT IN
(SELECT id
FROM ff_lu_wtn a left outer join stage_lu_Address b ON a.living_unit_id=b.living_unit_id and a.wire_cntr_id=b.wc_code
WHERE a.wire_cntr_id = @v_wire_center and a.living_unit_id=b.living_unit_id)
FOR READ ONLY

Table ff_lu_wtn has around 100,000 rows and table stage_lu_address has around 90,000 rows. The number of rows keeps changing every time this procedure runs.

I tried to improve the performance by creating the following two indexes, but there was no improvement.

CREATE NONCLUSTERED INDEX [FF_LU_WTN0] ON [dbo].[FF_LU_WTN]([WIRE_CNTR_ID])

CREATE NONCLUSTERED INDEX [STAGE_LU_ADDRESS1] ON [dbo].[STAGE_LU_ADDRESS]([LIVING_UNIT_ID], [WC_CODE])

I appreciate if someone can help me find a way to improve the performance. It can be either by:

1. splitting the above cursor into two cursors.
2. getting rid of left outer join and making a select statement within the cursor.
3. any other way.

I need this urgently, please help.

-Bheemsen

joshb
Yak Posting Veteran

52 Posts

Posted - 2002-08-02 : 13:27:04

What does the cursor do? Are you sure you must use a cursor?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-02 : 13:36:21

I think what Josh means is, post the code of your stored proc so that we can show how to write it without the cursor at all....

Jay White
{0}

bheemsen
Starting Member

5 Posts

Posted - 2002-08-02 : 15:59:49

quote:

I think what Josh means is, post the code of your stored proc so that we can show how to write it without the cursor at all....

Jay White
{0}

Hi Jay,

Actually it is a big stored procedure, so I am pasting here below, only the related portion of it.

DECLARE FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSOR CURSOR LOCAL FAST_FORWARD
FOR
SELECT wire_cntr_name, living_unit_id, wtn, id
FROM ff_lu_wtn
Where wire_cntr_id =@v_wire_center and id NOT IN
( SELECT id
FROM ff_lu_wtn a left outer join stage_lu_Address b ON a.living_unit_id=b.living_unit_id and a.wire_cntr_id=b.wc_code
WHERE a.wire_cntr_id=@v_wire_center and a.living_unit_id=b.living_unit_id
)
FOR READ ONLY

---------------WHILE NOT EOF #4 ( FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSOR) --------------------------Release5 Can Gencer

OPEN FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSOR
FETCH NEXT FROM FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSOR INTO @v_ff_lu_wire_cntr_name, @v_ff_lu_living_unit_id, @v_ff_lu_WTN, @i_ff_lu_id --Release5 Can Gencer
/*---------------CHECK if ERROR ( After each = FETCH, Update, Insert..)------------------------------*/
SET @procError = @@error
if @procError <> 0
BEGIN
ROLLBACK TRAN
SET @errMessage = 'Fetch Failed from the FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSOR Cursor for the WireCenter ' + @v_wire_center
SET @errDesc = @errDesc + @errMessage + space(1) + '(' + cast(@procError as char(5)) + ')'
EXEC spRegister_Exception @appID, @errCode, @errDesc, @procName
-- #debug
if @DebugVal = @DebugFlag -- debug mode is on
exec spCaptureTrace @procName, @procError, @errDesc
-- #end of debug
return @failed

END
-- #debug
if @DebugVal = @DebugFlag -- debug mode is on
print 'Fetch the first error record from FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSORcursor -- ' + convert(char(24), getdate(), 13)
-- #end of debug

--print 'Find the Living_Unit_Ids that does not exist' + convert(char(24), getdate(), 13)

/*-------------------------------------------------------------------------------------------------------------*/
WHILE (@@FETCH_STATUS <> -1)
BEGIN
-------------------Set LOAD_ERROR_CODE as 01704 = Living_Unit_id in STG_LU_WTN does not EXIST in STAGE_LU_ADDRESS table
SELECT @c_LOAD_ERROR_CODE = '01704'

/* Compare Current FF_LU_WTN Record with the RECORDS in LOAD_ERROR_LU_WTN
If the SAME Error Reported Before, Update LOAD_DATE field in LOAD_ERROR_LU_WTN */
SELECT @i_CountExistingRecord=Count(*)
FROM LOAD_ERROR_LU_WTN
WHERE wire_cntr_id=@v_wire_center AND
( (living_unit_id = @v_ff_lu_living_unit_id) OR ( ( living_Unit_Id IS NULL) AND ( @v_ff_lu_living_unit_id IS NULL) ) ) AND
( (WTN = @v_ff_lu_WTN) OR ( ( WTN IS NULL) AND ( @v_ff_lu_WTN IS NULL) ) )

if ( @i_CountExistingRecord > 0) --Update Modified Date ONLY ---Don't Insert new 01704
BEGIN

UPDATE LOAD_ERROR_LU_WTN
SET modified_date = getdate()
WHERE wire_cntr_id=@v_wire_center AND
( (living_unit_id = @v_ff_lu_living_unit_id) OR ( ( living_Unit_Id IS NULL) AND ( @v_ff_lu_living_unit_id IS NULL) ) ) AND
( (WTN = @v_ff_lu_WTN) OR ( ( WTN IS NULL) AND ( @v_ff_lu_WTN IS NULL) ) )
END
ELSE
BEGIN

INSERT INTO LOAD_ERROR_LU_WTN
( wire_cntr_id, living_unit_id, wtn, load_date, load_error_code, created_date, modified_Date ) --Release5 Can Gencer
values
( @v_wire_center, @v_ff_lu_living_unit_id, @v_ff_lu_WTN, getdate(), @c_LOAD_ERROR_CODE, getdate(), NULL ) --Release5 Can Gencer

/*---------------CHECK if ERROR (After each = FETCH, Update, Insert..)------------------------------*/
SET @procError = @@error

if @procError <> 0
BEGIN
ROLLBACK TRAN
SET @errMessage = 'Failed to insert the record into the LOAD_ERROR_LU_WTN Table for the WireCenter ' + @v_wire_center
SET @errDesc = @errDesc + @errMessage + space(1) + '(' + cast(@procError as char(5)) + ')'
EXEC spRegister_Exception @appID, @errCode, @errDesc, @procName
-- #debug
if @DebugVal = @DebugFlag -- debug mode is on
exec spCaptureTrace @procName, @procError, @errDesc
-- #end of debug
return @failed
END
-- #debug
if @DebugVal = @DebugFlag -- debug mode is on
print 'Inserted a new record in LOAD_ERROR_LU_WTN -- ' + convert(char(24), getdate(), 13)
-- #end of debug

/*-------------------------------------------------------------------------------------------------------------*/
------Concatenate ALL fields of the LEAD_SEQ_LU_ADDRESS record as @problem_detail TO insert INTO BUCKET_ITEM table--------
SET @v_problem_details = @v_wire_center + '|'
if @v_ff_lu_wire_cntr_name is NULL
SET @v_problem_details = @v_problem_details + '|'
else
SET @v_problem_details = @v_problem_details + @v_ff_lu_wire_cntr_name + '|'
if @v_ff_lu_living_unit_id is NULL
SET @v_problem_details = @v_problem_details + '|'
else
SET @v_problem_details = @v_problem_details + @v_ff_lu_living_unit_id + '|'
if @v_ff_lu_WTN is NULL
SET @v_problem_details = @v_problem_details + '|'
else
SET @v_problem_details = @v_problem_details + @v_ff_lu_WTN --Release5 Can Gencer

------Get Initiator Comment (ERROR DESCRIPTION) From ERROR_LIST Table---------------------
SELECT @v_initiator_comment = description_short
FROM ERROR_LIST
WHERE error_code = @c_LOAD_ERROR_CODE

if ( @c_LOAD_ERROR_CODE != '01704' )
BEGIN
------Get key_back_id and key_back_server_name from LOAD_ERROR_LU_WTN--------------
SELECT @i_key_back_id = id, @v_key_back_server_name = @@servername
FROM LOAD_ERROR_LU_WTN
WHERE wire_cntr_id=@v_wire_center AND
( (living_unit_id = @v_ff_lu_living_unit_id) OR ( (living_unit_id IS NULL) AND (@v_ff_lu_living_unit_id IS NULL) ) ) AND
( (WTN = @v_ff_lu_WTN) OR ( ( WTN IS NULL) AND ( @v_ff_lu_WTN IS NULL) ) ) ---Release5 Can Gencer

INSERT INTO BUCKET_ITEM
(server_name, bucket_type_code, wc_code, key_back_id, key_back_server_name, key_back_table_name, status, last_activity_datetime, problem_details, initiator_comment)
values
(@@servername, @c_LOAD_ERROR_CODE, @v_wire_center, @i_key_back_id, @v_key_back_server_name, 'LOAD_ERROR_LU_WTN', 'OPEN', getdate(), @v_problem_details, @v_initiator_comment)
END
ELSE ----@c_LOAD_ERROR_CODE = '01704' INFORMATIONAL BUCKET ITEM--NO key back id ---Release5 Can Gencer
BEGIN
INSERT INTO BUCKET_ITEM
(server_name, bucket_type_code, wc_code, key_back_id, key_back_server_name, key_back_table_name, status, last_activity_datetime, problem_details, initiator_comment)
values
(@@servername, @c_LOAD_ERROR_CODE, @v_wire_center, NULL, @v_key_back_server_name, NULL, 'OPEN', getdate(), @v_problem_details, @v_initiator_comment)

END

/*---------------CHECK if ERROR (After each = FETCH, Update, Insert..)------------------------------*/
SET @procError = @@error

if @procError <> 0
BEGIN
ROLLBACK TRAN
SET @errMessage = 'Failed to insert into the BUCKET_ITEM Table for the WireCenter ' + @v_wire_center
SET @errDesc = @errDesc + @errMessage + space(1) + '(' + cast(@procError as char(5)) + ')'
EXEC spRegister_Exception @appID, @errCode, @errDesc, @procName
-- #debug
if @DebugVal = @DebugFlag -- debug mode is on
exec spCaptureTrace @procName, @procError, @errDesc
-- #end of debug
return @failed
END
-- #debug
if @DebugVal = @DebugFlag -- debug mode is on
print 'Inserted a new bucket item into BUCKET_ITEM table -- ' + convert(char(24), getdate(), 13)
-- #end of debug
END -----else

FETCH NEXT FROM FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSOR INTO @v_ff_lu_wire_cntr_name, @v_ff_lu_living_unit_id, @v_ff_lu_WTN, @i_ff_lu_id --Release5 Can Gencer

/*---------------CHECK if ERROR ( After each = FETCH, Update, Insert..)------------------------------*/
SET @procError = @@error

if @procError <> 0
BEGIN
ROLLBACK TRAN
SET @errMessage = 'Fetch Failed from the FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSORCursor for the WireCenter ' + @v_wire_center
SET @errDesc = @errDesc + @errMessage + space(1) + '(' + cast(@procError as char(5)) + ')'
EXEC spRegister_Exception @appID, @errCode, @errDesc, @procName
-- #debug
if @DebugVal = @DebugFlag -- debug mode is on
exec spCaptureTrace @procName, @procError, @errDesc
-- #end of debug
return @failed
END
-- #debug
if @DebugVal = @DebugFlag -- debug mode is on
print 'Fetch the next error record from FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSOR cursor -- ' + convert(char(24), getdate(), 13)
-- #end of debug

END
---------------END WHILE #4----------------------Release5 Can Gencer

--print 'End finding the Living_Unit_Ids that does not exist ' + convert(char(24), getdate(), 13)

------------------------------------------END----- Living_unit_ids that does not exist in STAGE_LU_ADDRESS----------------------------------------

/* Get # of AllRecords and Error Records FROM BATCH_LOAD_CONTROL for LU_ADDRESS --for the Current Wire_Center*/
SELECT DISTINCT @i_CountErrorRecords_FF_LU_WTN = num_records_error
FROM batch_load_control
WHERE batch_status = 'SEQUENTIAL' AND
orig_file_name LIKE '%_wtn' AND
wire_Center = @v_wire_center AND
datediff(hour, last_activity_datetime, getdate()) <= @i_Load_Cycle_as_hours_ApplicationVariable

/* Find Number of Loaded records after DUPLICATES are deleted and non matching LIVING_UNIT_IDs are deleted*/
SELECT @i_CountLoadedRecords_FF_LU_WTN = count(*)
FROM ff_lu_WTN
WHERE wire_cntr_id = @v_wire_center

print 'Number of Loaded records after DUPLICATES are deleted and non matching LIVING_UNIT_IDs are deleted : ' + convert(varchar(12), @i_CountLoadedRecords_FF_LU_WTN)
-------Insert INTO STG_LU_WTN USING FF_LU_WTN and STAGE_LU_ADDRESS
print 'Insert INTO STG_LU_WTN USING FF_LU_WTN and STAGE_LU_ADDRESS ' + convert(char(24), getdate(), 13)

INSERT STG_LU_WTN
SELECT distinct wire_cntr_id as wc_code, a.living_unit_id, a.wtn
from ff_lu_wtn a --, stage_lu_address b ----Release5 01704 informational insert EVERYTHING from FF_LU_WTN
where a.wire_cntr_id=@v_wire_center -- and b.wc_code=@v_wire_center and a.living_unit_id=b.living_unit_id

/*---------------CHECK if ERROR ( After each = FETCH, Update, Insert..)------------------------------*/
SET @procError = @@error
/* jk5139 -- instead of removing duplicates in SEQ table, stop inserting duplicates to staging table */
-- #debug
if @procError = 2627
begin
set @errDesc = 'Duplicated rows in ff_lu_wtn.'
--print 'Duplicated rows in ff_lu_wtn found'
exec spCaptureTrace @procName, @procError, @errDesc
end
-- #end of debug

if @procError <> 0 and @procError != 2627
BEGIN
ROLLBACK TRAN
SET @errMessage = 'Failed to insert records into the STG_LU_WTN table for the WireCenter ' + @v_wire_center
SET @errDesc = @errDesc + @errMessage + space(1) + '(' + cast(@procError as char(5)) + ')'
EXEC spRegister_Exception @appID, @errCode, @errDesc, @procName
-- #debug
if @DebugVal = @DebugFlag -- debug mode is on
exec spCaptureTrace @procName, @procError, @errDesc
-- #end of debug
return @failed
END
-- #debug
if @DebugVal = @DebugFlag -- debug mode is on
print 'Inserted records into the STG_LU_WTN table -- ' + convert(char(24), getdate(), 13)
-- #end of debug

--print 'End Inserting INTO STG_LU_WTN USING FF_LU_WTN and STAGE_LU_ADDRESS ' + convert(char(24), getdate(), 13)

/**********************************************************************************************************************************************************/

------- Close and deallocate all outer join cursors ----Release5 Can Gencer
CLOSE FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSOR
DEALLOCATE FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSOR

---------------END WHILE #1------------------

-- #debug
if @DebugVal = @DebugFlag -- debug mode is on
print 'End of sp_dataload_seq2stage_test -- ' + convert(char(24), getdate(), 13)
-- #end of debug

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-08-02 : 16:28:55

ick...

This should be a little faster. I've personally always had better performace with NOT EXISTS over NOT IN. I would also recommend removing the left outer join. It's not filtering any records so it doesn't really have a point as far as I can see.

 
DECLARE FF_LU_WTN_STAGE_LU_ADDRESS_DIFF_CURSOR CURSOR LOCAL FAST_FORWARD FOR 
	SELECT 	wire_cntr_name, 
		living_unit_id, 
		wtn, 
		[id] 
	FROM 	ff_lu_wtn AS FLW
	WHERE 	wire_cntr_id = @v_wire_center and 
		NOT EXISTS (
				SELECT 	[id]
				FROM 	ff_lu_wtn a left outer join 
					stage_lu_Address b ON 
						a.living_unit_id=b.living_unit_id and 
						a.wire_cntr_id=b.wc_code 
				WHERE 	a.wire_cntr_id = @v_wire_center and 
					a.living_unit_id=b.living_unit_id AND
					A.[id] = FLW.[id]
			)

FOR READ ONLY

Jeff Banschbach
Consultant, MCDBA

bheemsen
Starting Member

5 Posts

Posted - 2002-08-06 : 14:21:41

Hi,

I tried with the following modified version of the query.

SELECT wire_cntr_name, living_unit_id, wtn, id
FROM ff_lu_wtn as flw
Where wire_cntr_id = @v_wire_center and NOT exists
(SELECT *
FROM ff_lu_wtn a inner join stage_lu_Address b
ON a.living_unit_id=b.living_unit_id and a.wire_cntr_id=b.wc_code
WHERE a.wire_cntr_id = @v_wire_center and a.id = flw.id)

But there was no improvement.

If needed, I can split the query into two queries, since it is cursor. So that makes a new cursor inside the first cursor. I don't know whether it would improve the performance.

Please provide your input and thoughts.

Thanx..
-Bheemsen

r937
Posting Yak Master

112 Posts

Posted - 2002-08-06 : 15:32:37

i think i have this figured out...

let's look at the original query that you posted --

SELECT wire_cntr_name, living_unit_id, wtn, id 
  FROM ff_lu_wtn 
 Where wire_cntr_id = @v_wire_center 
   and id NOT IN 
       ( SELECT id 
           FROM ff_lu_wtn a 
left outer join stage_lu_Address b 
             ON a.living_unit_id=b.living_unit_id 
            and a.wire_cntr_id=b.wc_code 
          WHERE a.wire_cntr_id = @v_wire_center 
            and a.living_unit_id=b.living_unit_id )

in a LEFT OUTER JOIN, all rows of the left table are normally returned, whether or not they have a matching row from the right table

however, this subquery is slightly tricky -- it repeats one of the ON conditions as a WHERE condition

so for those rows of ff_lu_wtn which have no matching row of stage_lu_Address, the stage_lu_Address columns are null, and the WHERE clause will compare a.living_unit_id with a null, and return false

this is too sneaky

in effect, what you are trying to do in the subquery is find all the rows of ff_lu_wtn which do have a matching row in stage_lu_Address

and therefore, because of the NOT IN, the outer query wants only those rows of ff_lu_wtn which do not have a matching row in stage_lu_Address

again, this is too sneaky

try this --

SELECT wire_cntr_name, living_unit_id, wtn, id 
  FROM ff_lu_wtn a
 Where wire_cntr_id = @v_wire_center 
   and NOT EXISTS 
       ( SELECT * 
           FROM stage_lu_Address 
          WHERE living_unit_id = a.living_unit_id 
            and wc_code = @v_wire_center )

or better yet, try this --

SELECT wire_cntr_name, living_unit_id, wtn, id 
  FROM ff_lu_wtn a
LEFT OUTER
  JOIN stage_lu_Address b 
    ON a.living_unit_id = b.living_unit_id 
   and a.wire_cntr_id   = b.wc_code 
 WHERE a.wire_cntr_id = @v_wire_center 
   and                    b.living_unit_id IS NULL

caution: untested

rudy
http://rudy.ca/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources