do( ) Versus prepare( )
As we mentioned in a previous section,
the do( )
method supplied by the DBI makes executing
non-SELECT
statements much simpler than repeatedly
preparing and executing statements. This is achieved by simply
wrapping the prepare and execute stages into one composite method.
There is a drawback to doing this, however: performance. If you
invoked do( )
repeatedly to insert a huge number
of rows into a table, you could be preparing a statement handle many
times more than is required, especially if the statement contained
placeholder variables. For example, the following script inserts some
rows into the megaliths
table:
### Iterate through the various bits of data... foreach $name ( qw( Stonehenge Avebury Castlerigg Sunhoney ) ) { ### ... and insert them into the table $dbh->do( "INSERT INTO megaliths ( name ) VALUES ( ? )", undef, $name ); }
Internally, what happens is that for each row being inserted, a new statement handle is created, and the statement is prepared, executed, and finally destroyed. Therefore, this loop has four prepare calls, four executes, and four destroys. However, since we’re using a bind value for each loop, the database will likely need to parse the statement only once and use that statement again from the Shared SQL Cache. Therefore, in essence, our program is “wasting” three prepares of that statement.
This is a rather inefficient process. In this case, it would be better to hand-prepare and re-execute the statement handle for each iteration ...
Get Programming the Perl DBI now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.