In the last few days whilst hanging out on the #dbi IRC channel I saw messages from someone using/developing OTRS (Open source Ticket Request System
) with DBD::ODBC
His basic problem was that it works with at least MySQL and MS SQL Server but a) the executed queries take longer with DBI/DBD::ODBC/MS SQL Server than DBI/DBD::mysql/mysql server and b) as the users increase the CPU load does not scale in a linear fashion. He was looking to either ditch support for MS SQL Server or perhaps develop a new DBD under DBI which was faster than DBD::ODBC (perhaps using Win32::SQLServer
) but I stepped in to to ask for clarification of what the perceived slowness was.
Generally I find people are quick to dismiss ODBC as slow but usually when I look in to it this is not the case. The DBD::ODBC driver has to work with literally dozens of drivers with different capabilities and this is often compared with an other DBDs which only work to a specific database - not a fair comparison in my view especially if you need to develop Perl code which works with multiple databases (why should there not be some give when developing for multiple databases).
However, in this case, once we got through the problems of generating a realistic test case which demonstrated the problem I quickly saw there was room for improvement. Even though the ODBC specification is reasonably specific there are a few places where it is open to interpretation and then there are ODBC drivers with bugs which DBD::ODBC attempts to work around (of which, I'm, afraid there are many). One such issue is with datetime parameters and fortunately Jeff Urlwin (previous maintainer of DBD::ODBC) had the forethought to write a test case for it. In ODBC you bind a parameter marker with SQLBindParameter which amongst other things provides the driver with the value_type (C data type), SQL data type it should be seen as and a host of other function parameters detailing the parameter length, column_size etc. In theory, if you prepare a statement and bind parameters, then each time you execute the statement you should not have to re-prepare the statement as all you are doing is changing some value of the parameter and it is still bound to the same column. However, the MS SQL Server driver (like all others) is forced (via the ODBC spec) to only use the values passed to SQLBindParameter and cannot use pointer arguments to SQLBindParameter until SQLExecute is called. As a result when you bind a NULL to a datetime the column_size you pass to SQLBindParameter really matters as the ODBC driver may say it is shorter than YYYY-MM-DD HH:MM:SS.sss (if the column_size is less than 23) and so bind it as a smalldatetime instead of a full datetime. This of course ok when it is a NULL but when you later pass a full datetime the driver might not spot the change and continue to bind it as a smalldatetime. This appears to be what happens in the MS SQL Server driver and previous maintainers of DBD::ODBC have worked around this problem by calling SQLFreeStmt(SQL_RESET_PARAMS) (which drops all parameters) and then binds all the parameters again. So the workaround works but...
All this would be ok except the MS SQL Server driver decides that since the parameters have been reset and rebound that it needs to re-prepare the statement and this requires another round trip to the server and the expense this involves.
The real problem in DBD::ODBC is that the workaround coded is a solution to the problem seen but it causes the MS SQL Server driver to do a lot of extra work. The proper solution is to bind NULL datetimes (and other SQL types) with the column_size of the actual column regardless of whether the parameter is NULL or not (which the driver cannot fairly know).
Once I'd made this change (to not reset parameters) and changed the SQLBindParameter code to only rebind if the arguments had changed we saw a 50%ish speed up on the test case provided.
I've released this to OTRS and await their response. In the meantime as this is a fundamental change affecting all drivers and in particular MS SQL Server I need testers. Through the #dbi IRC channel I've got the go ahead to mail a number of lists where I'll ask people to test and in the next few days I'll release to CPAN version 1.23_4. If you use DBD::ODBC please test this as unless I get negative feedback I will release this change and you could be affected.