Increasing the speed of APEX Collections

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.

Comments 11

  1. Mark Lancaster wrote:

    Hi John

    That’s a really good tip to remember.

    I can’t say I’ve ever used a collection to load 55,000 records, but agree entirely with you that collections are a great feature.

    Every time I use them I keep saying I should use collections more often.

    Posted 24 Feb 2010 at 11:52 am
  2. Joel R. Kallman wrote:

    Nice post, John. It’s one I’ve always wanted to write.

    What you’ll see in APEX 4.0 (and in the upcoming refresh of the Early Adopter’s instance of APEX 4.0) are three new parameters to create_collection_from_query_b, namely p_names and p_values, so you can pass in the bind variable names and values for your query, and p_max_row_count – so you can easily limit the number of rows populated in the collection, instead of having to write our own outer SELECT.

    Also, the part about the limitation of 2,000 bytes for VARCHAR2 no longer applies if you’re running DB 11gR2 (where the DBMS_SQL.VARCHAR2_TABLE type was finally resized to a table of VARCHAR2(4000)).

    Joel

    Posted 24 Feb 2010 at 6:51 pm
  3. StewS wrote:

    John,

    Thanks for the good tip and the statistics.

    Thanks to Joel for the hint about the new and improved version upcoming.

    Posted 24 Feb 2010 at 10:40 pm
  4. Peter Raganitsch wrote:

    Hi John,

    thanks for this hint, i’ll try this.

    Do you have any further tips or best practices when dealing with collections? When to use and when not?

    thanks,

    Peter

    Posted 25 Feb 2010 at 7:01 am
  5. Olivier Dupont wrote:

    Hey John,

    I use collections a lot but never the CREATE_COLLECTION_FROM_QUERY_B option.

    Thanks for the tip.

    Posted 25 Feb 2010 at 9:43 am
  6. John Scott wrote:

    Joel,

    Thank you and thanks for the heads up on the 4.0 improvements, I can see those being *extremely* useful!

    John.

    Posted 25 Feb 2010 at 10:30 am
  7. John Scott wrote:

    Peter,

    Thank you, yes I have a few more (draft) posts relating to collections that I should be publishing soon (when I get a chance to finish them).

    John.

    Posted 25 Feb 2010 at 10:31 am
  8. Jeff Kemp wrote:

    Great tip, thanks!\n\n”18 seconds is a long time for an user to wait for something to happen in a web application”\n\nI would have said, “18 seconds is a long time for a user to wait for something to happen in a web application, unless they specifically asked for all 55,000 records to be retrieved and shown on the page.”

    Posted 15 Mar 2010 at 8:06 am
  9. Denise wrote:

    Hi John,
    although you posted this a long time ago I just came across this bit of code as faced a problem with my collections. Usually I don’t have any problems with collections but now it hit me. Anyway, I would like to tell you my problem and then maybe you will have a tip or something:
    I am having a manually created tabular form with a checkbox as first column. When rows are ticked and button is pressed the page is submitted. A process will run:
    1. if collection exists, delete it
    2. create collection
    3. loop through ticked rows APEX_APPLICATION.G_F01.COUNT
    4. add members to collection APEX_COLLECTION.ADD_MEMBER(
    p_collection_name => v_coll_name,
    p_c001 => apex_application.g_f02(APEX_APPLICATION.G_F01(i)),–id
    a few more columns are coming up
    5.when I run the page in debug mode the process will run 90!! seconds.
    For one week long it ran fine. I don’t know what happened all of a sudden to run so long.
    Do you have an idea?
    Any help is highly appreciated.
    Thanks in advance
    Denise

    Posted 16 Feb 2011 at 3:58 pm
  10. Damir wrote:

    Hi!

    I do not understand what is the difference in mentioned two examples!??

    AFAIK, if parameter is not required then default value is assigned, and in thi scase it is “N” as in your first example…

    Any comment?
    THX
    Damir

    Posted 03 Apr 2011 at 4:23 pm
  11. John Scott wrote:

    Hi Damir,

    I’m not entirely sure I understand your question? The two examples call different routines -

    CREATE_COLLECTION_FROM_QUERY
    CREATE_COLLECTION_FROM_QUERY_B

    John.

    Posted 04 Apr 2011 at 7:54 am

Post a Comment

Your email is never published nor shared. Required fields are marked *