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 –

[sourcecode language="sql"]
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
p_collection_name => ‘COLL1′,
p_query => ‘select * from emp’,
p_generate_md5 => ‘NO’);
[/sourcecode]

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

[sourcecode language="sql"]
JES@dbtest> create table my_all_objects as select * from all_objects;

Table created.

JES@dbtest> select count(*) from my_all_objects;

COUNT(*)
———-
55853
[/sourcecode]

So now I’m going to create an APEX application, which has the following code in a process:

[sourcecode language="sql"]
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
p_collection_name => ‘COLL1′,
p_query => ‘select * from my_all_objects’,
p_generate_md5 => ‘NO’);
[/sourcecode]

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 –

[sourcecode language="sql"]
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
[/sourcecode]

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 –

[sourcecode language="sql"]
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(
p_collection_name => ‘COLL1′,
p_query => ‘SELECT * FROM my_all_objects’,
p_generate_md5 => ‘NO’);
[/sourcecode]

to

[sourcecode language="sql"]
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
p_collection_name => ‘COLL1′,
p_query => ‘SELECT * FROM my_all_objects’);
[/sourcecode]

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:

[sourcecode language="sql"]
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
[/sourcecode]

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.

11 responses

  1. 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.

  2. 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

  3. 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.

  4. Great tip, thanks!nn”18 seconds is a long time for an user to wait for something to happen in a web application”nnI 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.”

  5. 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

  6. 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

  7. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *