Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We use Linq2Sql in an ASP.NET application. Unfortunately the eager-loading in Linq2Sql isn't as powerful as in Entity Framework, so a lot of the data has to be lazy loaded as needed.

Taking connection pooling into account, is it OK for a web page to execute 60 queries to load a page? Executing a single big query probably won't be much better, as those 60 queries will all those connection pooled connections and not open a new connection each time (which I realize is slow).

Any thoughts?

share|improve this question
    
it is NOT OK, if they are not executed in a batch. –  Yusubov Jul 1 '12 at 10:43
    
Just imagine if someone makes a refreshbot, your servers will die at young age. –  alfa64 Jul 1 '12 at 23:08
add comment

3 Answers

up vote 1 down vote accepted

From experience with other systems, I'd say yes connection pooling will kick in and you'll only open 1 connection for all those queries. You should check this though.

That still doesn't solve your performance problems though, even assuming all those queries run sequentially (and therefore reuse a single connection) you're still hitting the DB 60 times. This puts a load on the single shared resource that is your DB. If 100000 people access your page, that's a whole lot of contention on your DB which will undoubtedly cause some problems. So firstly, make sure your DB locking strategy is good so pure reads don't lock everything up, then work on reducing the complexity of your pages.

share|improve this answer
add comment

One thing is what it takes to load one page. Another thing is what it takes to serve X concurrent users per second (which is what the acceptance test hopefully includes).

You will most likely find that the scalability issue will bring the database to its knees and do so very rapidly. If so, you have three options:

  1. Use fewer queries.
  2. Cache database query responses.
  3. Put a frontend like Varnish in front of your application and rewrite your application to enable Varnish to cache as much as possible transparently to you.

My gut feeling is that 60 queries pr page will become a bottleneck, and that your team should set up a load testing system simulating X users which e.g. could run every night, and let you know as soon as possible when the application becomes too slow to be accepted.

share|improve this answer
add comment

Is it OK for a web page to execute 60 queries to load a page?

I would say this is not usual. Why are there 60 different queries on 1 page? Even if you need 60 different queries, you probably could group them in several result sets where each result set uses its own connection and may be run them in parallel or Asynch. pattern (see Using Parallel Extensions for .NET 4 in ASP.NET apps) but using a connection per query is not good INMO.

Also, it may be worthwhile to consider re-designing the interface so that it uses drill-down style where only relevant data is shown when needed.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.