Should we move away from LLBLGen?

Posts   
 
    
MJ_01
User
Posts: 17
Joined: 16-Jun-2017
# Posted on: 18-Sep-2018 17:36:04   

My boss wants to move away from using llblgen because our code causes the database to receive a large amount of calls. I tried explaining to him that this is because llblgen works by avoiding duplicate data. He would rather use a stored proc that gets all the data in a single call even if most of it is duplicated. Also stored procs can be optimized over time through a query execution plan

Are there any other benefits of using LLBLGen? Any good arguments I can make?

Instead of my Collection.GetMulti() call making 50 calls to the db, is it possible to change a setting to have it make one call even if most of the data is duplicate?

I'm trying to make a case for LLBLGen other than it works great for me and it avoids duplicate data.

Thanks.

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 19-Sep-2018 07:24:20   

Hi MJ,

I recommend to you to dig into some of the following articles:

So, you shouldn't drop it, but you have to understand why, so learning it and using it is the best way IMHO. Hope that helps. simple_smile

David Elizondo | LLBLGen Support Team
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 19-Sep-2018 09:35:06   

MJ_01 wrote:

My boss wants to move away from using llblgen because our code causes the database to receive a large amount of calls. I tried explaining to him that this is because llblgen works by avoiding duplicate data. He would rather use a stored proc that gets all the data in a single call even if most of it is duplicated. Also stored procs can be optimized over time through a query execution plan

You won't get less calls when moving to procs, if you want less calls you should write queries which pull more data at once, everything a proc can do is also possible in an llblgen pro query so I don't see why a proc would help here. A proc isn't an equivalent either: it's just a query. How are you going to handle the resultset? Or sets? How are you going to deal with changes?

Please see below for what I think is your problem though, so it's fixable simple_smile

Execution plans are created from dynamic sql as well and optimized/reused. There's no difference with a proc, so that's a myth.

Are there any other benefits of using LLBLGen? Any good arguments I can make?

Instead of my Collection.GetMulti() call making 50 calls to the db, is it possible to change a setting to have it make one call even if most of the data is duplicate?

GetMulti makes 1 call to the database (or if you specify a prefetch path it will perform 1 query per node, but a prefetch path of 50 nodes is rare, so I guess it's not that), so if you see 50 calls, you have a SELECT N+1 problem, which is caused by lazy loading, I think. If you're on 5.3 or higher, you can switch off lazy loading completely: https://www.llblgen.com/Documentation/5.4/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/SelfServicing/gencode_usingentityclasses_lazyloading.htm#how-to-switch-off-lazy-loading-completely

If not, you should look into defining a prefetch path which fetches the data eagerly instead of with lazy loading. The queries are coming from calls reading the properties of the fetched entities using the GetMulti() call. So you should be able to fix this yourself and gain a lot of performance. SELECT N+1 is a problem that has a solution, without needing to use procs.

So let's investigate this problem further so you can solve it on your end and remove the only argument your boss has: the large amount of queries simple_smile

(btw, if this app is on .net full, you can use ORM Profiler for finding which queries are SELECT N+1 queries https://www.ormprofiler.com. You can try a trial first if you just want to find them quickly. Not to push you into using yet another product, your boss likely won't buy it wink but it would give you an initial insight in what's going on.)

Frans Bouma | Lead developer LLBLGen Pro
MJ_01
User
Posts: 17
Joined: 16-Jun-2017
# Posted on: 19-Sep-2018 17:50:51   

Daelmo, I'll definitely check those out today. Thanks!

Otis, I had no idea that execution plans are created from dynamic sql. That's good to know! As far as all my calls, I'm getting 42 from a single GetMulti call with a branching structure of 19 PrefetchPaths. This process compiles data from many different tables. It used to mostly all be lazy loaded. We've gone from tens of thousands of calls per this process to under 50. But he wants it all done in a single call for some reason despite the fact that the data returned would be over 90% duplicated data.

We actually do have the orm profiler. We actually just upgraded our LLBLgen license to v5.3 only a few months ago. I haven't used the profiler yet mostly because a lot of our code causes lazy loading and I've been fixing as I've had time. I've been using


  <system.diagnostics>
    <switches>
      <add name="SqlServerDQE" value="4"/>
    </switches>
  </system.diagnostics>

to help count Generated Sql queries and review the generated queries. I'm not sure if the profiler is easier to use in this regard.

Yeah, I'm just going to tell him that he's wrong. He's a great guy but I think he's stuck in the 80's and it's unreasonable for him to expect me to put myself at a disadvantage.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 20-Sep-2018 05:40:46   

I believe you are double counting.... Counting queries preparations as well as excited queries. Use the profiler to give you a clear view.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 20-Sep-2018 09:56:45   

MJ_01 wrote:

Otis, I had no idea that execution plans are created from dynamic sql. That's good to know!

Yeah for the database they're the same.

As far as all my calls, I'm getting 42 from a single GetMulti call with a branching structure of 19 PrefetchPaths. This process compiles data from many different tables. It used to mostly all be lazy loaded. We've gone from tens of thousands of calls per this process to under 50. But he wants it all done in a single call for some reason despite the fact that the data returned would be over 90% duplicated data.

We actually do have the orm profiler. We actually just upgraded our LLBLgen license to v5.3 only a few months ago. I haven't used the profiler yet mostly because a lot of our code causes lazy loading and I've been fixing as I've had time. I've been using


  <system.diagnostics>
    <switches>
      <add name="SqlServerDQE" value="4"/>
    </switches>
  </system.diagnostics>

to help count Generated Sql queries and review the generated queries. I'm not sure if the profiler is easier to use in this regard.

I think Walaa is right, you're counting queries twice (a subquery is present in the output of that tracer for instance, but never executed as it's embedded later on in another query). if you want to see the executed queries, use ORMPersistenceExecution (I think level 3 is enough) instead. There you'll get the queries which are actually executed.

Enabling the profiler is just 1 line of code, at the startup of you app (InterceptorCore.Initialize("My app"); ) and 1 nuget package, and it will start intercepting. Then just start the client on the same machine and it will receive the queries. (has to be the same box). Best is if you have the app launched, orm profiler is running, and you have the real time tab open, just start the feature that's causing a lot of queries, then pause the profiler, so you have a set of queries and where they're originating. From there it should be rather easy to track down where they're coming from and you can then update them.

Yeah, I'm just going to tell him that he's wrong. He's a great guy but I think he's stuck in the 80's and it's unreasonable for him to expect me to put myself at a disadvantage.

A flat set can sometimes be faster than a graph of entities. There's nothing against fetching a flat set using some joins, unless you end up with a lot of duplicates due to the joins which usually is slower. The question is what are you going to do with the data? If you're going to process it like a set of entities in a graph, the flat set is slower.

Good luck simple_smile

Frans Bouma | Lead developer LLBLGen Pro
MJ_01
User
Posts: 17
Joined: 16-Jun-2017
# Posted on: 20-Sep-2018 17:25:00   

ah ok. Thanks all for your help and feedback. I'll definitely look into the profiler as soon as I am done with these next couple of projects. I look forward to seeing the results and I'll be sure to post my findings here when I am finished.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 20-Sep-2018 17:56:30   

No worries simple_smile Hopefully it's a rather easy fix on your side which is beneficial for everyone involved simple_smile

Frans Bouma | Lead developer LLBLGen Pro