Listing Host Structure array into table
I'm trying to tweak a piece of big code written in Pro * C, specifically the bottleneck loop and the statement UPDATE
inside it. for loop
goes through the "Host Structure" array which can contain several thousand times in millions of records and the update is done too many times. The update can be done most often during the entire program, but it will require a radical change in the code, and I am not entitled to make major changes.
So I have something like this
....
#define NULL_REF_NO 10
#define NULL_ERR 256
....
....
struct s_errors
{
char s_ref_id [NULL_REF_NO];
char s_ref_seq_no [NULL_REF_NO];
char s_err_msg [NULL_ERR];
};
....
....
struct s_errors *ps_errors = NULL;
....
....
/*The part below happens throughout the program to collect all errors*/
/*ls_ref_id, ls_ref_seq_no, and ls_err_msg are local variables of same data type. and i_curr_index is the array index variable*/
strcpy(ls_ref_id, ps_errors[i_curr_index].s_ref_id);
strcpy(ls_ref_seq_no, ps_errors[i_curr_index].s_ref_seq_no);
strcpy(ls_err_msg, ps_errors[i_curr_index].s_err_msg);
.....
/* At this point ps_error contains thousands or even millions of rows*/
/* The final part is to update all these rows back to the table like below*/
/* pl_err_count is a Global var which keeps track of the total number of records in the host structure array*/
int i_curr_index = 0;
char l_ref_id [NULL_REF_NO];
char l_ref_seq_no [NULL_REF_NO];
char l_err_msg [NULL_ERR];
for(i_curr_index = 0; i_curr_index < pl_err_count; i_curr_index++)
{
strcpy(l_ref_id, ps_errors[i_curr_index].s_ref_id);
strcpy(l_ref_seq_no, ps_errors[i_curr_index].s_ref_seq_no);
strcpy(l_err_msg, ps_errors[i_curr_index].s_err_msg);
EXEC SQL
UPDATE some_table
SET status = 'E',
error_message = :l_err_msg
WHERE ref_id = :l_ref_id
AND ref_seq_no = :l_ref_seq_no;
if (SQL_ERROR_FOUND)
{
sprintf(err_data, "Updation failed with sql errors ");
strcpy(table, "some_table");
WRITE_ERROR(SQLCODE,function,"",err_data);
return(FATAL);
}
}
The bottleneck is the for loop above (and this is the last step in the program) that alternates too many times, making the program run for a long time. I was wondering if there is a way to CAST
host a Host Structure array ps_errors
for an Oracle table type so that I can easily do bulkier UPDATE
or even do MERGE
with some parallel DML without having to iterate over every record.
source to share
Extrapolating from some other code I've seen at work that does something similar, you can do something like this instead of a loop for
:
EXEC SQL for :pl_err_count
UPDATE some_table
SET status = 'E',
error_message = :ps_errors.s_err_msg
WHERE ref_id = :ps_errors.s_ref_id
AND ref_seq_no = :ps_errors.s_ref_seq_no;
This may depend on the content added to ps_errors
the null-terminated. Given the use strcpy()
in existing code and not strncpy()
(or similar), I am assuming they are already there.
If it s_err_msg
can be zero, you should also consider using indicator variables . eg.
error_message = :ps_errors.s_err_msg INDICATOR :indicator_variable
source to share
You can use array update http://docs.oracle.com/cd/B28359_01/appdev.111/b28427/pc_08arr.htm#i1879 but you will need to modify your array of structures ps_errors
as structure arrays
eg:
EXEC SQL
UPDATE some_table
SET status = 'E',
error_message = :ps_errors.s_err_msg
WHERE ref_id = :ps_errors.s_ref_id
AND ref_seq_no = :ps_errors.s_ref_seq_no;
source to share