Programming the Perl DBI

Bind Values Versus Interpolated Statements

So, why use bind values? What’s the real differences between these and interpolated on-the-fly SQL statements?

On the face of it, there’s no obvious difference. Interpolated statement creation uses Perl’s string-handling functionality to create a complete SQL statement to send to the database. The bind values are sent to the database after the SQL statement, but just before it’s executed. In both cases, the same result is achieved.

The actual difference lies in the way that databases handle bind values, assuming that they do. For example, most large database systems feature a data structure known as the "Shared SQL Cache,” into which SQL statements are stored along with additional related information such as a query execution plan .

The general idea here is that if the statement already exists within the Shared SQL Cache, the database doesn’t need to reprocess that statement before returning a handle to the statement. It can simply reuse the information stored in the cache. This process can increase performance quite dramatically in cases where the same SQL is executed over and over again.^[49]

For example, say we wished to fetch the general information for 100 megalithic sites, using the name as the search field. We can write the following SQL to do so:

SELECT name, location, mapref
FROM megaliths
WHERE name = <search_term>

By using interpolated SQL, we would actually issue 100 different SQL statements to the database. Even though they are ...