I created an Oracle APEX Plugin recently, with the amazing title of Pretius x-y of Lazy z Pagination. I created this plugin because Interactive Reports, with x-y of z pagination, were taking a ridiculously long amount of time to not only fetch the results, but fetch the row count at the same time. i.e why take 2 trips to the database when you can take 1 - and the price paid for this is, for enormous tables, the user has to wait until the DB has counted all records.
Why not enable lazy loading Matt? this has no affect as the lazy part is on the results and, as I told you, the row count is integrated with the results, so no joy with this smart idea.
How slow are you talking? OK for 6.5m records, here are some rough timings for the first page of 20 rows:
None: 0.00576 secondsx - y: 0.00660 secondsx -y of z: 25.93756
The x -y of z performance issue has been known for some time making Point #8 of Michelle’s 15 Top Tips to tune your Oracle APEX Performance - coincidentally, this blog is released exactly 5 years, to the day, following Michelle’s blog
The plugin’s purpose is to take advantage of the speedy x - y pagination and provide the z count in the background. Combine the two and … x - y of lazy z is born.
The picture above shows a spinner representing z, which is replaced by the row count following the results of the background job.
But this blog isn’t about promoting the plugin. This is about an interesting find during the testing.
To code this plugin, I needed to obtain the SQL behind the query, including all filters and customizations. This would have been a job for apex_ir.get_report, however I did find it was deprecated - It has been a while since I needed this code. The documentation says to use APEX_REGION.OPEN_QUERY_CONTEXT instead.
Here is how I implemented it. It’s quite simple really
-- Open the query context using APEX_EXEC
l_context := apex_region.open_query_context (
p_page_id => l_app_page_id_c,
p_region_id => l_region_id,
p_component_id => l_report_id,
p_outer_sql => l_outer_sql_c );
-- -- Fetch and process rows using APEX_EXEC
WHILE apex_exec.next_row(l_context) LOOP
l_row_count := apex_exec.get_number(l_context, 1);
END LOOP;
I also use an outer SQL of the following string defined as a constant.
SELECT COUNT(*) CNT FROM #APEX$SOURCE_DATA#
This just wraps around the whole SQL, so it will count all the records. The #APEX$SOURCE_DATA# is defined within the documentation and is used to denote the main region source.
Following unit testing of the Plugin, I then submitted it to the Pretius team to review the code and…. TEST FAILED (not exactly - I’m being a bit dramatic here).
What, what, what? … Why? so apparently, the code-reviewer told me that the outer SQL is unnecessary as p_total_row_count will accept a boolean to count the rows and basically I should use that and not the outer SQL.
I’m all for native solutions and so I tried this and… immediately, I can tell, even without implementing any timings, this feature is shockingly slow. In fact it is the same effect as restoring the x - y of z pagination.
Therefore, after supplying evidence, I convinced the code-reviewer that I had overlooked this feature, however, following testing, the outer SQL method was indeed the correct one.
So, in the words of Jon Dixon: For Speeds Sake, Stop Using p_total_row_count
… even though I do use APEX_JSON in this plugin 😉
I think, on that point, Jon does prove, without a doubt, that alternatives to APEX_JSON are much faster. However, for smaller sets like in my case, a single object results set, it’s perfectly fine.
-- Return JSON data with the row count
apex_json.open_object;
apex_json.write('data', l_row_count);
apex_json.write('reportid', l_report_id);
apex_json.write('plpseq', l_plpseq);
apex_json.write('plpcs', l_plpcs);
apex_json.close_object;
Finally, why even use row counts at all? They can take a long time to compute and they are only relevant for that moment - so why even bother at all? - It the words of Connor Macdonald just don’t do it.
I agree with Connor & this Plugin would only be relevant if
The client insisted on row counts
Performance issues are/likely to be encountered
ENJOY
What’s the picture? Harrogate Christmas Fayre. Visit Yorkshire.