My New Nemesis APEX_JSON Package – Keep Learning Keep Living

Hi,

I think after “triggers”, “unnecessary recursive sql” and “comma separated data stored in one column”, my new enemy is APEX_JSON package. This is a package to parse/build json provided by apex. Even if it is an Oracle Apex provided package, I really don’t like this package. especially if you are parsing/building big json data, it is consuming all the performance. My rule of thumb is: if there is a built in package for the job, then use it. for Json parsing and building actions, use built-in json functions. basic test shows 10x faster execution via built in functions.

set serveroutout on

declare
  v_clob clob;
  s timestamp with time zone := systimestamp;
begin
  
  s := systimestamp;
  
  apex_json.initialize_clob_output;
  apex_json.open_array('arr1');
  for r in (select * from tmp_data) 
  loop
      apex_json.open_object();
      apex_json.write('code', r.code);
      apex_json.write('description', r.description);
      apex_json.write('description2', r.description2);
      apex_json.write('description3', r.description3);
      apex_json.close_object();                    
  end loop;
  apex_json.close_array();  
  v_clob := apex_json.get_clob_output();
  
  dbms_output.put_line(rpad('APEX_JSON package duration: ', 35, ' ') ||(systimestamp-s));
  
  -------------------------------------------------------------------------------------------
  
  s := systimestamp;
  
  select json_Arrayagg(json_object(code, description, description2, description3) returning clob )
  into v_clob
  from tmp_data;

  dbms_output.put_line(rpad('Built in Json funcitons duration: ', 35, ' ') ||(systimestamp-s));
  
end;
/

APEX_JSON package duration:        +000000000 00:00:00.277239000
Built in Json funcitons duration:  +000000000 00:00:00.024480000

set serveroutout on

declare

v_clob clob;

s timestamp with time zone := systimestamp;

begin

s := systimestamp;

apex_json.initialize_clob_output;

apex_json.open_array('arr1');

for r in (select * from tmp_data)

loop

apex_json.open_object();

apex_json.write('code', r.code);

apex_json.write('description', r.description);

apex_json.write('description2', r.description2);

apex_json.write('description3', r.description3);

apex_json.close_object();

end loop;

apex_json.close_array();

v_clob := apex_json.get_clob_output();

dbms_output.put_line(rpad('APEX_JSON package duration: ', 35, ' ') ||(systimestamp-s));

-------------------------------------------------------------------------------------------

s := systimestamp;

select json_Arrayagg(json_object(code, description, description2, description3) returning clob )

into v_clob

from tmp_data;

dbms_output.put_line(rpad('Built in Json funcitons duration: ', 35, ' ') ||(systimestamp-s));

end;

APEX_JSON package duration: +000000000 00:00:00.277239000

Built in Json funcitons duration: +000000000 00:00:00.024480000

if JSON got bigger, difference between built-in and apex_json processing is increased. Also, as far as I understand, apex_json package is using plsql collections which means multiple exeuctions of it will consume PGA for each session. but json functions will be using shared memory so it will be more efficient for multi session execution.

thank for reading.

Leave a Reply Cancel reply