I am working on implementing use of the mysql class found here in an existing script. The script almost always needs to interact with the database, even if there are times when it does not. What is the best practice in this case? Should I open a connection and keep that open until the end of the script or should I open a connection when I need one, closing it when I'm done, to avoid opening a connection when the script does not need it?
Source: Tips4all
Because connections are rather expensive, as others have pointed out, I'd recommend using a "lazy connect" technique in your database layer. If you have structured your application effectively, your application logic should not be concerned with when connections are opened and closed as this would be encapsulated in the database layer. The database layer, when asked to perform a query, would first check to see if it has an active connection and if not, create one. This way you'll avoid opening connections that are never used and you'll also have a nice separation of logic between your application and the database code.
ReplyDeleteWell, if you are using a class, the connection should be opened automatically when you instaciate the class, or when the first query is performed. If you never use the class, the connection wouldn't be opened. While it is good practice to close it when you don't need it, it doesn't hurt to let it be closed when the request thread dies.
ReplyDeleteThis can be bad if you don't have a resource limits set in your php.ini file, the request could possible live forever and never close the connection.
If you have a medium to high traffic site, you should be thinking about using mysql_pconnect anyways so there is always a connection open and you don't need the overhead of opening one on every request.
Usually you'd only want to open a connection to your database when you need to use that connection. Keeping connections open can increase the chance that part of your code will accidentally, or maliciously through the actions of others, cause unwanted queries to be performed on the database.
ReplyDeleteThat being the case, you should only open the connection before you want to run your queries. If you have a large number of queries, try to open your connection as late in the process as possible.
It is better to have one connection left open for a longer duration than to open and close multiple connections.
If your code is performance-sensitive, then the preferred technique tends to be to use some form of connection pooling and/or persistent processes so that you can open one database connection and then use that connection to service many page requests rather than opening a new connection for each request that needs one.
ReplyDeleteIf your code is not performance-sensitive, then it doesn't really matter anyhow.
Either way, the exact timing of when the database is accessed in the course of handling a specific request isn't that great of a cause for concern.
My personal practice is to open a database connection immediately when a new handler process is spawned, then verify that it's still alive when I start processing each request. The rest of the code is then free to just assume that the connection is available when needed without incurring the cost of connecting while a user is waiting for a response.