Hello All! Loading huge excel file 35k lines (api_datasource.importFromFile) - what to do? are there any parallel execution features or some oracle hacks to speed up the process? Or probably there is some max limit number of rows to be proceeded by importFromFile? Regards, Dmitry.

asked 05 Dec '13, 14:02

Dmitry's gravatar image

Dmitry
135
accept rate: 0%


12next »

Hi Dmitry,

There is not a feature to speed up the current process. Also, there is not a predefined limit for the number of rows to be proceeded. Are you experiencing any problem during the import?

Regards,
Ibrahim

link

answered 05 Dec '13, 14:34

Ibrahim%20Sandalli's gravatar image

Ibrahim Sand... ♦♦
1.5k5
accept rate: 25%

Hello, sorry for long reply. Finally I ve managed to load a file using importFromFile tool. 6M Excel file with 35k rows inside loaded for about 10 minutes which is acceptable. But another issue is that is seems that the most of browsers (except Firefox) have some kind of a timeout. I have some further steps after the end of the load (for example a message that everything loaded) but they never fire. If I load a smaller file size - everything goes ok. So the question is if we can commit after some number of rows loaded or/and if there is any workaround for the "browser not responding" issue? Best regards, Dmitry

link

answered 12 Dec '13, 14:28

Dmitry's gravatar image

Dmitry
135
accept rate: 0%

Hi Dmitry would you be able to post your Excel file structure and api_datasource.importFromFile example?

I am interested to compare this as well with your results.

Thanks

link

answered 13 Dec '13, 01:45

downunder's gravatar image

downunder
613
accept rate: 0%

Excel file have simple structure - 11 columns: 1st number, 2nd - date, and 9 strings. 35к rows. Table for import have 11 fields of varchar2(256).

Code:

l_ColumnNames api_datasource.tt_datasourceColumnNames;

l_blob blob;

begin

................

l_ColumnNames(1) := 'FIELD1';

l_ColumnNames(2) := 'FIELD2';

l_ColumnNames(3) := 'FIELD3';

l_ColumnNames(4) := 'FIELD4';

l_ColumnNames(5) := 'FIELD5';

l_ColumnNames(6) := 'FIELD6';

l_ColumnNames(7) := 'FIELD7';

l_ColumnNames(8) := 'FIELD8';

l_ColumnNames(9) := 'FIELD9';

l_ColumnNames(10) := 'FIELD10';

l_ColumnNames(11) := 'FIELD11';

api_datasource.importFromFile(in_datasourceName_tx => 'ds_excel_data', in_file_bl => l_blob, in_fileFormat_cd => api_datasource.FILE_FORMAT_XLS, in_datasourceColumnNames_t => l_ColumnNames);

..................

end;

link

answered 13 Dec '13, 12:42

Dmitry's gravatar image

Dmitry
135
accept rate: 0%

Hello Team, are there any updates regarding "browser not responding" issue? (please see the original question from Dmitry)

Best regards, Anatoly

link

answered 15 Dec '13, 16:52

anatoly4u's gravatar image

anatoly4u
6918
accept rate: 0%

Hi Anatoly,

That seems to be a Google Chrome bug. We are looking for a workaround. We'll update that thread when we have one.

Kind Regards,
Yalim

