Monday, June 15, 2009

How to Quickly Append VARCHAR2 to CLOB

This is not an APEX specific issue, however it could be useful for some of your PL/SQL code

I ran into an issue today where I had to append VARCHAR2s to a CLOB many times in a loop. I first tried appending a VARCHAR2 to a CLOB: CLOB := CLOB || VARCHAR2. I noticed that this was taking a long time to run. In order to speed up the process I tried the following techniques:
- Create a "temp" CLOB (TMP_CLOB := VARCHAR2) and then appended it the clob CLOB := CLOB || CLOB
- Use the CLOB := CLOB || TO_CLOB(VARCHAR2)
- Use DBMS_LOB.append (CLOB, VARCHAR2)

All three options resulted in significant speed increases, however using the "temp" CLOB method resulted in the quickest code. Here is the test that I ran along with the results:


DECLARE
v_start TIMESTAMP;
v_end TIMESTAMP;
v_clob CLOB;
v_tmp_clob CLOB;
v_iterations PLS_INTEGER := 100000; -- Used 1,000, 10,000, and 100,000 for testing
BEGIN
v_start := SYSTIMESTAMP;
v_clob := NULL;

FOR i IN 1 .. v_iterations LOOP
v_clob := v_clob || TO_CHAR (SYSTIMESTAMP) || ', ';
END LOOP;

v_end := SYSTIMESTAMP;
DBMS_OUTPUT.put_line ('CLOB := CLOB || VARCHAR2 method: ' || TO_CHAR (v_end - v_start));
v_start := SYSTIMESTAMP;
v_clob := NULL;

FOR i IN 1 .. v_iterations LOOP
v_clob := v_clob || TO_CLOB (TO_CHAR (SYSTIMESTAMP) || ', ');
END LOOP;

v_end := SYSTIMESTAMP;
DBMS_OUTPUT.put_line ('CLOB := CLOB || TO_CLOB(VARCHAR2) method: ' || TO_CHAR (v_end - v_start));
v_start := SYSTIMESTAMP;
v_clob := NULL;

FOR i IN 1 .. v_iterations LOOP
v_tmp_clob := TO_CHAR (SYSTIMESTAMP) || ', ';
v_clob := v_clob || v_tmp_clob;
END LOOP;

v_end := SYSTIMESTAMP;
DBMS_OUTPUT.put_line ('CLOB := CLOB || TMP_CLOB method: ' || TO_CHAR (v_end - v_start));
v_start := SYSTIMESTAMP;
v_clob := NULL;
v_clob := 'h'; -- need to initialize it;

FOR i IN 1 .. v_iterations LOOP
DBMS_LOB.append (v_clob, TO_CHAR (SYSTIMESTAMP) || ', ');
END LOOP;

v_end := SYSTIMESTAMP;
DBMS_OUTPUT.put_line ('DBMS_LOB.append method: ' || TO_CHAR (v_end - v_start));
END;


The results were as follows:

1,000 Iterations
CLOB := CLOB || VARCHAR2 method: +000000000 00:00:00.578000000
CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:00.063000000
CLOB := CLOB || TMP_CLOB method: +000000000 00:00:00.047000000
DBMS_LOB.append method: +000000000 00:00:00.172000000

10,000 Iterations
CLOB := CLOB || VARCHAR2 method: +000000000 00:00:10.656000000
CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:00.688000000
CLOB := CLOB || TMP_CLOB method: +000000000 00:00:00.672000000
DBMS_LOB.append method: +000000000 00:00:00.687000000

100,000 Iterations
CLOB := CLOB || VARCHAR2 method: +000000000 00:42:17.453000000
CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:17.953000000
CLOB := CLOB || TMP_CLOB method: +000000000 00:00:08.140000000
DBMS_LOB.append method: +000000000 00:00:11.110000000

