Minimize Your Database Connection Time
One of the techniques that I learned and used way back when I did mostly ASP development was to:
- connect to the database only when I needed to,
- retreive only (and all of) what I needed, and then
- disconnect from the database as soon as possible.
In working with ASP and MS SQL Server, following the three steps was easy. I'd open my connection and then build my query to return one or more recordsets. Then, I'd use getRows to pull all of the data into a multi-dimensional array. After that, I'd close the connection. This meant from opening the connection to closing it, I usually only had a few lines of code. With all my data now in ASP, I could manipulate it and pass it on to my template.
PHP and MySQL can and should be used in a very similar fashion. There's a little extra grunt work in that you have to make multiple queries and loop through each recordset to get it into arrays but the benefits are worth it. Jay Pipes' recent blog post on MySQL Connection Management sheds some light on this.
MVC frameworks should be designed to do this well. The request comes in to the controller and it queries the model for any data it needs. The model should then be responsible for either pulling from the cache or connecting to the database and retreiving the data at that time. Once the controller has all the data it needs, it can close the connection to the database and then proceed to sending the data on to the view.
Minimizing your connection time is an approach you should take for any external resource, be that reading from files or downloading data from a remote server.