Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Huge performance degradation when upgrading to efcore 3.1beta or efcore 2.19.80 when connecting to o

Kenny WangJul 19 2020 — edited Aug 10 2020

we have applications use efcore connecting to oracle exadata 11g. It's been working well before 2.19.80. when i upgrade efcore for oracle to the latest version (2.19.80) or to beta version 3.1, things were getting wrong. Just as the following log shows: it takes nearly 2 minutes to complete one simple query which is unacceptable. before this, it tooks only serveral milliseconds. Can anyone help me?

dbug: Microsoft.EntityFrameworkCore.Database.Command[0]

      2020-07-19 16:25:54.008715 ThreadID:9   (SQL)     OracleRelationalCommand.ExecuteReaderAsync() : SELECT "p"."PRODUCTID" "ProductId", UPPER(LTRIM(RTRIM("p"."M_ALTERNATENAME"))) "JdeProduct" FROM "MESWIPSMT"."CONTAINER" "c" INNER JOIN "MESWIPSMT"."PRODUCT" "p" ON ("c"."PRODUCTID" = "p"."PRODUCTID") WHERE ("c"."CONTAINERNAME" = :barcode_0) and rownum <= 1

dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]

      Executing DbCommand [Parameters=[:barcode_0='?' (Size = 2000)], CommandType='Text', CommandTimeout='0']

      SELECT "p"."PRODUCTID" "ProductId", UPPER(LTRIM(RTRIM("p"."M_ALTERNATENAME"))) "JdeProduct"

      FROM "MESWIPSMT"."CONTAINER" "c"

      INNER JOIN "MESWIPSMT"."PRODUCT" "p" ON ("c"."PRODUCTID" = "p"."PRODUCTID")

      WHERE ("c"."CONTAINERNAME" = :barcode_0)

      and rownum <= 1

info: Microsoft.EntityFrameworkCore.Database.Command[20101]

      Executed DbCommand (95,670ms) [Parameters=[:barcode_0='?' (Size = 2000)], CommandType='Text', CommandTimeout='0']

      SELECT "p"."PRODUCTID" "ProductId", UPPER(LTRIM(RTRIM("p"."M_ALTERNATENAME"))) "JdeProduct"

      FROM "MESWIPSMT"."CONTAINER" "c"

      INNER JOIN "MESWIPSMT"."PRODUCT" "p" ON ("c"."PRODUCTID" = "p"."PRODUCTID")

      WHERE ("c"."CONTAINERNAME" = :barcode_0)

      and rownum <= 1

dbug: Microsoft.EntityFrameworkCore.Database.Command[0]

      2020-07-19 16:27:29.682973 ThreadID:9   (EXIT)    OracleRelationalCommand.ExecuteReaderAsync()

This post has been answered by Alex Keh-Oracle on Aug 7 2020
Jump to Answer

Comments

Mike Kutz

I was under the impression that the file parsing was done via ORDS code (using Apache POI), not APEX (which has to work on XE - which can't use Java)

I'm wondering: Do you need to upgrade ORDS?

I've always used SQL*Developer to adjust the EXCEL2COLLECTION settings.

Although, I prefer to use the XLS2Collection plugin.

I don't have access to a 5.1 that I can play with.  sorry

MK

Marc Sewtz-Oracle
Answer

Hi Denes,

The Excel to Collection functionality no longer works when using ORDS and APEX 5.1. In APEX 5.1 we've changed the page submission to use JSON, which does not work with the current Excel to Collection implementation in ORDS. Unfortunately we don't have an alternative or work around available at this time.

Regards,

Marc

Marked as Answer by Denes Kubicek · Sep 27 2020
Denes Kubicek

Marc,

Thanks for answering. Well, this is bad for us since a couple of applications depend on this feature. I will have to think about the workarrounds here. This is not easy now.

Regards,

Denes Kubicek

Patrick Wolf-Oracle

Hi Denes,

as a workaround, you might want to have a look at https://chrisonoracle.wordpress.com/2013/11/13/read-excel-file-in-plsql-using-java-in-the-db/

Regards

Patrick

Anton Scheffer

This - Process Type Plugin - EXCEL2COLLECTIONS  plugin is a great workaroud                      

Denes Kubicek

Anton,

Many thanks. I could get it working and can use it as an alternative solution until it works again with ORDS.

Regards,

Denes

Scott Wesley

Best PL/SQL plugin ever. (excel2collection)

Anton Scheffer

Thanks for nice words. It's a nice plugin indeed. Not the best though.
I've never used it myself. I prefer less generic plugins which insert the data into a table, not into an APEX collection.

That results into a much better performance, seconds instead of minutes for loading a file with than a few 1000 records. 

Anton

jareeq

Some time ago (i believe ords 2.x and apex 4.2) xls2collection was broken. After that was fixed to finally work with xls and xlsx (apex 5.0.2 and listener 2.0.9) and worked (not as efficient as in listener 1.x but it was complete out of the box solution)

Now in 2017 (apex 5.1 and ords 3.x) we go back to 2011 and need to use workaround to keep function live.

I'm always impressed how apex/ords new versions broke old functions without a word.

Without Anton we would have been beaten.

jareeq

After reading this Let's Wreck This Together...with Oracle Application Express!: excel2collection functionality of ORDS will not work with … 

Will Anton plugin work without ords support for excel import ?

Mike Kutz

Please start a new question.

thanks,

MK

1 - 11

Post Details

Added on Jul 19 2020
11 comments
2,015 views