6 comments:

  1. You might want to mention that you can initialize a CLOB using DBMS_LOB.CREATETEMPORARY (http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_lob.htm#i997788). This is especially useful if you need to output the contents of the LOB and don't want an "H" included in it.

    ReplyDelete
  2. This is exactly what I needed, thanks!

    ReplyDelete
  3. Why this solution is not performed with a long row add (2000 caractrers)?
    CLOB := CLOB || TO_CLOB(VARCHAR2) method > near 9 seconds for 1000.

    ReplyDelete
  4. Cool. solved by issue in couple of minutes.

    ReplyDelete
  5. There is one more option: use temporary varchar2 variable, then as varchar2 overflows append it to clob.
    That is 8 times faster than plain dbms_lob.append.

    On my machine, timings for 10'000 iterations are as follows:
    CLOB := CLOB || VARCHAR2 method: +000000000 00:00:29.955637000
    CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:16.037394000
    CLOB := CLOB || TMP_CLOB method: +000000000 00:00:15.695537000
    app(v_clob, v_vc, VARCHAR2) method: +000000000 00:00:00.182733000
    DBMS_LOB.append method: +000000000 00:00:01.488726000


    Here is the code (note "app" procedure):

    DECLARE
    v_start TIMESTAMP;
    v_end TIMESTAMP;
    v_clob CLOB;
    v_tmp_clob CLOB;
    v_iterations PLS_INTEGER := 10000; -- Used 1,000, 10,000, and 100,000 for testing
    v_vc varchar2(32000);
    v_len pls_integer := 0;
    procedure app(v_clob in out nocopy clob, v_vc in out nocopy varchar2, v_app varchar2) is
    begin
    v_vc := v_vc || v_app;
    exception when VALUE_ERROR then
    if v_clob is null then
    v_clob := v_vc;
    else
    dbms_lob.append(v_clob, v_vc);
    v_vc := v_app;
    end if;
    end;
    BEGIN
    v_start := SYSTIMESTAMP;
    v_clob := NULL;

    FOR i IN 1 .. v_iterations LOOP
    v_clob := v_clob || TO_CHAR (SYSTIMESTAMP) || ', ';
    END LOOP;

    v_end := SYSTIMESTAMP;
    DBMS_OUTPUT.put_line ('CLOB := CLOB || VARCHAR2 method: ' || TO_CHAR (v_end - v_start));
    v_start := SYSTIMESTAMP;
    v_clob := NULL;

    FOR i IN 1 .. v_iterations LOOP
    v_clob := v_clob || TO_CLOB (TO_CHAR (SYSTIMESTAMP) || ', ');
    END LOOP;

    v_end := SYSTIMESTAMP;
    DBMS_OUTPUT.put_line ('CLOB := CLOB || TO_CLOB(VARCHAR2) method: ' || TO_CHAR (v_end - v_start));
    v_start := SYSTIMESTAMP;
    v_clob := NULL;

    FOR i IN 1 .. v_iterations LOOP
    v_tmp_clob := TO_CHAR (SYSTIMESTAMP) || ', ';
    v_clob := v_clob || v_tmp_clob;
    END LOOP;

    v_end := SYSTIMESTAMP;
    DBMS_OUTPUT.put_line ('CLOB := CLOB || TMP_CLOB method: ' || TO_CHAR (v_end - v_start));
    v_start := SYSTIMESTAMP;
    v_clob := null;

    FOR i IN 1 .. v_iterations LOOP
    app(v_clob, v_vc, TO_CHAR (SYSTIMESTAMP) || ', ');
    END LOOP;
    v_clob := v_clob || v_vc;

    v_end := SYSTIMESTAMP;
    DBMS_OUTPUT.put_line ('app(v_clob, v_vc, VARCHAR2) method: ' || TO_CHAR (v_end - v_start));

    v_start := SYSTIMESTAMP;
    v_clob := NULL;
    v_clob := 'h'; -- need to initialize it;

    FOR i IN 1 .. v_iterations LOOP
    DBMS_LOB.append (v_clob, TO_CHAR (SYSTIMESTAMP) || ', ');
    END LOOP;

    v_end := SYSTIMESTAMP;
    DBMS_OUTPUT.put_line ('DBMS_LOB.append method: ' || TO_CHAR (v_end - v_start));
    END;

    ReplyDelete
    Replies
    1. Who ever you are Anonymous .... Salute to you... you rock big time :) The App proc is in my code forever now :)

      Delete