One database connection per query?
Posted: Sun Feb 22, 2009 1:06 pm
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
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