One database connection per query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
samfinley
Forum Newbie
Posts: 2
Joined: Sun Feb 22, 2009 12:36 pm

One database connection per query?

Post by samfinley »

Hello,

is it a good idea to open a separate connection to the database for each query?

To elaborate my question: I am using PHP with MySQL. Some time ago, I wrote a class which takes care of the whole querying process for me. That is, I can instantiate the class passing a query to the constructor, and it then establishes a connection to the database, executes the query, closes the connection, and stores the result. It also contains the account details for the database as constants. So it is very convenient to use, I do not need to care about database connections anywhere else in my code.

As my PHP projects grew over time, I wondered if this is the best solution. When it comes to performance, it is probably better to keep one connection open for all queries. But I am not sure if this is really critical for performance. In terms of architecture, what would be the best option to handle a single connection for all queries? To establish the connection at the beginning of my scripts and make it a global variable? To hand it down as a parameter to the functions or objects which need it to execute queries?

Apart from that, are there any other reasons for either of these ways to handle connections? How are you organizing connections currently, and what is the state-of-the-art way to do it?

Thanks for your advice,
samfinley
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: One database connection per query?

Post by josh »

You can use persistent connections but I'd inject the connection object into the query object ( pass object as parameter ). I cant imagine this would be great for performance, I'd try to use 1 connection. Globals are evil
samfinley
Forum Newbie
Posts: 2
Joined: Sun Feb 22, 2009 12:36 pm

Re: One database connection per query?

Post by samfinley »

Thanks for the advice.

Someone mentioned to me to use a singleton. This is a new concept for me, but I looked into it and it might just do what I want (1 database connection but no hassle with passing the connection as parameter) . What do you think of that solution?
Post Reply