[adamsmith]

I'm trying to get a general rule of thumb on how many SQL connections should be used at max on each page. My website's profile page at the moment is using about 10-12 connections in order for it to display all the info it needs, I was wondering if this was too many.

Also, is there a way to reduce the number of connections but still retrieving the same data?

This post has been edited by adamsmith: 02 January 2012 - 10:03 PM

[Jay Gilford]

One connection should be all you need. You can run queries multiple times using the same connection. So if you use 10-12 for one page load, then you're using 9-11 too many!

[adamsmith]

Jay Gilford, on 02 January 2012 - 11:32 PM, said:

One connection should be all you need. You can run queries multiple times using the same connection. So if you use 10-12 for one page load, then you're using 9-11 too many!

I'm sorry, I meant queries, I'm using one connection per page but there are 10-12 queries, i guessed that each query was almost like a connection to the database each time...

[Jay Gilford]

It depends on what data you are pulling from the db. You're going to have to provide more information on the tables and structures

[Nitin Reddy]

adamsmith, on 03 January 2012 - 01:21 AM, said:

I'm sorry, I meant queries, I'm using one connection per page but there are 10-12 queries, i guessed that each query was almost like a connection to the database each time...

You can never really tell how many queries is too many because you can have a web page that looks like the following:
* A banner image on top from an ad manager script
* The site title pulled from a CMS table
* The home page content obtained from a CMS table
* A sidebar newsfeed obtained from querying a table containing blog posts
* A footer containing links obtained from another CMS table

These are 5 queries right here so if you compare that to a static HTML page that has no database interaction at all, you are looking at two very different pages right there. You can also have a 'chat' page that makes a database query every 20 seconds so there could be over a hundred database queries being made by that page (though, ideally, one shouldn't built a chat application in this manner the world is less than perfect).

If you aren't experiencing any kind of a performance problem or foresee a performance problem in future, you probably don't need to bother about the number of queries or the number of connections because the effort you put into it may not really pay off... though if you can do it right, you should, as it would be a great learning experience.

[adamsmith]

Cheers, thanks for the help guys.