I’d love to say “I’m quite often asked…”, but to be honest hardly anyone seems to use Collections in APEX (a very under utilised feature in my opinion).
However, a question does come up from time to time, often along the lines of “I am using a collection but it is slow…”.
Typically, you might create a collection in the following way -
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
p_collection_name => 'COLL1',
p_query => 'select * from emp',
p_generate_md5 => 'NO');
This would create a collection (called ‘COLL1′) and populate it with the results of the query (select * from emp).
So, let’s take a quick example. Firstly I want to use a sizable table
JES@dbtest> create table my_all_objects as select * from all_objects;
Table created.
JES@dbtest> select count(*) from my_all_objects;
COUNT(*)
----------
55853
So now I’m going to create an APEX application, which has the following code in a process:
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
p_collection_name => 'COLL1',
p_query => 'select * from my_all_objects',
p_generate_md5 => 'NO');
It’s not really important to see the APEX application as such, what is important is to see how long it takes to run this process. There are lots of ways I can achieve that (putting in my own instrumentation etc), but APEX comes with a nice debug mode, which shows me timing information for everything that it does when you run a page. Running that page in debug mode I see something similar to -
0.03: ...Process "Create 1": PLSQL (AFTER_SUBMIT) APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY( p_collection_name => 'COLL1', p_query => 'SELECT * FROM my_all_objects', p_generate_md5 => 'NO');
18.57: ...Do not run process "Create_B", process point=AFTER_SUBMIT, condition type=, when button pressed=CREATE_B
The important bit here is the difference in timings (18.57 – 0.03) between the two lines, so that means my process took around 18.54 seconds to create the collection. 18 seconds is a long time for an user to wait for something to happen in a web application.
So, what can we do to improve things? Well if you look in the documentation for APEX_COLLECTION you’ll see there is another routine called CREATE_COLLECTION_FROM_QUERY_B. From the documentation -
The CREATE_COLLECTION_FROM_QUERY_B method offers significantly faster performance than the CREATE_COLLECTION_FROM_QUERY method by performing bulk SQL operations, but has the following limitations:
No column value in the select list of the query can be more than 2,000 bytes. If a row is encountered that has a column value of more than 2,000 bytes, an error will be raised during execution.
The MD5 checksum will not be computed for any members in the collection.
Ok, so if we can live with those limitations (the restriction about not computing the checksum is not as significant as it might at first seem, since we could also manually calculate it ourselves once the collection has been populated), then it’s worth trying it, so changing our code from -
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
p_collection_name => 'COLL1',
p_query => 'SELECT * FROM my_all_objects',
p_generate_md5 => 'NO');
to
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
p_collection_name => 'COLL1',
p_query => 'SELECT * FROM my_all_objects');
as you can see, it’s a relatively minor change. So let’s run the page in debug mode again and see if there is an improvement:
0.03: ...Process "Create_B": PLSQL (AFTER_SUBMIT) APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B( p_collection_name => 'COLL1', p_query => 'SELECT * FROM my_all_objects');
5.25: Branch point: AFTER_PROCESSING
So this time it took around 5.2 seconds (5.25 – 0.03) to create the collection. (Note that I ran these tests multiple times to remove any caching issues).
Whilst I’d say 5.2 seconds is still a long time for a user to wait for a webpage, it’s a significant improvement over 18 seconds. Note your own results might vary with the difference in timings.
