Set InitialLobFetchSize for EF 6 db context

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Set InitialLobFetchSize for EF 6 db context

Post by PeterUser » Tue 26 Apr 2022 08:27

Hi,

I'm using EF 6.4.4 on .Net 6 with Devart.Data.Oracle.EF6 9.14.1382. I have performance issues with NCLOB columns in tables and I read that setting InitialLobFetchSize of the OracleCommand can fix that issue.

Is there a way to set this option for an EF DbContext?

Maybe something like

Code: Select all

// Not working!
OracleEntityProviderConfig.Instance.QueryOptions.InitialLobFetchSize = -1;
Kind regards
Peter

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Set InitialLobFetchSize for EF 6 db context

Post by PeterUser » Wed 27 Apr 2022 12:52

Hello,

I did additional investigation and tried implementing an IDbCommandInterceptor. Unfortunately, the returned DbCommand is not an OracleDbCommand, but a Devart.Data.Oracle.Entity.ag, which does not have an InitialLobFetchSize property.

Is there a change to make this approach work?

Any idea is greatly welcome.

Kind regards
Peter

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Set InitialLobFetchSize for EF 6 db context

Post by PeterUser » Thu 28 Apr 2022 14:54

Hi,

the best idea I found so far is the IDbCommandInterceptor.

@devart: Do you think it is technically possible to add the InitialLobFetchSize property to Devart.Data.Oracle.Entity.ag?

Kind regards
Peter

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Set InitialLobFetchSize for EF 6 db context

Post by Shalex » Thu 28 Apr 2022 16:51

PeterUser wrote: Tue 26 Apr 2022 08:27 I have performance issues with NCLOB columns in tables and I read that setting InitialLobFetchSize of the OracleCommand can fix that issue.

Is there a way to set this option for an EF DbContext?

Maybe something like

Code: Select all

// Not working!
OracleEntityProviderConfig.Instance.QueryOptions.InitialLobFetchSize = -1;
PeterUser wrote: Wed 27 Apr 2022 12:52 I did additional investigation and tried implementing an IDbCommandInterceptor. Unfortunately, the returned DbCommand is not an OracleDbCommand, but a Devart.Data.Oracle.Entity.ag, which does not have an InitialLobFetchSize property.
We will investigate the possibility of implementing both approaches and notify you about the result. There is no timeframe.

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Set InitialLobFetchSize for EF 6 db context

Post by PeterUser » Thu 28 Apr 2022 19:23

Hello Alex,

thanks a lot for your effort. I'm looking forward to your findings.

Kind regards
Peter

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Set InitialLobFetchSize for EF 6 db context

Post by PeterUser » Fri 06 May 2022 09:13

Hello Alex,

is there any information on that issue, yet?

Sorry for being impatient, but we access a database over VPN on another continent and the 80ms roundtrip times make the current solution unusable. So, if you do not see a chance to add that parameter, we will have to consider an alternative technology.

In that sense, any news would be highly appreciated, a "we can do that" would of course be the preferred answer :-)

Kind regards
Peter

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Set InitialLobFetchSize for EF 6 db context

Post by Shalex » Mon 09 May 2022 08:57

We are working on the feature. As soon as it is implemented, we are going to provide you with the internal build of dotConnect for Oracle.

Do you use the NuGet package (https://www.nuget.org/packages/devart.data.oracle.ef6) or the assemblies shipped with the provider installation (C:\Program Files (x86)\Devart\dotConnect\Oracle\)?

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Set InitialLobFetchSize for EF 6 db context

Post by PeterUser » Mon 09 May 2022 09:00

Hello Alex,

that is great news indeed! Thanks a lot for working on that.

I use the Nuget package from Nuget.org, but I also have a local Nuget server here, if that helps for testing.

Looking forward to any news.
Kind regards and thanks again
Peter

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Set InitialLobFetchSize for EF 6 db context

Post by PeterUser » Thu 23 Jun 2022 08:31

Hello,

is there any update on this feature?

Kind regards
Peter

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Set InitialLobFetchSize for EF 6 db context

Post by PeterUser » Tue 12 Jul 2022 13:47

Hello devart,

is there any news on this item?

Kind regards
Peter

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Set InitialLobFetchSize for EF 6 db context

Post by PeterUser » Mon 18 Jul 2022 09:04

Ping

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Set InitialLobFetchSize for EF 6 db context

Post by PeterUser » Tue 26 Jul 2022 16:10

Ping

DmitryGm
Devart Team
Posts: 152
Joined: Fri 11 Dec 2020 10:27

Re: Set InitialLobFetchSize for EF 6 db context

Post by DmitryGm » Tue 02 Aug 2022 17:43

We can offer you pre-release builds with the fixes:

• Implemented IOracleCommandAdapter interface to access OracleCommand in IDbCommandInterceptor in EF6
• Implemented option config.QueryOptions.InitialLobFetchSize for EF4/EF5/EF6 and EF Core
• Fixed bug with InitialLobFetchSize in OracleCommand when calling Clone() method

NuGet packages you can download here:
https://download.devart.com/builds/Nuge ... 0.0.12.zip

PeterUser
Posts: 70
Joined: Mon 27 Feb 2017 07:45

Re: Set InitialLobFetchSize for EF 6 db context

Post by PeterUser » Thu 25 Aug 2022 08:01

Hello Dimitry,

sorry for my late reply after pinging here so often, but I was on holiday and sick leave.

Thanks for the package though. I set the config.QueryOptions.InitialLobFetchSize and this reduces fetching 1000 rows with a CLOB column from ~40 seconds to 0.3 seconds in my specific scenario.

Thanks for implementing that.

I have a suggestion for improvement: It would be great to have XML-comments on all OracleEntityProviderConfig properties. Just bring the texts from the online docu there. That would save some googeling.

Kind regards
Peter

alexsms
Posts: 1
Joined: Wed 07 Sep 2022 15:26

Re: Set InitialLobFetchSize for EF 6 db context

Post by alexsms » Wed 07 Sep 2022 15:28

Is there a net framework version of this pre release?

I am facing the same performance issue with the CLOB types.

Thanks,

Alex.

Post Reply