http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Ok, thanks for the reply. So I should be using lazy load here? I'm trying to implement your commons library for your unit of work/session in view pattern. When I was using lazy loading I was getting lazy exceptions, so I suppose this was a fix in the wrong direction. Obviously I'm not the NHibernate expert you are, but I plan to go through everything with your profiler and learn/clean. http://ayende.com/3732/solving-the-select-n-1-problem#comment19http://ayende.com/3732/solving-the-select-n-1-problem#comment19Mon, 26 Jan 2009 16:42:58 GMTYou aren't using lazy load, so it is forcing the load of all the items in sub optimal fashion http://ayende.com/3732/solving-the-select-n-1-problem#comment18http://ayende.com/3732/solving-the-select-n-1-problem#comment18Mon, 26 Jan 2009 16:37:22 GMTSo I guess the best solution is to pass your fetch strategy to your persistence layer wherever you make your calls? I'm using NHprofiler and I'm getting a SELECT N+1 alert .. but im not sure why. My object graph is as follows: AuthUsers have SystemUsers, SystemUsers have Permissions with a many-many (SystemUserPermission) In my mappings, the realtionship is not lazy between either of those entities, and I get the warning. I do see that 4 queries were executed and it looks like it's looping, but my code isn't doing that and it seems to be the result of lazy loading.. SELECT permission0_.SystemUserId as SystemUs1___1_, permission0_.permissionid as permissi2_1_, permission1_.PermissionId as Permissi1_3_0_, permission1_.Name as Name3_0_, permission1_.PermissionDescription as Permissi3_3_0_ FROM Omega.dbo.systemuserpermission permission0_ left outer join Omega.dbo.Permission permission1_ on permission0_.permissionid = permission1_.PermissionId WHERE permission0_.SystemUserId = 89 /* @p0 */ Any suggestions? http://ayende.com/3732/solving-the-select-n-1-problem#comment17http://ayende.com/3732/solving-the-select-n-1-problem#comment17Mon, 26 Jan 2009 16:02:07 GMTOk, thank you. http://ayende.com/3732/solving-the-select-n-1-problem#comment16http://ayende.com/3732/solving-the-select-n-1-problem#comment16Wed, 03 Dec 2008 21:15:51 GMTSteve, That will be two calls. select blog select posts where blogid =? http://ayende.com/3732/solving-the-select-n-1-problem#comment15http://ayende.com/3732/solving-the-select-n-1-problem#comment15Wed, 03 Dec 2008 18:35:57 GMTStupid question: if I have a Blog with a lazy-load collection of Posts. And do: Blog b = GetById(1); foreach(Post p in b.Posts) ... is that foreach calling each - or since I reference the collection, it gets them all at that point - one call ? http://ayende.com/3732/solving-the-select-n-1-problem#comment14http://ayende.com/3732/solving-the-select-n-1-problem#comment14Wed, 03 Dec 2008 17:37:30 GMTOk this is a manual pingback... I've seen quite a lot of people saying that lazy load is evil. But remember when they were saying that automatic memory management was bad ! For sure I hope one day will come where data fetching will totally transparent, with a minor performance impact. This day we'll be able to spend our time on more important things. But today we have to decide between fetch early (performance hit on some scenarii), lazy loads (perf hit on other cases) or no fetch (the data is not available). The 3rd choice can be ok to be Yagni. Fighting 1 vs 2 before reaching a real performance problem is premature optimisation... http://ayende.com/3732/solving-the-select-n-1-problem#comment13http://ayende.com/3732/solving-the-select-n-1-problem#comment13Mon, 01 Dec 2008 21:55:46 GMTThat's why I said "in this case". Ideally you would build the rule meanwhile you come up with more test cases. I just mentioned an initial idea for the first testcase of the rule that would cover the code in this blogspot :) I'd love to start doing Gendarme rules for NH but don't have too much time for it :( http://ayende.com/3732/solving-the-select-n-1-problem#comment12http://ayende.com/3732/solving-the-select-n-1-problem#comment12Mon, 01 Dec 2008 21:28:11 GMTAndres, That is a very simplistic solution. You would need to scan all the code paths to get to that, and that means that you have to do full system analysis. You suggestion would create false positive for code path that just created the objects, or would ignore something that does Select N+1 by stepping through the Parent.Parent.Parent relationship http://ayende.com/3732/solving-the-select-n-1-problem#comment11http://ayende.com/3732/solving-the-select-n-1-problem#comment11Mon, 01 Dec 2008 20:07:57 GMTWell, you would be amazed of the kind of things you can do with Gendarme today (it uses Cecil BTW, now that you mention it), so I'd say it's not so difficult. In this case you would for IL that resembles a for/foreach construct on a property whose name you have not used in a fetch clause. http://ayende.com/3732/solving-the-select-n-1-problem#comment10http://ayende.com/3732/solving-the-select-n-1-problem#comment10Mon, 01 Dec 2008 19:23:19 GMTYes, you need to execute the code in order to get the warning. Trying to do this with static analysis would be extremely complex. The piece of code above is completely valid, but you would need to search for all calling code paths and analyze them to try to figure out what is going on here. Not impossible, but an extremely complex solution In addition to that, those sort of things are usually not something that you have to work hard to reproduce, they are generally very easy to find. http://ayende.com/3732/solving-the-select-n-1-problem#comment9http://ayende.com/3732/solving-the-select-n-1-problem#comment9Mon, 01 Dec 2008 16:19:33 GMTAyende: how does this NH Prof tool work? I mean, does it need to see the app running this code in order to report the warning. If yes, that's a shame because you're doing "lazy loading" of the NH usage of the code in your app :) I mean, with a compile-time tool you would get all the warnings in a row, without trying to reproduce every bottleneck. http://ayende.com/3732/solving-the-select-n-1-problem#comment8http://ayende.com/3732/solving-the-select-n-1-problem#comment8Mon, 01 Dec 2008 15:46:08 GMTFrans, For multi branched graphs, you use other options. I am using this to show the classic issue. Also, you can do eager loading in NH using other options than a join. http://ayende.com/3732/solving-the-select-n-1-problem#comment7http://ayende.com/3732/solving-the-select-n-1-problem#comment7Mon, 01 Dec 2008 15:25:06 GMTPeter, It can, you need to configure it, NH in general doesn't try to be too smart in trying to figure out what the best data access strategy is. That subject is too complex to guess successfully, so you need to tell it how you want it to behave. See batch="50" http://ayende.com/3732/solving-the-select-n-1-problem#comment6http://ayende.com/3732/solving-the-select-n-1-problem#comment6Mon, 01 Dec 2008 15:23:42 GMTIt seems that 'SetFetchMode(propertyPath, FetchMode.Eager)' doesn't take into account the 'SubSelect' fetch mode declared in the mapping, which is usually better than outer-joining. http://ayende.com/3732/solving-the-select-n-1-problem#comment5http://ayende.com/3732/solving-the-select-n-1-problem#comment5Mon, 01 Dec 2008 12:12:47 GMTThat join query is still inefficient, as you can introduce a lot of duplicates for Post which you have to wade through when materializing Post instances. With multi-branched graphs, it gets downhill pretty quickly. http://ayende.com/3732/solving-the-select-n-1-problem#comment4http://ayende.com/3732/solving-the-select-n-1-problem#comment4Mon, 01 Dec 2008 09:19:17 GMTPS: By "why not fetch them" I meant the class in NHibernate implementing the enumerator. http://ayende.com/3732/solving-the-select-n-1-problem#comment3http://ayende.com/3732/solving-the-select-n-1-problem#comment3Mon, 01 Dec 2008 09:01:52 GMTWhen accessing elements by index I can understand why an OPF would want to fetch individual elements, but when you are iterating over them using an IEnumerable it is pretty obvious you are going to be accessing them each in turn so why not fetch them in chunks of 50 or something by default? http://ayende.com/3732/solving-the-select-n-1-problem#comment2http://ayende.com/3732/solving-the-select-n-1-problem#comment2Mon, 01 Dec 2008 09:00:35 GMTThe Select N +1 problem is certainly something commonly used by proponents of stored procedures to dismiss ORMs as being inefficient and suffering from poor performance. Obviously, it takes a careful and somewhat experienced eye with ORMs to recognize when this occurs and how to fix it. Your NHibernate Profiler should hopefully help mitigate this. As an aside, any NHibernate tools in the future to help identify potential security holes such as not using named parameters (another favorite target of ORM opponents)? http://ayende.com/3732/solving-the-select-n-1-problem#comment1http://ayende.com/3732/solving-the-select-n-1-problem#comment1Mon, 01 Dec 2008 08:21:51 GMT