Can this cursor be removed?
-
Greetings,
I'd like to know if there is a way to get rid of the cursor used in the following code. Any insights would be greatly appreciated.
Forever learning,
Jeff
-----------------------------------
declare @loan_number varchar( 13 ),
@system_date datetime
begin transaction
select @system_date = getdate()
declare number_update cursor for
select loan_number
from flat_load
open number_update
fetch next from number_update into @loan_number
while @@fetch_status = 0
begin
update orig_serv_number_seed
set loan_number = @loan_number
where case_number = ( select min( case_number )
from orig_serv_number_seed
where loan_number is null )
update b
set servicing_loan_number = a.case_number,
update_date = @system_date
from orig_serv_number_seed a
inner join ods..loan_misc_data b on a.loan_number = b.loan_number
where a.loan_number = @loan_number
fetch next from number_update into @loan_number
end
close number_update
deallocate number_update
insert into orig_serv_number ( case_number, loan_number, system_insert_date )
select case_number,
loan_number,
@system_date
from orig_serv_number_seed
where loan_number is not null
order by case_number
delete orig_serv_number_seed
where loan_number is not null
commit transaction
-
Are there a lot of transactions? This may work but I am not sure this is a good option and that I didn't miss something. And it will depend on how well the indexes are and the number of rows to be processed.
It would help if I could undertsand what this table orig_serv_number_seed and this column case_number are about. Make sure you test before using somewhere safe.
delcare @system_date datetime
set @system_date = getdate()
update
oseed
set
oseed.loan_number = oload.loan_number
from
orig_serv_number_seed oseed
inner join
flat_load oload
on
(select count(*) from orig_serv_number_seed iseed where iseed.case_number < oseed.case_number) =
(Select count(*) from flat_load iload where iload.loan_number < oload.loan_number)
update
b
set
servicing_loan_number = a.case_number,
update_date = @system_date
from
orig_serv_number_seed a
inner join
ods..loan_misc_data b
on
a.loan_number = b.loan_number
insert
orig_serv_number
(
case_number,
loan_number,
system_insert_date
)
select
case_number,
loan_number,
@system_date
from
orig_serv_number_seed
where
loan_number is not null
order by
case_number
--Delete used case_numbers
delete
orig_serv_number_seed
where
loan_number is not null
-
If you only want to get rid of the cursor you could use a memory table.
But if the cursor would be HUGE it wouldn't be an option. But if the cursor is relatively small you could use a mem table.
-
First off, thank you both for replying!
Patrick, when you say I can use a memory table, do you mean a temp table (i.e., #table_name)? I tried searching for "memory table" in BoL, but couldn't find anything. And if it is a temp table that you're referring to, then if I understand correctly I would still be using the cursor (although this would increase performance, I believe).
James, I think it's going to take me the better part of the morning to fully grasp that initial join of yours (I'm sure it's quite simple, I've just never seen a join like that before).
And I'll try to clarify the scenario this is being used in.No, there are not a lot of transactions; basically, there's just the one transaction that will either succeed or fail (there is error checking, but I removed that when I posted the code).
Some background on this process. Our company does home loans. A customer has a "loan_number" that uniquely identifies them. After the loan funds (meaning the customer is getting their money), it moves to a different department and is assigned another unique identifier, which is the "case_number".
"flat_load" is a table with a single column [loan_number, varchar(13)] that has our daily funded loans. This table usually has less than 1,000 rows and is not indexed (because it's only a single column and I didn't think an index would improve performance). Our process starts by deleting all the rows in this table and then inserting a list of newly funded loans (again, these are the loans that will need to have a "case_number" assigned to them for our other department).
"orig_serv_number_seed" is a table with two columns [case_number, varchar(10); loan_number, varchar(13)] that has a block of "case_number"'s assigned (i.e., all rows in "case_number" have a value already assigned and the "loan_number" column is completely NULL each time the process is run). The "loan_number" column is updated with a loan from "flat_load" and will eventually move to the table below "orig_serv_number". This table has about 200,000 rows, with "case_number" set to the primary key, but no indices (again, I thought since the table was narrow, it didn't need indices). This table is used to store the list of pre-calculated unique identifiers ("case_number") that will be used by the other department. After a "case_number" is used from this table (i.e., when a "loan_number" from "flat_load" is assigned to "case_number") it will be removed so it's not used by another loan in the future. As we run out of "case_number" rows, this table will have additional rows manually inserted.
"orig_serv_number" is a table with three columns [case_number, varchar(10); loan_number, varchar(13); system_insert_date, datetime] that is used as a final cross reference table in our production database (the other two tables are on the same production server, but are in a "staging" database). This table has about 160,000 rows, with "case_number" set to the primary key, but no indices.
Well, I hope that gives you more insight. Now I'm off to take a look at your code. Thanks again!!
Forever learning,
Jeff
-
No memory table is not a Temp table. It is really a table in the server's memory. Nothing is on disk... There is no I/O. That is why the memtable shouldn't be too large.... Memory tables are very fast. No locks Nothing in Tempdb.
And when you finish the stored procedure the memtable is gone as well !!!
Below an example of a function. Don't aske me what it does because i found it on this Website. But when i found it it was using a cursor. I rewrote this one so it used a memory table. And guess what .... It was faster.....
Declare @Memtable Table
( int identity,
fieldA Varchar(100)
FiledB Integer)
I have an example posted on this Website, Look for memory table
here is the example.....
SET NOCOUNT ON
DECLARE @ARRAY TABLE (K1 INT IDENTITY, ARRAYCOL VARCHAR(8000))
INSERT @ARRAY (ARRAYCOL) VALUES ('LES PAUL '+
'BUDDY GUY '+
'JEFF BECK '+
'JOE SATRIANI ')
INSERT @ARRAY (ARRAYCOL) VALUES ('STEVE MILLER '+
'EDDIE VAN HALEN'+
'TOM SCHOLZ ')
INSERT @ARRAY (ARRAYCOL) VALUES ('STEVE VAI '+
'ERIC CLAPTON '+
'SLASH '+
'JIMI HENDRIX '+
'JASON BECKER '+
'MICHAEL HARTMAN')
DECLARE @I INT, @L INT
DECLARE @CURREC INT, @MAXREC INT
DECLARE @ARRAYVAR VARCHAR(8000)
SET @CURREC = 0
SELECT @MAXREC = MAX(K1) FROM @ARRAY
WHILE @CURREC < @MAXREC
BEGIN
SET @CURREC = @CURREC + 1
SELECT @ARRAYVAR= ARRAYCOL FROM @ARRAY WHERE K1 = @CURREC
SET @I = 0
SET @L=DATALENGTH(@ARRAYVAR)/15
WHILE (@I<@L) BEGIN
SELECT 'GUITARIST'=SUBSTRING(@ARRAYVAR,(@I*15)+1,15)
SET @I=@I+1
END
END
Edited by - well0549 on 12/06/2002 12:57:39 AM
-
Thanks, Patrick, I'll look into this.
And I'll try to clarify the scenario this is being used in.Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply