Should we move away from LLBLGen?
Joined: 16-Jun-2017
Hi MJ,
I recommend to you to dig into some of the following articles:
- Stored procedures are bad, m'kay
- Yay! A new Stored Proc vs. Dyn. Sql battle!
- Interview with Entity Modelling Tool Creator, Frans Bouma
- Product information. Despite it's the product description, I find it very useful to summarize the core benefits of LLBLGen.
- And lastly, it doesn't mean that you can't use SPs in LLBLGen, in fact it's very easy to use them and you can map them to entities, typedviews, DTOs, but it's key to know whether to use them or not.
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. 
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
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
(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 
but it would give you an initial insight in what's going on.)
Joined: 16-Jun-2017
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
Joined: 16-Jun-2017
No worries Hopefully it's a rather easy fix on your side which is beneficial for everyone involved