Setting SQLAPI++/iODBC/FreeTDS for Minimal Impact
This morning I spent a little time looking at the SQLAPI++ manuals looking for the way to make it minimal impact on the SQL Server I'm hitting. I was hoping to find a way of setting a timeout on the SQL statement's execution. What I'm seeing now is that every so often the act of reading from the database will hang the thread doing the reading, and it doesn't give it up for long enough that I restart the process.
This isn't good.
So I wanted to put in a timeout without resorting to a boost ASIO timeout. What I found was that there isn't a timeout in the SQLAPI++ code, and there isn't really one in the iODBC layer, either. There is one in the server configuration on FreeTDS, but I'm not really keen on putting a timeout value there for all connections and queries to a database. I just wanted to be able to put one on this set of queries.
What I did find was that I could make the SQLAPI++ command quite a bit nicer to the database with a few options on the command:
cmd.setCommandTest(aSQL.c_str()); cmd.setOption("PreFetchRows") = "200"; cmd.setOption("SQL_ATTR_CONCURRENCY") = "SQL_CONCUR_READONLY"; cmd.setOption("SQL_ATTR_CURSOR_TYPE") = "SQL_CURSOR_FORWARD_ONLY"; cmd.Execute();
where the middle three lines are new this morning. The default for the command is to fetch only one row at a time - that's very bad, and to allow a more liberal reading/updating policy with the cursor. I don't need any of that, and this will make sure that I'm about as lightweight on the database as possible.
With no timeout to fall back on, I'll have to just see if these changes are enough to make sure I don't get the lock-up again. Sure hope so...