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...


Anonymous said...

The link to "JDBC/SQLJ" above for statement concentrator at client is broken. Also, can you elaborate on why statement concentrator is preferred at client over server (and how it works differently at client).

Henrik Loeser said...

Well, it seems that the structure of the JDBC/SQLJ documentation changed and the link became invalid. I replaced the link and it should point to the right information.

The reason why the statement concentrator should be enabled per application (client) is actually given at the second link ("statement concentrator, a new feature"):

"Enabling statement concentrator at the client level is preferable to the database manager level for several reasons. Firstly, it allows statement concentrator to be controlled at the finest level. Secondly, it is the only consistent way to enable statement concentrator throughout the DB2® family of products."


Related Posts with Thumbnails