MySQL Forums
Forum List  »  Performance

cursor size problem
Posted by: Srivats Chandrasekaran
Date: December 12, 2005 05:49AM

Hi All

I have a store proc in which i dump .26 million rows of data of two columns. When i run this store proc the DB restarts itself due to some memory problem. My read buffer memory is 2G and my server has a memory of 1GB ram.

create procedure nb_compute_nol_geographic_info(in nb_factor_date_gb char)
begin

declare nb_current_nol_tmp int;
declare nb_pool_number_tmp char;
declare nb_factor_date_tmp date;
declare nb_orig_nol_tmp int;
declare nb_nol_tmp int;
declare nb_loan_size_tmp int;
declare nb_orig_loan_size_tmp int;
declare nb_end_loop int;
declare nb_current_balance_tmp double;

declare geographic_info CURSOR for select sum(nb_current_nol),nb_pool_number from nb_geographic_info where nb_factor_date = nb_factor_date_gb group by nb_pool_number;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET nb_end_loop=1;

open geographic_info;
repeat

fetch geographic_info into nb_nol_tmp,nb_pool_number_tmp;

select nb_current_balance into nb_current_balance_tmp from nb_pool_info where nb_pool_number = nb_pool_number_tmp and nb_factor_date = nb_factor_date_gb;


if nb_current_balance_tmp = 0 then

update nb_pool_master
set
nb_original_nol = nb_nol_tmp
where
nb_pool_number = nb_pool_number_tmp;

else
update nb_pool_info
set
nb_nol = nb_nol_tmp,
nb_loan_size = nb_current_balance_tmp/nb_nol_tmp
where
nb_pool_number = nb_pool_number_tmp and
nb_factor_date = nb_factor_date_gb;
end if;

until nb_end_loop =1
end repeat;
close geographic_info;
end;
//

Is there neway i can get the store proc to load data into the cursor some 10000 records at a time or something like that. Or will increasing my read memory buffer size help.

Thks All

Options: ReplyQuote


Subject
Views
Written By
Posted
cursor size problem
3321
December 12, 2005 05:49AM
1322
December 15, 2005 02:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.