msobkow's Journal: MSS Code Factory is moving right along

As you can see from the MSS Code Factory project site, things are progressing steadily with my pet project. I've just finished spending a couple of weeks reworking the PostgreSQL database IOs to use PreparedStatements wherever possible instead of pure dynamic SQL. At this point, dynamic SQL is only used for cursor-based reads and index queries which reference nullable columns; all other queries and accessors use prepared statements (static SQL.)

I haven't tested the performance of this new layer with PostgreSQL, and don't intend to compare performance of dynamic and static SQL as it would require keeping copies of and debugging both versions of the code. I know from previous experience with DB/2 UDB that using PreparedStatements can result in an 80% overall performance improvement for something like loading a model into a relational database.

Unfortunately most of the performance benefits would be lost when using the code for a web server, because you have to releasePreparedStatements() at the end of each web page served, because there is the possibility that a particular vendor's implementation of PreparedStatements might have data associated with it on the server end of the connection, and the connection has to be released after serving the page.

One of the biggest advantages of switching to static SQL is that parameter binding with PreparedStatements can handle variables up to the maximum size for the type, whereas dynamic SQL is limited by the size of the statement buffer accepted by the database (which used to be a significant limitation with DB/2 UDB 7.2, though I've no doubt that limit has been expanded or eliminated.)

A key point of the use of static SQL is that the only difference between the different databases now is the specific SQL functions used to convert strings to date-time types, so I'm going to be rolling out the support for the commercial databases under GPLv3 after all, rather than trying to leverage them for profit. The differences are just too negligable for me to believe anyone would pay for the privelege of using a commercial database.