Store large text file to DB using oData

Question

I need to store a very large string into the backend table under one field which is of type string.

The string which I am storing is above 10 million (1 crore) character length. It is taking long time to store and retrieve from the backend.

I tried compressing algorithms,which failed to compress such large string. So what is the best way to handle this situation and improve the performance.

Technologies used:

front end - SAP UI5,
gateway - oData,
backend - SAP ABAP.

Compressing methods tried:

https://github.com/tcorral/JSONC
https://github.com/floydpink/lzwCompress.js

the above compressing methods weren't able to solve my problem.

Does this string come from the Frontend? If so, can you possibly generate it in the backend to store? — hirse
hirse, its coming from the frontend. I am reading an excel sheet, then converting it into the JSON and storing it as string. — saikiran bommagowni
Can't you just upload the excel file and store that? XLSX files are basically ZIPs, so they might be smaller than your JSON. — Marc
Hi Marc, I have to perform some operations on that excel and then store the data,so my requirement is not just storing the files. — saikiran bommagowni
I see no way for things to go well while storing a 10 million character string in a varchar. I would start by changing that to a blob and work from there. — Chris Neve

Suncatcher Suncatcher · Accepted Answer · 2016-11-23T17:23:51

Well, Marc is right stating that transferring XLSX is definitely better/faster than JSON.
ABAP JSON tools are not so rich however sufficient for most manipulations. More peculiar tasks can be done via internal tables and transformations. So it is highly recommended to perform your operations (XLSX >> JSON) on the backend server.

What concerns backend DB table, I support Chris N that inserting 10M string into string field is a worst idea that can be ever imagined. The recommended way of storing big files in transparent tables is utilizing XSTRING type. This is a kind of BLOB for ABAP which is much faster in handling binary data.

I've made some SAT performance tests on my sample 14-million file and that's what I got.

INSERT into XSTRING field:

INSERT into STRING field:

As you can notice DB operations net time differs significantly, not in favour of STRING.

Your upload code can look like this:

DATA: len type i,
      lt_content TYPE standard table of tdline,
      ws_store_tab TYPE zstore_tab.

"Upload the file to Internal Table
 call function 'GUI_UPLOAD'
  exporting
   filename                = '/TEMP/FILE.XLSX'
   filetype                = 'BIN'
 importing
   filelength              = len
 tables
   data_tab                = lt_content
.

IF sy-subrc <> 0.
 message 'Unable to upload file' type 'E'.
ENDIF.

"Convert binary itab to xstring
call function 'SCMS_BINARY_TO_XSTRING'
 exporting
  input_length      = len
  FIRST_LINE         = 0
  LAST_LINE          = 0
  importing
  buffer             = zstore_tab-file        "should be of type XSTRING!
 TABLES
  binary_tab        = gt_content
 exceptions
 failed             = 1
 others             = 2
        .
IF sy-subrc <> 0.
 MESSAGE 'Unable to convert binary to xstring' type 'E'.
ENDIF.

INSERT zstore_tab FROM ws_store.

IF sy-subrc IS INITIAL.
 MESSAGE 'Successfully uploaded' type 'S'.
ELSE.
 MESSAGE 'Failed to upload' type 'E'.
ENDIF.

For parsing and manipulating XLSX multiple AS ABAP wrappers already present, examples are here, here and here.

All this is about backend-side optimization. Optimization on the frontend are welcomed from UI5-experts (to whom I don't belong), however general SAP recommendation is to move all massive manipulation to application server.

Store large text file to DB using oData

1 Answers