Performance issue with large object table type

Performance issue with large object table type

Postby anibri » Wed 24 Dec 2014 17:36

Hello,
i've a function that turn very very slow when i send an object table type to a stored procedure with 2500 records using dev art

it works fine if i send only 500 or even 1000.

for 1000 it takes about 8 minutes. with 2500 it takes about 3h30 min !!

Any idea please ?
anibri
 
Posts: 2
Joined: Wed 24 Dec 2014 17:31

Re: Performance issue with large object table type

Postby anibri » Thu 25 Dec 2014 08:56

Hello,
i've tested with an empty stored procedure that does nothing and still taking very very long if i passe an table object with 1500 records .

Code: Select all
create or replace
PROCEDURE ADD_MEP_FROM_CSV(
  MEP_HEADs in  MEP_HEAD_TYPE_TAB,
  MEP_SKUs in  MEP_SKU_TYPE_TAB,
  MEP_LOCs in MEP_LOC_TAB,
  USER_NAME varchar,
  MEP_IDs out  varchar,
  ArticleMagasinError out varchar,
  cur_OUT OUT sys_refcursor
  ) AS
BEGIN


open cur_OUT for 
  select * from TEMP_CSV_EXPORT;
END ADD_MEP_FROM_CSV;


where TEMP_CSV_EXPORT is an enty table !!
MEP_SKUs contains 1500 records. the execution take more than 30 minutes !!

Any idea please ?

Regards
anibri
 
Posts: 2
Joined: Wed 24 Dec 2014 17:31

Re: Performance issue with large object table type

Postby Pinturiccio » Fri 26 Dec 2014 14:24

We could not reproduce the issue. We created an object type, and then а table of object type.
Next, we passed the parameter with the table of object type and with the value “50000 objects” to the indicated procedure. In our environment, the procedure was executed in 1 or 2 seconds.

To reproduce the issue, please provide the entire scripts of all procedure objects. Also, please create and send us a small test project, if possible.
Pinturiccio
Devart Team
 
Posts: 2026
Joined: Wed 02 Nov 2011 09:44


Return to dotConnect for Oracle