Single or Multiple Connections to DB

[chadmg]

I've decided to look at server performance and I am wondering whether it is better to open one db connection at the first query of my page and close it after the last query or if I should open and close the db connection with each query (or close group of queries). This is a very heavy database driven website. There are many database queries on each page load, but there is also a lot of other other code that needs to be executed between queries, so there may be an extra server strain by leaving the connection open. I have no idea. There are an unlimited number of client connections that can be made simultaneously, so that is not an issue. But I am thinking that processing time will be different. I can not think of a way to adequately bench test the two methods, so I am asking for your thoughts. Thanks.

[httpwebwitch]

Leave the connection open and reuse it for all your queries. You'll waste more server resources by opening and closing connections repeatedly.

Make sure you streamline and optimize all your queries so the database returns only the minimum of data needed to render the page. make the database do as much of the work as possible, with grouping, joining, ordering, limiting, etc.

[edited by: httpwebwitch at 7:31 pm (utc) on July 8, 2004]

[ergophobe]

check out one of the thread on php performance testing [webmasterworld.com]

That said, I think one connection will result in some time savings, but I'm not sure how much.

[peterdaly]

In general it the best idea to open one connection then resuse it.