I'm using version 4.1. I've recently started supporting Oracle in addition to SQL Server and I'm finding that Oracle (ODP.NET) is far slower than SQL Server for the same operations. I have not worked extensively with ODP.NET so I'm trying to figure out if this is a normal issue with the database or with the driver or something else going on. I'm testing against Oracle 12 XE running locally. It doesn't seem like the server is straining but I don't really know anything about Oracle server configuration or tuning. (I've also tested against an Oracle 21 cloud ATP database, which gives slightly slower performance due to the slowish network connection.)

For example, I have a fetch that returns all 180 records in a table plus a lot of relations using prefetch--about 55 queries total. For Oracle, ORMProfiler shows DB time of 16,246ms and .NET time 29,842ms. The same operation against a SQL Server database that has the same data takes about 1,600ms DB time and 1,800 .NET time. Both databases have the same indexes, and anyway there is not much data in the tables (<200 rows in any table).

A few queries are showing 5 seconds of DB time but execute in <200ms in SQL Developer.

Another query shows 1.89ms in DB time and 21,000ms in .NET time. The query returns 142 rows with 20 columns that are mostly numbers and short strings. In SQL Server it's as fast as you would expect it to be, so it doesn't seem like it's LLBLGen being slow merging the results

In addition to ORMProfiler I did some profiling in ANTS and it does seem like most of the time is being eaten up inside the database driver--so it doesn't really seem like an LLBLGen problem.

Anyone have suggestions on what to look at?

ODP.NET has 2 versions: the CLI one (so the one which wraps the java client) and the managed one (which is a stand alone ado.net provider). The latter is much faster than the former. LLBLGen Pro v4.1 is the first version which supports the managed ODP.NET https://www.llblgen.com/Documentation/4.1/LLBLGen%20Pro%20RTF/hh_goto.htm#Using%20the%20generated%20code/gencode_dbspecificfeatures.htm#oraclemanagedprovider

The Oracle DQE has some configuration settings, which are quite conservative in your llblgen pro version, like they are set for older database versions. So you might want to look into these (but they don't explain the slow performance).

I never found the CLI odp.net perform well at all, it has a ridiculous startup time and the marshalling of data between the java and .net layers isn't helping.

Sorry, I forgot there are two versions. I am using the managed one. I'll take a look at settings you mentioned.

Has anything changed in newer versions of LLBLGen that would affect the Oracle performance? There is a new version of my product in development that moves to the latest LLBLGen, but the affected customer won't be moving to the new version for several years due to their deployment cycle.

We switched to better defaults, removed some wrapping queries for e.g. paging and more efficient joins, but all in all, the sets you describe should be fetched instantly, also with the older setups.