1

How to fetch all data required for processing a request in one call to DB. Also I want to know how will I do this if my master page also requires some data from database? I know that its clarified in Passing data to Master Page in ASP.NET MVC. But I think that will make multiple calls to database. Please let me know if it's not like that.

UPDATE
+1 and thanks to all answerers/commenter s for their views and advice.

Community
  • 1
  • 1
IsmailS
  • 10,797
  • 21
  • 82
  • 134
  • Requesting reason for close please... – IsmailS Apr 15 '11 at 06:57
  • 2
    "It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form." – Andrew Barber Apr 15 '11 at 09:31

3 Answers3

3

I think you might be confusing one session per request and SELECT N + 1 scenarios. It is perfectly fine to have multiple calls to the database, however you want to watch out for when you have to do one database call per item in a list. This is most commonly encountered when using lazy loaded lists. The solution is to use eager fetching.

ps. I think the popular solution for getting data to the master page is to use Html.RenderAction in the master page. That way you don't have to worry about master page concerns (like navigation) all over the place.

Ryan
  • 4,303
  • 3
  • 24
  • 24
  • First thing. I don't know what is `one session per request` and `SELECT N +1` so I can't get confused among them. But I would like to know what these are. Can you please elaborate? You said `It is perfectly fine to have multiple calls to the database (for a single request)`. Is it? I'm not sure. If it is so then there is no point of raising this question. – IsmailS Apr 15 '11 at 06:54
  • 5
    It's perfectly fine to have multiple calls to a database per-request. Do you think this very Stack Overflow question, with all its answers, comments, user data, tags, careers info, stats, were all retrieved in a single call to the database? – James Gregory Apr 15 '11 at 09:18
3

I have to agree with the other answers, what is your justification for this?

NHibernate can batch SQL statements using FUTURES, have you read this blog post?

However said I am not sure how you would combine all possibilities for all of your views on your site. If you do come up a solution I would love to see it.

This is not going to be easy and showing a small code example is not possible.

Rippo
  • 22,117
  • 14
  • 78
  • 117
2

It's a bit of a tall order to limit yourself to only one database hit per request. What are your reasons for doing this? Is it just a fun challenge you've set yourself? If it's for optimisation reasons, there are better ways of achieving this: optimising queries, caching etc.

Sunday Ironfoot
  • 12,840
  • 15
  • 75
  • 91
  • Obviously for optimisation only. Caching is not useful as required data will differ a lot. Queries definitely has to be optimised also. That's fine. Even after doing both of the things, don't you think limiting DB calls will further optimize performance? Considering that I make one DB call for master page and another for request specific info, if I get x number of hits on my website then it will make 2x number of calls to DB. If my user base doubles in a year or couple it will increase DB call to 4 folds of the number of requests a year back. Shouldn't that be concerning? – IsmailS Apr 15 '11 at 08:59
  • 2
    It's not quite as simple as that. One call to a database isn't half as expensive as two calls. Connection pooling will negate most of the cost of opening/closing connections, and so the expense is in the network traffic, parsing and executing the query, and then parsing the result set. What's cheaper, two small queries or one large multi-resultset query? Then you also need to consider execution plans, as they're much less likely to be reused for merged queries (because the more queries you merge, the more chance there'll be a variation). It's rarely that simple. – James Gregory Apr 15 '11 at 09:15
  • 2
    While striving to reduce the number of database queries is a noble goal, reducing them down to just one will probably limit your application in some way. "Make everything as simple as possible, but not simpler" - Einstein – Sunday Ironfoot Apr 15 '11 at 09:36
  • Thanks @James you are sounding great. You tried to answer the bigger problem in my mind. I think you could have posted as answer. And very thanks to @Sunday too. – IsmailS Apr 18 '11 at 09:03