Srivats Chandrasekaran wrote:
> 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.
Sure, don't use a cursor when you don't need one.
CREATE PROCEDURE nb_compute_nol_geographic_info(IN nb_factor_date_gb char)
BEGIN
# Update all pool master records having a current balance
# of zero to the sum of the pool's geographic info rows.
UPDATE nb_pool_master
INNER JOIN nb_pool_info
ON nb_pool_master.nb_pool_number = nb_pool_info.nb_pool_number
AND nb_pool_info.nb_factor_date = nb_factor_date_gb
INNER JOIN (
SELECT
SUM(nb_current_nol) as sum_cur_nol
, nb_pool_number
FROM nb_geographic_info
WHERE nb_factor_date = nb_factor_date_gb
GROUP BY nb_pool_number
) as sums
ON nb_pool_master.nb_pool_number = sums.nb_pool_number
SET nb_pool_master.nb_original_nol = sums.sum_cur_nol
WHERE nb_pool_info.nb_current_balance = 0;
# Update all the rest of pool info records.
UPDATE nb_pool_info
INNER JOIN (
SELECT
SUM(nb_current_nol) as sum_cur_nol
, nb_pool_number
FROM nb_geographic_info
WHERE nb_factor_date = nb_factor_date_gb
GROUP BY nb_pool_number
) as sums
ON nb_pool_info.nb_pool_number = sums.nb_pool_number
SET nb_pool_info.nb_nol = sums.sum_cur_nol
, nb_pool_info.nb_loan_size = (nb_pool_info.nb_current_balance / sums.sum_cur_nol)
WHERE nb_pool_info.nb_current_balance <> 0
AND nb_pool_info.nb_factor_date = nb_factor_date_gb;
END //
The way to do this is to use set-based SQL as opposed to procedural language constructs, like cursors. It's kind of like trying to fit a sqare peg through a round hole...
> Or will increasing my read
> memory buffer size help.
No, since you've already increased it past the memory that the machine actually has... You should lower it to something more in line with the actual RAM of the machine.
As an afterthought, you might also get some added performance benefit by placing the sums into a temporary table, instead of using the derived table like above. So, it would look like this:
CREATE PROCEDURE nb_compute_nol_geographic_info(IN nb_factor_date_gb char)
BEGIN
CREATE TEMPORARY TABLE sums
SELECT
SUM(nb_current_nol) as sum_cur_nol
, nb_pool_number
FROM nb_geographic_info
WHERE nb_factor_date = nb_factor_date_gb
GROUP BY nb_pool_number;
# Update all pool master records having a current balance
# of zero to the sum of the pool's geographic info rows.
UPDATE nb_pool_master
INNER JOIN nb_pool_info
ON nb_pool_master.nb_pool_number = nb_pool_info.nb_pool_number
AND nb_pool_info.nb_factor_date = nb_factor_date_gb
INNER JOIN sums
ON nb_pool_master.nb_pool_number = sums.nb_pool_number
SET nb_pool_master.nb_original_nol = sums.sum_cur_nol
WHERE nb_pool_info.nb_current_balance = 0;
# Update all the rest of pool info records.
UPDATE nb_pool_info
INNER JOIN sums
ON nb_pool_info.nb_pool_number = sums.nb_pool_number
SET nb_pool_info.nb_nol = sums.sum_cur_nol
, nb_pool_info.nb_loan_size = (nb_pool_info.nb_current_balance / sums.sum_cur_nol)
WHERE nb_pool_info.nb_current_balance <> 0
AND nb_pool_info.nb_factor_date = nb_factor_date_gb;
DROP TABLE sums;
END //
Hope this helps,
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com