I hope you guys wont mind my asking an off-topic and naive question. But, I could not find any satisfying answer for this. Maximum, how many queries on an average a page must be allowed to process for rendering output(I am looking for the quality aspect). For example in an e-commerce application, 4 to 5 queries on average are required. The minimum the number of queries, shorter is its response time. Hence the question. This question is irrespective of any programming platform. But, lets consider PHP and MySql as an example.
Asked
Active
Viewed 488 times
1
-
I think there is no average number of queries required. To achieve the best performance, the fewer the queries, the better performance. – Irvin Nov 10 '16 at 08:27
-
I think 1 is good. – Strawberry Nov 10 '16 at 08:34
-
How long is a piece of string – Nov 10 '16 at 08:56
-
Too many variables here. – Progrock Jun 07 '18 at 10:49
3 Answers
2
A single complex query that takes half a second to execute is less optimal than running 5 simple queries that take a total of 1/10 of a second to complete.
Having a higher number of simple queries may also be a trade-off in order to keep your code more organized. ORMs, for example, often wind up making a higher number of database calls than if you were manually writing the queries, but they make the application substantially simpler to develop.
Ultimately, though, any application that's complex enough to need to worry about the number of database calls is complex enough that it should really be utilizing a caching layer wherever possible anyway. As long as there are no pages that take a long time to load, it's not really worth nitpicking the exact number of queries.
Ben Chamberlin
- 671
- 4
- 18
1
Actually, It's difficult to say how many queries, a page must fire. It depends on following factors :
Complexity of Application
Data shown in a web page in a big, complex and data-oriented application is large in quantity, compared to normal web pages. Thus, it may requires you to fire more number of queries to get data.
Types of Queries
You may opt to fire more queries to get individual piece of data OR You can use complicated Join to fetch all the data from multiple tables in one go. Thus, here you are making tradeoff between no. of db calls VS Amount of Processing and memory, a Complex JOIN requires. If the tables you are joining are not very large, then join is the best option. But, If your tables are large, then it may make more sense to make individual call to db to fetch data.
Deployment Configuration
In case when you application server and db server are configured on the same machine, then making multiple queries may not have big significance. But, if your db server and application server are located at different places, then minimum queries must be used or single queries with more joins must be used, to avoid network latency.
Mangu Singh Rajpurohit
- 10,806
- 4
- 68
- 97
0
I have developed many web pages with up to 50 queries per page. All are acceptably fast. The trick is not the "number of queries", but their "speed". In the best of situations, a query takes under 10 milliseconds. 50 of those is still under half a second. In such a page, the rendering of the page is likely to take longer than that. That is, MySQL is not the bottleneck.
How to speed up queries? Simple queries. Point queries. Good indexes (often 'composite').
How to slow down queries? EAV. "Showing 10 results out of 123456". Table scans.
Want more details? Let's see some queries.
Rick James
- 135,179
- 13
- 127
- 222