You can also benefit from persistent connections by replacing prepare( ) with prepare_cached( ). That way you will always be sure that you have a good statement handle and you will get some caching benefit. The downside is that you are going to pay for DBI to parse your SQL and do a cache lookup every time you call prepare_cached( ). This will give a big performance boost to database servers that execute prepare( ) quite slowly (e.g., Oracle), but it might add an unnecessary overhead with servers such as MySQL that do this operation very quickly.

Be warned that some databases (e.g., PostgreSQL and Sybase) don't support caches of prepared plans. With Sybase you could open multiple connections to achieve the same result, but this is at the risk of getting deadlocks, depending on what you are trying to do!

Another pitfall to watch out for lies in the fact that prepare_cached( ) actually gives you a reference to the same cached statement handle, not just a similar copy. So you can't do this:

my $sth1 = $dbh->prepare_cached('SELECT name FROM table WHERE id=?');
my $sth2 = $dbh->prepare_cached('SELECT name FROM table WHERE id=?');

because $sth1 and $sth2 are now the same object! If you try to use them independently, your code will fail.

Make sure to read the DBI manpage for the complete documentation of this method and the latest updates.