So, I was hoping somebody more knowledgeable would chime in with some insight but I'll take a stab. Most of the data it is getting is necessary but I'm guessing the abundance of queries is just a shortcoming in the LINQ to nHibernate provider. Your original query doesn't go very deep; you start with reviews and just go one level deep to get the Count and SubmitBy info. I'm guessing since your other query is navigating down through follower->leader->review it just can't make the same intelligent decisions. The difference in the way it handles Count points to that for me, but I don't really know what I'm talking about :)
One thing that might help, if I understand your query correctly, is it appears 'leader' is the same thing as 'review.SubmitBy.' This would allow you to change those couple of parameters: UserId = leader.Id AccountType = leader.AccountType, Avatar = leader.Avatar, NickName = leader.NickName, and might knock a level of queries off. I've noticed in my app that a couple of operations execute a fair number of queries but since it hasn't adversely affected performance I haven't worried too much about it. I would advise the same thing. If this really is going to cause issues, you might be better off getting into HQL, QueryOver, etc. or some custom stored procedure and a Dto and AliasToBean, which I've used in a couple of situations. Thanks, Patrick On Thursday, March 22, 2012 12:35:25 PM UTC-5, feelexit wrote: > > here's all 7 queries. > > -- statement #1 > SELECT leaders0_.Follower as Follower1_, > leaders0_.Leader as Leader1_, > user1_.Id as Id1_0_, > user1_.Email as Email1_0_, > user1_.Password as Password1_0_, > user1_.CreateDate as CreateDate1_0_, > user1_.Avatar as Avatar1_0_, > user1_.AccountType as AccountT6_1_0_, > user1_.OtherID as OtherID1_0_, > user1_.NickName as NickName1_0_, > user1_.ParentType as ParentType1_0_, > user1_.Status as Status1_0_, > user1_.Roles as Roles1_0_, > user1_.Point as Point1_0_, > user1_.Ip as Ip1_0_, > user1_.FirstName as FirstName1_0_, > user1_.MiddleName as MiddleName1_0_, > user1_.LastName as LastName1_0_ > FROM Following leaders0_ > left outer join User user1_ > on leaders0_.Leader = user1_.Id > WHERE leaders0_.Follower =44 /* ?p0 */ > > -- statement #2 > SELECT reviews0_.SubmitBy as SubmitBy1_, > reviews0_.Id as Id1_, > reviews0_.Id as Id3_0_, > reviews0_.Title as Title3_0_, > reviews0_.Description as Descript3_3_0_, > reviews0_.SubmitBy as SubmitBy3_0_, > reviews0_.SubmitDate as SubmitDate3_0_, > reviews0_.Category as Category3_0_, > reviews0_.Link as Link3_0_, > reviews0_.Status as Status3_0_, > reviews0_.Age as Age3_0_ > FROM Review reviews0_ > WHERE reviews0_.SubmitBy =55 /* ?p0 */ > > -- statement #3 > SELECT favorites0_.ReviewId as ReviewId1_, > favorites0_.Id as Id1_, > favorites0_.Id as Id4_0_, > favorites0_.ReviewId as ReviewId4_0_, > favorites0_.PostBy as PostBy4_0_, > favorites0_.PostDate as PostDate4_0_ > FROM Favorite favorites0_ > WHERE favorites0_.ReviewId =45 /* ?p0 */ > > -- statement #4 > SELECT votes0_.ReviewId as ReviewId1_, > votes0_.Id as Id1_, > votes0_.Id as Id0_0_, > votes0_.VoteBy as VoteBy0_0_, > votes0_.VoteDate as VoteDate0_0_, > votes0_.Score as Score0_0_, > votes0_.ReviewId as ReviewId0_0_ > FROM Vote votes0_ > WHERE votes0_.ReviewId =45 /* ?p0 */ > > -- statement #5 > SELECT reviews0_.SubmitBy as SubmitBy1_, > reviews0_.Id as Id1_, > reviews0_.Id as Id3_0_, > reviews0_.Title as Title3_0_, > reviews0_.Description as Descript3_3_0_, > reviews0_.SubmitBy as SubmitBy3_0_, > reviews0_.SubmitDate as SubmitDate3_0_, > reviews0_.Category as Category3_0_, > reviews0_.Link as Link3_0_, > reviews0_.Status as Status3_0_, > reviews0_.Age as Age3_0_ > FROM Review reviews0_ > WHERE reviews0_.SubmitBy =45 /* ?p0 */ > > -- statement #6 > SELECT favorites0_.ReviewId as ReviewId1_, > favorites0_.Id as Id1_, > favorites0_.Id as Id4_0_, > favorites0_.ReviewId as ReviewId4_0_, > favorites0_.PostBy as PostBy4_0_, > favorites0_.PostDate as PostDate4_0_ > FROM Favorite favorites0_ > WHERE favorites0_.ReviewId =44 /* ?p0 */ > > -- statement #7 > SELECT votes0_.ReviewId as ReviewId1_, > votes0_.Id as Id1_, > votes0_.Id as Id0_0_, > votes0_.VoteBy as VoteBy0_0_, > votes0_.VoteDate as VoteDate0_0_, > votes0_.Score as Score0_0_, > votes0_.ReviewId as ReviewId0_0_ > FROM Vote votes0_ > WHERE votes0_.ReviewId =44 /* ?p0 */ > > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To view this discussion on the web visit https://groups.google.com/d/msg/nhusers/-/iO2TL2Z9lVcJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.
