0

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?

2
  • it is NOT OK, if they are not executed in a batch.
    – Yusubov
    Jul 1, 2012 at 10:43
  • Just imagine if someone makes a refreshbot, your servers will die at young age.
    – alfa64
    Jul 1, 2012 at 23:08

3 Answers 3

1

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.

6

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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