Carping About DBI - Speed Demon (
Page 6 of 10 )
The single greatest performance boost you can realize with DBI, is through the intelligent use of prepare() and execute() functions. Whenever you find yourself using a particular SQL statement over and over again, prepare() it and call it using execute().
However, it rarely happens that the very same query, with no difference at all, is executed over and over again. For example, suppose you're trying to INSERT some data into the database. The INSERT statement may remain similar in form, but the data values will keep changing every time. In this case, prepare() and execute() must be used in combination with DBI "placeholders", as demonstrated below.
$sth = $dbh->prepare("INSERT INTO pets VALUES (?,?,?)");
$sth->execute($name, $species, $age);
The questions marks you see in the prepare() statement are placeholders, and they function just like variables; they indicate where the data values for the current query will be placed later. The data values themselves come from the subsequent execute() statement. Typically, the execute() statement is placed in a loop and the variables $name, $species and $age change every time. The data could be entered interactively at the command prompt, read in from a file, or injected intravenously.
It's also important to be careful about the number of open database connections while using DBI. All databases have a finite limit on the number of concurrent client connections they can handle. If the maximum number of concurrent clients is unspecified, it may make more sense to open up a new connection only when required and to shut it down as soon as possible. This is necessary to ensure that there is never a time when the database server is so heavily loaded that it cannot accept any new connections and is forced to turn away client programs.
Unfortunately, opening and closing connections is computationally expensive and time-consuming. You'll have to find a balance between keeping connections open, and loading the server. This is something that you can't apply in a cookbook fashion; it depends on many variables and the final compromise will differ from implementation to implementation.
This article copyright Melonfire 2001. All rights reserved.
/ 1 
