Hello We are optimizing an application that uses nhibernate and we have noticed that the many to many mapping joins a little too much and load two much data from the database. For example we have a User table and a Role table and a many to many relation between the two tables. If we want to get the first 10 users of a role, we can write the following linq request: var arr = session.Query<User>().Where(u => u.Roles.Contains(r)).Take(10); But the generate sql has a double join, which is unnecessary, it doesn't need to join the Role table, the id of the role is already in the mapping table, why this double join? exec sp_executesql N'select TOP (@p0) user0_.Id as Id8_, user0_.Username as Username8_ from [User] user0_ where exists (select role2_.Id from RoleToUser roles1_, [Role] role2_ where user0_.Id=roles1_.User_id and roles1_.Role_id=role2_.Id and role2_.Id=@p1)',N'@p0 <role2_.Id=@p1)>int,@p1 uniqueidentifier' ,@p0=10,@p1='59B05DEC-D8B2-4FEE-ABEB-9C5300F9D067'
Moreover we would like to use the caching system of nhibernate to reduce the requests to the database, but when you access the roles collection of the user, nhibernate joins again the two tables and so it loads again the roles from the database. exec sp_executesql N'SELECT roles0_.User_id as User2_1_, roles0_.Role_id as Role1_1_, role1_.Id as Id6_0_, role1_.Name as Name6_0_ FROM RoleToUser roles0_ left outer join [Role] role1_ on roles0_.Role_id=role1_.Id WHERE roles0_.User_id=@p0',N'@p0 <roles0_.User_id=@p0> uniqueidentifier',@p0= 'C4F98BC6-3485-4088-8EE7-9C5300F9D083' Fetching the roles is neither better because it uses the same technique and it join again the two tables, moreover with a where clause it generate four joins, two for the where clause and two for the fetching, what is a big overhead. var arr = session.Query<User>().Where(u => u.Roles.Contains(r)).Take(10). Fetch(u => u.Roles); exec sp_executesql N'select TOP (@p0) user0_.Id as Id8_0_, role2_.Id as Id6_1_, user0_.Username as Username8_0_, role2_.Name as Name6_1_, roles1_.User_id as User2_0__, roles1_.Role_id as Role1_0__ from [User] user0_ left outer join RoleToUser roles1_ on user0_.Id=roles1_.User_id left outer join [Role] role2_ on roles1_.Role_id=role2_.Id where exists (select role4_.Id from RoleToUser roles3_, [Role] role4_ where user0_.Id=roles3_.User_id and roles3_.Role_id=role4_.Id and role4_.Id=@p1)', N'@p0 <role4_.Id=@p1)> int,@p1 uniqueidentifier',@p0=10,@p1= '59B05DEC-D8B2-4FEE-ABEB-9C5300F9D067' Is it possible to configure nhibernate to load the many to many collections only with object proxies with their ids (something like session.get)? So that accessing the many to many collection just retrieves the ids from the many to many table? Only after when you access the properties of the object proxy retrieve the object from the database and if the object is already in the cache return this one? Or do you have to convert the many to many relation in two one to many relations? -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/nhusers?hl=en-US. For more options, visit https://groups.google.com/groups/opt_out.
