Need help with slowing cursor. 1

[desperateUser]

Hello, long time no talk to. Y'all have helped me out so much in the past that I haven't had any problems lately...well, til this one.

This SQL programming was written by someone else. I have the ability to change it - I just don't know what to do with it. This piece of a stored procedure has slowed to a crawl (90 minutes + to run all the way. When I first started working here it took maybe five minutes.) It is updating the description field of a list of accounts.

/************************************************************************************************
Function		: UPDATE - update any/all rows in the permanent table	
************************************************************************************************/
update_record:

-- declare and open the temp table cursor 
declare temp_tbl_crsr cursor scroll
for
select t.fund_id, t.account_id, t.center_id, t.center_desc 
from dbo.ibiscen i INNER JOIN dbo.temp_ibiscen t
on     i.fund_id = t.fund_id and     i.account_id = t.account_id and    i.center_id = t.center_id and 
    i.center_desc <> t.center_desc

-- declare and open the perm table cursor  FOR UPDATE
declare perm_tbl_crsr cursor dynamic
for
select fund_id, account_id, center_id, center_desc 
from dbo.ibiscen
for UPDATE of account_id, center_id, center_desc

-- open the cursors 
open perm_tbl_crsr
open temp_tbl_crsr

-- get the first row from the temp_tbl cursor 
fetch first from temp_tbl_crsr into @fund, @account, @center_id, @center_desc

-- loop through the rows in the cursor */    
while @@fetch_status = 0
begin

	-- get the first row from the prem_tbl cursor 
	fetch first from perm_tbl_crsr into @fund_p, @account_p, @center_id_p, @center_desc_p
		
	-- loop through the rows in the cursor     
	while @@fetch_status = 0
	begin			if @fund + @account + @center_id = @fund_p + @account_p + @center_id_p
		begin

			if @center_desc <> @center_desc_p 
			begin
			exec usp_ins_code_table_modifications "ibiscen","UPDATE","",
				"center_desc", @center_desc_p, @center_desc
			end

			update ibiscen
			set	au = Left(RTrim(@center_id),3),
			 	center_desc = @center_desc,
				last_modified =CONVERT(char(11),getdate())
				where	fund_id + account_id + center_id = @fund + @account + @center_id
		end
		-- get the first row from prem_aufund cursor 
		fetch next from perm_tbl_crsr into @fund_p, @account_p, @center_id_p, @center_desc_p
	end
	-- get the next row from temp_tbl_crsr 
	fetch next from temp_tbl_crsr INTO @fund, @account, @center_id, @center_desc	
end


-- close cursors 
close temp_tbl_crsr
deallocate temp_tbl_crsr

close perm_tbl_crsr
deallocate perm_tbl_crsr

RETURN

I would like to be pointed in the right direction as far as where to go from here. TIA.

 

[SQLSister]

The right way to go from here is to get rid of both cursors altogether. Replace the exec of the stored proc with set-based code. Do the update with a join rather than using variables.

You should not be using a cursor for inserts and updates because they are slow. The more records, the slower they are which is probaly why this one has gotten slower over time.

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

 

[desperateUser]

So I can do an insert like this?

INSERT INTO ibiscen
(ic, fund_id, account_id, center_id, center_desc, last_modified, au)

SELECT i.fund_id, i.account_id, i.center_id, t.center_desc, get(date), LEFT(i.center_id, 3) AS AU)

FROM ibiscen i LEFT JOIN temp_ibiscen t ON 
i.fund_id = t.fund_id AND 
i.account_id = t.account.id AND
i.center_id = t.center_id

My only concern is the other two pieces, where new codes are added or old codes deleted...this is all wrapped up in one stored procedure. Then there's another sp to document all the changes made. I could just drop the table each time but then there's no history of when the change occurred...the get(date) field would be useless. Of course, I don't think we use the functionality of knowing the last modified date of the information anyway.

 

[SQLSister]

yes but you would want to add a where clause like
where t.fund_id is null
that is what tells you which records are not inthe the table you are inserting into.

As fara as the other I'd have to see the code to advise you ona set-based way to do it. Reuse of an existing proc that works one record at a time for a large data set by using a cursor is almost always a bad choice. It is better to write new set-based code.


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

 

[desperateUser]

Actually, I'm looking for records where the field center_desc has changed. Wouldn't the INSERT statement add a duplicate record to the one that has the old center_desc?

 

[SQLSister]

Then why not use an update?

REally we need some more info here to help you. Give us some sample data and results you want after this is done. Then maybe we can give you better advice?

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

 

[desperateUser]

ibiscen data

ic_id (num, 3)
fund_id (varchar, 4)
account_id (varchar, 6)
center_id (varchar, 8)
center_desc (char, 30)
last_modified (datetime, 8)
au (char, 3)

This is a sample record:
81423 711 555110 10066999 WOMENS STRENHGT 3/1/2002 100

The temp_ibiscen would have the exact same data except someone has updated the center_desc field to correct the spelling of STRENGTH in the center_desc field. This is what I need to update in the ibiscen table from the temp_ibiscen table.

The previous developer also wrote cursors to add new records not in the table at all (which I know I can do with an INSERT) and to delete records for center codes no longer in use.

Do you need more data or does this make sense?

 

[SQLSister]

use your update but change it to a join

update ibiscen
set    au = Left(RTrim(t.center_id),3),
       center_desc = t.center_desc,
       last_modified =CONVERT(char(11),getdate())
from ibiscen
join     dbo.temp_ibiscen on
fund_id + account_id + center_id = t.fund + t.account + t.center_id

BYW, last updated should be a datetime field instead of a char field. It is a bad idea to store dates as anything except datetime.

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

 

[desperateUser]

Hey, thanks. A star for you!