Hi,

Please find the gen pro details I use as below:

• What LLBLGen are you using? - The llbl gen pro version is 3.5 Final, released on May 1st, 2012.

• Are you using Adapter or SelfServicing templateSet? - Adapter

I am executing some queries via gen pro and when I see the executed queries in ORM profiler, it appears that .Net takes a long time to execute even though the underlying SQL queries executes very fast. Please find some screenshots attached for your reference.

Unsure what causes slowness on genpro/.net end. The attachment view contains a CLOB column for resume text which is interpreted as string in .net end. Apart from this there are plenty of calls to gen pro code in the execution call stack, into which I don't have much visibility into. Please guide me on how to troubleshoot this issue and optimize the performance. The queries definitely executes faster from SQL end, something needs to be optimized in genpro, not sure what.

Unsure if we even need to set a transaction on the adapter as I am just reading data from database. If I don't set the transaction, then I get an error - "SET TRANSACTION must be first statement of transaction."

First thing try the latest release of v.3.5.

Walaa wrote:

First thing try the latest release of v.3.5.

Thanks Walaa. We will certainly be upgrading in the near future but for now, we are looking at ways to optimize the performance by analyzing SQL queries, our code as well as look into the best ways of using the genpro framework. We had optimized SQL queries and they execute fast but we are still having issues with the time taken by gen pro framework, not sure if we are doing something wrong here. Hence we need some guidance on how best we can improve performance from .net side, specifically the gen pro code. Please refer to the screenshot attached with the earlier message for more details.

It reads 1 row and that shouldn't take 2 seconds to complete indeed. However it might be the data of that 1 row is huge (e.g. megabytes of data because of a blob/clob) and the time to transport that to the client and read it into the objects is sadly slower than one might think.

Is that the case? You do mention the row has a CLOB, but not the size of the data in this particular row.

Also, if you read the row separately into an entity (so not in a prefetch path but directly using an entity fetch) is it as slow as it is here? If so, if you use a .NET profiler like dottrace (jetbrains) or ants (redgate), you can see where the time is spent in .NET with respect to the query, and it will show you what is slow, e.g. ODP.NET (I assume you use oracle, from the looks of it) or our code.

Otis wrote:

It reads 1 row and that shouldn't take 2 seconds to complete indeed. However it might be the data of that 1 row is huge (e.g. megabytes of data because of a blob/clob) and the time to transport that to the client and read it into the objects is sadly slower than one might think.

Is that the case? You do mention the row has a CLOB, but not the size of the data in this particular row.

Also, if you read the row separately into an entity (so not in a prefetch path but directly using an entity fetch) is it as slow as it is here? If so, if you use a .NET profiler like dottrace (jetbrains) or ants (redgate), you can see where the time is spent in .NET with respect to the query, and it will show you what is slow, e.g. ODP.NET (I assume you use oracle, from the looks of it) or our code.

Yes, I use oracle and the size of the data is a little large in the CLOB column which is used to stored resume with a lot of characters in it. Even if I read the row separately into an entity, the performance is still bad. Please find the screenshot as attached.

Whereas when I queried other entities without a CLOB/BLOB column but with a lot of prefetches, then the performance drops down. Querying them into separate entities performed better but then I would need to build relationships between these entities manually if I do not use a prefetch.

I will check on the tools you suggested but would not ORM profiler be sufficient. I read the call stack and could not find a specific area to target to improve performance. It appeared that we needed to use gen pro framework better to optimize the performance as we had already optimized our code and SQL queries.

Damodar wrote:

I will check on the tools you suggested but would not ORM profiler be sufficient. I read the call stack and could not find a specific area to target to improve performance. It appeared that we needed to use gen pro framework better to optimize the performance as we had already optimized our code and SQL queries.

You could exclude large fields from the fetch operation (ref...), and fetch them later, to fast things.

And yes, ORMProfiler gets you an idea of what queries to improve, but at your .net code, you might need additional tools to spot specific areas to improve, even if they don't use OR/M interaction at all.