Re^5: DBI, place holders and CGI forms (quote++)

[mje]

Preparing a statement once and executing many times is nearly always faster than preparing it many times. Look at my simple benchmark I posted at Re^5: DBI, place holders and CGI, 3s vs 16s. Now, I now this was a lot of iterations (10000) but if the SQL is a lot more complex it can make a much bigger difference. As I'm sure you are aware, the database engine is having to parse the SQL and work out an execution plan every time if you keep calling prepare (and yes, I know some databases cache plans). However, that ignores the extra round trips the DBD is doing as well and for some that can be very expensive. Most DBDs parse the SQL themselves to look for placeholders (some convert the SQL, e.g., ? becomes :1 or $1), send the SQL to the server, make another call(s) to find out details about the result set then call execute with the placeholders. So, I don't think recommending quote is good advice in general although I concede it is the "more general solution" most likely to work with all DBDs.

I cannot really comment much on MySQL as I don't use it these days but I feel I can comment on DBD::ODBC (as I maintain it) and DBD::Oracle (as I help maintain it). Both are demonstrably faster reusing a prepared statement.

Regarding placeholders in DBD::ODBC. The first thing you need to recognise is that few open source ODBC drivers implement even 70% of ODBC and the 70% they do implement often falls short in many different ways. The main ODBC API DBD::ODBC wants for placeholder support is SQLDescribeParam and few (if any) open source ODBC drivers support it at all. Most commercial drivers do support SQLDescribeParam although admittedly some don't do so well when the SQL is complex (more below). Without SQLDescribeParam support DBD::ODBC has to bind everything as a string to be interpreted as a string meaning you often end up having to add cast etc around your strings all over the place (especially dates/times).

As a side point, few people seem to know ODBC has a defined syntax for dates/times using {d 'yyyy-mm-dd'}, {t 'hh:mm:ss.fff'} and {ts 'yyyy-mm-dd hh:mm:ss.fff'} and all drivers should at least support that.

Why is SQLDescribeParam not supported that often? Because it is difficult to implement and usually requires reorganising your SQL. e.g., you do "insert into table (a) values(?)" and the driver basically reorganises this into "select a from table" so it can then find out about column a and hence the type, size etc of the placeholder. When the SQL is straight forward like this example it is fairly easy but even a simple query with an insert can be difficult to work out e.g., "select some_func(?) from table where some_column = 1", how does the driver reorganise that SQL to describe the placeholder when it does not refer to a column! Mostly, they don't and SQLDescribeParam will fail but in some rare cases I've seen the driver will actually reorganise the SQL incorrectly (but so it works) and then incorrectly describe the parameter.

So I suspect your apparent bad experience with placeholders is down to bugs in the ODBC driver or database engine. They could be DBD::ODBC but as I seldom receive any bug report associated with an open source ODBC driver (other than freeTDS which seems to be used a lot) it is difficult to know.

As for DBD::Oracle, I use placeholders all over the place in a large system and I've never had any problem with them that wasn't a bug and fixed. If you are repeatedly executing the same SQL then preparing it once is definitely faster and you can even see this in the enterprise manager stats.

I don't use prepare_cached and have no need to as most of my Perl is running in daemons so I prepare once.