How many SQL queries per HTTP request is optimal?

Question

I know the answer to this question for the most part is "It Depends", however I wanted to see if anyone had some pointers.

We execute queries each request in ASP.NET MVC. Each request we need to get user rights information, and Various data for the Views that we are displaying. How many is too much, I know I should be conscious to the number of queries i am executing. I would assume if they are small queries and optimized out, half-a-dozen should be okay? Am I right?

What do you think?

Answer 1

Premature optimization is the root of all evil :)

First create your application, if it is sluggish you will have to determine the cause and optimize that part. Sure reducing the queries will save you time, but also optimizing those queries that you have to do.

You could spend a whole day shaving off 50% time spend off a query, that only took 2 milisecond to begin with, or spend 2 hours on removing some INNER JOINS that made another query took 10 seconds. Analyse whats wrong before you start optimising.

Answer 2

The optimal amount would be zero.

Given that this is most likely not achievable, the only reasonable thing to say about is: "As little as possible".