Recently, I was working on a poorly performing Interactive Report (IR) within an APEX solution, providing an extension to an HR Oracle E-Business Suite (EBS) application. The solution I ultimately used was a little different, so I thought it would be useful to document what I did.
The report returns holiday allowance information for thousands of employees. To limit the data returned in the base report, a separate region is used to specify three required key filters, which are then used in the WHERE clause of the IR.
Refreshing the report when these items change was taking too long, typically in the region of ten seconds, but occasionally over thirty seconds for managers with a large number of employees.
The report was using a standard SQL query, and the performance issue was a result of the multiple data sources required. The core data is from EBS via a database link, with further data coming from the APEX Workspace. Additionally, there are package functions needed from both EBS and APEX.
The usual solutions had already been tried: SQL tuning, reviewing indexes, rewriting the query, switching to use function-based indexes, etc. There were some noticeable improvements, but performance was ultimately still unacceptable. Using materialised views was also ruled out as the data changes too frequently throughout the day.
When testing all the separate queries and functions independently at the command line, performance was very good, and it was clear that the most significant issue was when combining them together into a single SQL query over the database link.
It was at this stage that I investigated using a PL/SQL package procedure to generate the data, returning the core EBS data first, and then retrieving the additional data for the returned employees from APEX, and lastly calling the additional functions.
The data was generated very quickly, so this became the basis of the solution: to build the data using a PL/SQL package and write it to an APEX collection for the IR in APEX to use. However, to give the best user experience and ensure the APEX IR behaves like any standard query-based report, I needed to add some further tweaks.
The first step was to refine the PL/SQL package procedure to generate the data and write it away to a collection as quickly as possible.
The report uses three parameters to generate data for a single year, manager, and employee type (direct, indirect, or both).
The following is the main body of the procedure.
OPEN c_allowances;FETCH c_allowances BULK COLLECTINTO lt_allowances;CLOSE c_allowances;FOR al_loop IN 1..lt_allowances.countLOOP
lva_employee_number(al_loop) := lt_allowances(al_loop).employee_number;
lva_employee_name(al_loop) := lt_allowances(al_loop).employee_name;
lva_position(al_loop) := lt_allowances(al_loop).position;
lva_location_name(al_loop) := lt_allowances(al_loop).location_name;
lva_std_entitlement(al_loop) := lt_allowances(al_loop).standard_entitlement;
lva_long_service(al_loop) := lt_allowances(al_loop).long_service;
lva_carry_over(al_loop) := lt_allowances(al_loop).carry_over;
lva_buy_sell(al_loop) := lt_allowances(al_loop).buy_sell;
lva_total_annual_leave(al_loop) := lt_allowances(al_loop).total_annual_leave;
lva_total_approved(al_loop) := lt_allowances(al_loop).total_approved;
lva_pending_approval(al_loop) := lt_allowances(al_loop).total_pending_approval;
lva_total_left_to_book(al_loop) := lt_allowances(al_loop).total_left_to_book;
lva_manager(al_loop) := lv_manager;
lva_employee_type(al_loop) := lt_allowances(al_loop).employee_type;
lva_carry_over_css(al_loop) := hr_utils.alw_hch('CO',lt_allowances(al_loop).id,pn_year);
lva_pending_highlight(al_loop) := hr_utils.alw_hch('CO',lt_allowances(al_loop).id,pn_year);
END LOOP;
IF lt_allowances.count > 0 THEN
apex_collection.create_or_truncate_collection(p_collection_name => 'HR_ALLOWANCE_DATA');
apex_collection.add_members(p_collection_name => 'HR_ALLOWANCE_DATA',
p_c001 => lva_employee_number,
p_c002 => lva_employee_name,
p_c003 => lva_position,
p_c004 => lva_location_name,
p_c005 => lva_std_entitlement,
p_c006 => lva_long_service,
p_c007 => lva_carry_over,
p_c008 => lva_buy_sell,
p_c009 => lva_total_annual_leave,
p_c010 => lva_total_approved,
p_c011 => lva_pending_approval,
p_c012 => lva_total_left_to_book,
p_c013 => lva_carry_over_css,
p_c014 => lva_pending_highlight_css,
p_c015 => lva_manager,
p_c016 => lva_employee_type);
ELSIF apex_collection.collection_exists(p_collection_name => 'HR_ALLOWANCE_DATA') THEN
apex_collection.delete_collection(p_collection_name => 'HR_ALLOWANCE_DATA');
END IF;
Next, I modified the IR to use the HR_ALLOWANCE_DATA collection using the following SQL query, converting the numeric data back to numbers so they displayed correctly in the report.
SELECT c001 employee_number,
c002 employee_name,
c015 manager_name,
c016 employee_type,
c003 position,
c004 location_name,
TO_NUMBER(c005) standard_entitlement,
TO_NUMBER(c006) long_service,
TO_NUMBER(c007) carry_over,
TO_NUMBER(c008) buy_sell,
TO_NUMBER(c009) total_annual_leave,
TO_NUMBER(c010) total_approved,
TO_NUMBER(c011) pending_approval,
TO_NUMBER(c012) total_left_to_book,
c013 carry_over_highlight,
c014 pending_highlight
FROM apex_collections
WHERE collection_name = 'HR_ALLOWANCE_DATA';The final step in the solution was to add a dynamic action to execute when any of the three filter items changed and call the package to rebuild the report, and then refresh the IR.
To do this, I added the following JavaScript code to the dynamic action.
apex.server.process( "GEN_ALLOWANCE_COLLECTION", {
pageItems: "#P1_YEAR,#P1_MANAGER,#P1_EMPLOYEE_TYPE"
}, {
success: function( data ) {
apex.region("empAllowances").refresh();
},
error: function( jqXHR, textStatus, errorThrown ) {
apex.message.clearErrors();
apex.message.showErrors([
{
type: "error",
location: "page",
message: data.errorMsg,
unsafe: false
}
]);
}
} ); The JavaScript code calls an AJAX callback process named GEN_ALLOWANCE_COLLECTION, submitting the values of the three filter items into the session first. If successful, the IR region is then refreshed using its static id, otherwise an error is displayed using the function apex.message.showErrors.
The GEN_ALLOWANCE_COLLECTION AJAX process simply calls the database package procedure to rebuild the collection, passing in the three filter values as parameters.
BEGIN
lv_error := hr_reports.gen_employee_allowance_data(pn_manager_number => :P1_MANAGER,
pn_year => :P1_YEAR,
pv_reports_mode => :P1_EMPLOYEE_TYPE);
IF lv_error IS NOT NULL THEN
apex_json.open_object;
apex_json.write('success',FALSE);
apex_json.write('errorMsg', error);
apex_json.close_object;
ELSE
apex_json.open_object;
apex_json.write('success',TRUE);
apex_json.close_object;
END IF;
EXCEPTION
WHEN OTHERS THEN
apex_json.open_object;
apex_json.write('success',FALSE);
apex_json.write('errorMsg', 'Failed to generate team allowance data');
apex_json.close_object;
END;
Now, each time one of the filter items changes the dynamic action rebuilds the APEX collection and refreshes the IR. After that, the user can use the built-in IR features to further modify the report as usual.
The new solution worked well and typically takes one second to return the data. For managers with many employees, it occasionally takes two or three seconds.
By default, when an AJAX process is executing, the user does not see anything, so they could mistakenly think nothing is happening and try changing the filters again. Therefore, I decided to add the standard APEX spinner to display while the APEX collection is rebuilding via the AJAX callback.
To do this, I simply use the apex.util.showSpinner function to add a spinner to the IR region (using its static id), before calling the AJAX callback process, and then remove it afterwards.
var lSpinner = apex.util.showSpinner($("#empAllowances"));
apex.server.process( "GENERATE_ALLOWANCE_COLLECTION", {
pageItems: "#P1_YEAR,#P1_MANAGER,#P1_EMPLOYEE_TYPE"
}, {
success: function( data ) {
$("#apex_wait_overlay").remove();
$(".u-Processing").remove();
apex.region("empAllowances").refresh();
},
error: function( jqXHR, textStatus, errorThrown ) {
$("#apex_wait_overlay").remove();
$(".u-Processing").remove();
apex.message.clearErrors();
apex.message.showErrors([
{
type: "error",
location: "page",
message: data.errorMsg,
unsafe: false
}
]);
}
} );
There will no doubt be other ways this performance issue could have been resolved, but for this application, the method has worked well and provides great performance, while leaving the user with a standard IR that behaves exactly as it did previously.
You can see the performance in this video clip.
For more information, check out our Oracle APEX Services, and if you liked this blog, check out our other APEX blogs here.
Contact us today, and one of our expert developers will be in touch.