For example, you want to insert 100 records into a table with two columns, so you write your insert statement like this:
The '15' and 'Foo' are literal values that you've placed into your SQL statement; they will be different (presumably) for each record that you're inserting. So you would have to prepare a similar statement 100 times (once for each record).my $sth = $dbh->prepare(<<SQL); insert into my_table (id, name) values ('15', 'Foo') SQL
Placeholders allow you to prepare the statement once; instead of using literal values, you just use '?', like this:
When you want to actually execute this statement, you give execute the values to insert:my $sth = $dbh->prepare(<<SQL); insert into my_table (id, name) values (?, ?) SQL
This means that the database has to do much less work. Instead of compiling your statement 100 times, it only has to compile it once.$sth->execute('15', 'Foo');
In order to benefit from this newfound efficiency, you'll have to modify your code slightly. The best way to do this is to use the prepare_cached method instead of prepare. prepare_cached does the same thing as prepare, but first looks to see if the statement has already been compiled; if it has, it returns the compiled version.
So your final code would look something like this:
The other reason to use placeholders is that DBI will automatically take care of any quoting issues for you. You don't need to enclose the ?'s in single quotes, and you don't need to call DBI's quote method on them.my $sth = $dbh->prepare_cached(<<SQL); insert into my_table (id, name) values (?, ?) SQL $sth->execute('15', 'Foo');
For more information, read the DBI manpage and A Short Guide to DBI.