Publish date: 2009-08-13

New IBProvider with queries pool and enhanced working with Delphi

Support of tables list getting in Delphi in Client Cursor Engine mode

Tables list getting has been brought into compliance with the requirements of dbGo components in Client Cursor Engine mode.

New version of Microsoft Visual C++ 2005 SP1 Runtime

IBProvider v3.0.0.7575 RC4 uses the newer version of Microsoft Visual C++ 2005 SP1 Runtime also included into the distribution kit.

Performance optimization for repeated queries

New IBProvider v3 has the pool of SQL-queries that stores prepared and created commands and allows substantial time saving with a large number of repeated queries.

Queries pool will be especially relevant for the following tasks:

Queries pool and metadata

Queries pool may prevent metadata modifications. Therefore, when DLL commands are executed the queries pool is reset to solve possible caching problems.

Queries pool operation peculiarities

Queries pool is the list that stores two types of queries:

  • Prepared query related to a particular SQL command. It is placed to the pool after the client has made the command’s object free and started preparation of another query.
  • Initiated query enters the pool when the client sets incorrect SQL text and the server refuses to prepare such a query. The initiated query is placed to the pool for further use.

When the user prepares a new query:

  • A query with the appropriate SQL is searched for among the prepared queries. If the prepared query with the same SQL-text has been found, it is returned to the client.
  • If a query with the appropriate text has not been found in the pool, initiated queries are used first and then prepared queries being in the pool for over 30 seconds («stmt_cache__req_life_time» property) but related to other SQL-commands.
  • If the pool contains only prepared queries related to other commands and added less than 30 seconds ago («stmt_cache__req_life_time» property) or the pool is empty, a new query will be initiated and prepared.

When maximum number of queries is reached in the pool («stmt_cache__size» property), new queries start substituting old prepared queries.

Queries pool settings

Properties that manage queries pool behavior:

  • stmt_cache__time — minimum lifetime of an initiated or prepared query. 60 seconds by default. Any query contained in the pool for more than stmt_cache__time seconds will be deleted.
  • stmt_cache__req_life_time — minimum lifetime of a prepared query. 30 seconds by default.
  • stmt_cache__size — maximum number of resources. 1024 by default.

Queries pool disabling

Queries pool is enabled by default. To disable it, set «stmt_cache__time» or «stmt_cache__size» property as 0.

Queries pool setting via Data Links dialog

For your convenience, queries pool may be set via Data Links dialog.

Publish date: 2009-08-13. Copyright: IBProvider. This material may be reproduced on other web sites, without written permission but link required.