How many SQL queries for an HTTP query is optimal?

Question

I know that the answer to this question for the most part is “Does it depend”, however I wanted to see if anyone has pointers.

We execute requests for each request in ASP.NET MVC. For each request, we need to obtain information about user rights and various data for the displayed views. How many of them are too many, I know that I must be aware of the number of requests that I perform. I would suggest that if they are small queries and are optimized, should half a dozen be okay? I'm right?

What do you think?

Answer 1

• Simplify your site’s design while it’s as simple as possible while still satisfying your customer requirements.

• Cache information that can be cached.

• Preloading information into the cache outside of the request, where you can.

• Ask only the information you are in this request.

• If you need to make many independent requests for one request, compare the load as much as possible.

What you have left is the "optimal" amount for this site. If it is too slow, you need to consider the above again.

User rights information can be cached, as well as other general information that you display everywhere.

You can probably avoid caching more than you need. For example, you can probably cache live information, such as product inventory levels and the user's cart. Use SQL change notifications so you can expire and refill the cache in the background.

Answer 2

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 this part. Of course, reducing queries will save you time, but it also optimizes those queries that you need to fulfill.

You can spend the whole day 50% of the time by spending a request on it, which took only 2 milliseconds to start, or spend 2 hours deleting some INNER JOINS , as a result of which another request took 10 seconds. Review what happened before you begin optimizing.

Answer 3

The optimal amount will be zero.

Given that this is most likely impossible to achieve, the only reasonable statement is "As little as possible."

Answer 4

As less as possible.

Use caching to search. Also save some light data (e.g. permissions) in the session .

Answer 5

You can make as many requests as you like until your site gets too slow.

Answer 6

Q: Do you have a performance problem related to database queries?

Yes? A: Less than you have now.
No? A: The exact number you have now.

If it does not break, do not correct it.

While refactoring and optimizing to save a few milliseconds is a fun and intellectually useful way for programmers to waste time, it is often a waste of time.

In addition, changing the code to combine database queries may be related to the simplicity and convenience of maintaining your code. That is, although it is technically possible to combine several requests into one, which may require eliminating the conceptual isolation of business objects in your code, which is bad.

Answer 7

How much is needed, but no more.

In other words, performance bottlenecks will come not from the number of queries, but from what you do in the queries and how you process the data (for example, caching a huge but static result set can help).

Answer 8

Along with all other recommendations that require fewer trips, it also depends on how much data will be received on each round trip. If it's just a few bytes, then maybe it will be chatty, and performance won't hurt. However, if every trip returns hundreds of kilobytes, then your performance will hurt faster.

Answer 9

You answered your question " It depends ."

Although the attempt to justify the optimal number of requests per HTTP request is not legible. If your SQL server has real good hardware support, you can run a large number of queries in less time and have a real low turnaround time for an HTTP request. So basically, “it depends,” as you correctly said.

Answer 10

As you can see from the above comments, some caches are probably appropriate for your situation. And, as your question, the real answer is "it depends." Generally, the fewer the requests, the better, since each request has an associated cost. You should examine your data model and the requirements for your application to determine which is appropriate.

For example, if user rights are likely to be static during a user session, it makes sense to cache the rights data, so fewer requests are required. If aspects of the data displayed in your view are also static for a user session, they can also be cached.