Apex Office Print (AOP) - Performance issue with document merge using Converter API - SOLVED

Apex Office Print (AOP) is a great tool for document printing, document conversion and document merging. We have used AOP Converter API to merge pdf documents, and it works great. It was fast to implement and worked very well.

We have observed during testing that we are hitting performance issue with large pdf files. I have used following apporach to overcome this.

Following is the sample AOP API call where we were getting performance issue:

l_blob_return := aop_api_pkg.plsql_call_to_aop(
      p_data_type             => 'SQL',
      p_data_source           => q'!select
      'file1' as "filename",
      cursor
      (select
           '1' as "dummy"
       from dual
      ) as "data"
  from dual!',
      p_template_type         => 'PLSQL',
      p_template_source       => q'[
  declare
    l_return        clob;
  begin
    l_return := '{ ' || ' "template_type": "converter" }';
    return l_return;
  end;]',
      p_output_type           => 'pdf',
      p_output_filename       => l_output_filename,
      p_app_id                => p_app_id,
      p_prepend_files_sql     => q'!select filename, mime_type, file_blob
                                      from <your_table>!',
      p_append_files_sql      => q'!select filename, mime_type, file_blob
                                      from <your_table>!'
  );

Following is the sample AOP API call with improved performance:

l_blob_return := aop_api_pkg.plsql_call_to_aop(
      p_data_type             => 'SQL',
      p_data_source           => q'!select
      'file1' as "filename",
      cursor
      (select
           '1' as "dummy"
       from dual
      ) as "data"
  from dual!',
      p_template_type         => 'PLSQL',
      p_template_source       => q'[
  declare
    l_return        clob;
  begin
    l_return := '{ ' || ' "template_type": "converter" }';
    return l_return;
  end;]',
      p_output_type           => 'pdf',
      p_output_filename       => l_output_filename,
      p_app_id                => p_app_id,
      p_prepend_files_sql     => q'!select filename, mime_type, '<url_to_blob_file>' as url_call_from_aop
                                      from <your_table>!',
      p_append_files_sql      => q'!select filename, mime_type, '<url_to_blob_file>' as url_call_from_aop
                                      from <your_table>!'
  );

Conclusion:

When you pass BLOB content as base64 (basically, you have directly passed the BLOB column in query) which AOP internally converts to base64 and add that into JSON document. This JSON document is the request information to AOP server. Now, due to large document and eventually a large JSON - this will be your bottleneck for the performance issue.

With the small change of passing your BLOB document as a URL (this can be your RESTful API URL) to pass as a BLOB source, your JSON document will be just few kb of size and you will get improved result. Check out my another blog here on how to create RESTful web service using Media Resource to make BLOB content downloadable.

With my test case, it was executing in 10~ second with base64 apporach for a request - which is reduce to 2~ second with URL as a BLOB source method.

Hope this helps.

Regards,
Jaydip Bosamiya
jbosamiya@gmail.com