(16 Dec '13, 09:48) Yalim Gerger ♦♦

sorry for resurrecting this thread but... I have an xls file which is 3mb in size and has 22k rows in it with 10 fields. appears to work, but takes a really long time, 24 minutes is this about normal???

Simon

link

answered 13 Jun '16, 05:00

apacheuk's gravatar image

apacheuk
1391225
accept rate: 0%

edited 13 Jun '16, 05:23

Hi Simon,

24 minutes time for this seems odd to me. Does it include an upload time if you're using an fileUpload component maybe?

If so, is it possible to check what the upload time is by commenting out the api_datasource.importFromFile API call from your procedure?

Kind regards,
Serdar

(13 Jun '16, 08:49) Serdar ♦♦

see info below, couldn't format the code in this comment :)

(13 Jun '16, 09:50) apacheuk

Added some debug to the code to get some timings results below as well as the relevent code...

api_debug.log('uploading file');
v_bl := api_component.getvaluebl('imp_load_tasks2.FileUploader'); 
api_debug.log('file uploaded');
api_datasource.importfromfile('_UPLOADED_TASK_APPLIC1', v_bl, api_datasource.FILE_FORMAT_XLS,     v_datasourceColumnNames_t);
api_debug.log('file imported');
api_debug.log(api_datasource.getrowcount('_UPLOADED_TASK_APPLIC1'));

debug output

USER : 13-06-2016 13:45:57 uploading file
USER : 13-06-2016 13:45:57 file uploaded
USER : 13-06-2016 14:16:09 file imported
USER : 13-06-2016 14:16:09 22011

As you can see its the actual importfromfile that takes the time the actual upload takes no time at all.

link

answered 13 Jun '16, 09:50

apacheuk's gravatar image

apacheuk
1391225
accept rate: 0%

any update on this performance issue?

link

answered 23 Jun '16, 03:49

apacheuk's gravatar image

apacheuk
1391225
accept rate: 0%

Hi Simon,

We're on this issue. I'll let you know as soon as we found something.

By the way, can you share your excel file via e-mail if it is possible?

Kind Regrads Serdar

(23 Jun '16, 06:46) Serdar ♦♦

There is no way for me to do that due to Uk security restrictions, I can probably tell you what the column data types are and how many we have and the total number of rows if that would help?

(23 Jun '16, 07:49) apacheuk

Still have an issue with this and was looking into it again and noticed we are using api_datasource.FILE_FORMAT_XLS as the file format, I noticed that there is also a api_datasource.FILE_FORMAT_XLSX in the api, could just be a red hearing but figured I try it

Whenever I set to api_datasource.FILE_FORMAT_XLSX (save the file in the right format) and try and import I get the following error :-

api_datasource.importFromFile => Invalid import file format parameter "xlsx". Expected "xls".

does the api_datasource.FILE_FORMAT_XLSX option not work?

link

answered 27 Jun '16, 06:35

apacheuk's gravatar image

apacheuk
1391225
accept rate: 0%

been trying to get to the bottom of this performance issue and if it helps here is the out from a trace for the query that I think is causing the the performance issue, I believe this is where is parsing the XML

SQL ID: bh8v3gs8v616h Plan Hash: 2711747855

SELECT CLASS ,PLATFORM ,TASK_ID ,TASK_VERS ,ACCREF ,LASTCOMPLETED ,
  DATECREATED ,DELETED ,DATEDELETED ,OLDLASTCOMPLETED ,
  BDF_DATASOURCEINS_SEQ.NEXTVAL DATASOURCEINSTANCE_OID ,:B3 
  BDF_DBOBJECTACCESSINS_OID ,BDF_DMLORDER_SEQ.NEXTVAL BDF_DMLORDER_NR ,:B2 
  BDF_STATUS_CD ,-1 BDF_SNAPSHOTINS_OID ,ROWNUM BDF_ORDER_NR ,'Y' 
  BDF_FILTERED_YN ,ROWNUM BDF_ORIGINALORDER_NR 
FROM
( SELECT EXTRACTVALUE (VALUE (DATA), '*/CLASS') CLASS ,EXTRACTVALUE (VALUE 
  (DATA), '*/PLATFORM') PLATFORM ,EXTRACTVALUE (VALUE (DATA), '*/TASK_ID') 
  TASK_ID ,EXTRACTVALUE (VALUE (DATA), '*/TASK_VERS') TASK_VERS ,EXTRACTVALUE 
  (VALUE (DATA), '*/ACCREF') ACCREF ,EXTRACTVALUE (VALUE (DATA), 
  '*/LASTCOMPLETED') LASTCOMPLETED ,EXTRACTVALUE (VALUE (DATA), 
  '*/DATECREATED') DATECREATED ,EXTRACTVALUE (VALUE (DATA), '*/DELETED') 
  DELETED ,EXTRACTVALUE (VALUE (DATA), '*/DATEDELETED') DATEDELETED ,
  EXTRACTVALUE (VALUE (DATA), '*/OLDLASTCOMPLETED') OLDLASTCOMPLETED FROM 
  TABLE (XMLSEQUENCE (EXTRACT(:B1 , 'ROWS/ROW'))) DATA)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      2.09       5.42          0      14687      86308           0
Fetch        1   3132.64    4832.89          0          0       2934       29341
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3   3134.73    4838.32          0      14687      89242       29341

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: FIRST_ROWS
Parsing user id: 3216     (recursive depth: 4)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     29341      29341      29341  SEQUENCE  BDF_DATASOURCEINS_SEQ (cr=17390 pr=0 pw=0     time=167987491 us)
     29342      29342      29342   COUNT  (cr=14456 pr=0 pw=0 time=158818446 us)
     29342      29342      29342    COLLECTION ITERATOR PICKLER FETCH XMLSEQUENCEFROMXMLTYPE (cr=14456 pr=0 pw=0 time=158258917 us cost=2 size=20 card=10)
link

answered 28 Jun '16, 06:01

apacheuk's gravatar image

apacheuk
1391225
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×13
×9
×3

Asked: 05 Dec '13, 14:02

Seen: 2,449 times

Last updated: 06 Jul '16, 07:31


© Copyright Gerger 2017