Friday, October 15, 2010

Performance: Statement concentrator, small knob with an impact

First off, let me start by pointing out that it is best practice to use parameter markers for repeating, similar dynamic SQL queries. Instead of issuing

select fname, lname from person where id=15
select fname, lname from person where id=266

one could write
select fname, lname from person where id=?

and provide the 15 or 266 as parameter to the query. The reason is that every SQL statement needs to be compiled before it can be executed. Query compilation takes some time and if you need to repeat it gazillion times, it will add up. If a parameter marker is used, the statement is compiled once and DB2 remembers how to execute the statement (the code produced is called package) in its package cache. Whenever the statement is executed again, the compilation phase can be skipped, the package is taken from the cache, the parameter is replaced with its actual value, and you have the results back.

Now to the statement concentrator, a new feature introduced in DB2 9.7. Sometimes, it is not possible to use parameter markers or the application is third-party code and it cannot be changed. That's when the statement concentrator comes in handy. Once it is enabled for literals, the new logic detects repeating similar statements that don't use parameter markers. It then tries to reuse an already existing package from the package cache to skip compilation and to save time.

The statement concentrator is OFF by default and it can be configured on the server (STMT_CONC in the database configuration) and/or for clients (preferred) using, e.g., CLI/ODBC or JDBC/SQLJ. Remember: If you want to benefit from the statement concentrator, you need to be active and enable it. And yes, parameter markers and the statement concentrator work for XML-related queries, too...