Subject Optimize Locate() for an entirely fetched TIB_Query result set
Author Thomas Steinmaurer
Hello,

I have a setup/configuration table, which I entirely load with a
TIB_Query and AutoFetchAll := True, as I have to visit all records in an
initializiation/setup routine anyway.

The underlaying table consists of three columns with a composite primary
key including all three columns.

The TIB_Query is configured at run-time like that. DatabaseInfo.<xyz> is
just holding string constants for the table name and the three column names.

Close;
SQL.Clear;
SQL.Add('SELECT * FROM ' + DatabaseInfo.LOGDETAILSMETADATA + ' ORDER BY
' + DatabaseInfo.TABLE_COLUMN + ',' + DatabaseInfo.OPERATION_COLUMN +
',' + DatabaseInfo.COLUMN_COLUMN);
OrderingItems.Clear;
OrderingItems.Add(DatabaseInfo.TABLE_COLUMN + '=' +
DatabaseInfo.TABLE_COLUMN + ';' + DatabaseInfo.TABLE_COLUMN + ' DESC');
OrderingItems.Add(DatabaseInfo.OPERATION_COLUMN + '=' +
DatabaseInfo.OPERATION_COLUMN + ';' + DatabaseInfo.OPERATION_COLUMN + '
DESC');
OrderingItems.Add(DatabaseInfo.COLUMN_COLUMN + '=' +
DatabaseInfo.COLUMN_COLUMN + ';' + DatabaseInfo.COLUMN_COLUMN + ' DESC');
OrderingLinks.Clear;
OrderingLinks.Add(DatabaseInfo.TABLE_COLUMN + '=ITEM=1;POS=0');
OrderingLinks.Add(DatabaseInfo.OPERATION_COLUMN + '=ITEM=2;POS=0');
OrderingLinks.Add(DatabaseInfo.COLUMN_COLUMN + '=ITEM=3;POS=0');
Open;

So, what I'm trying to do is to set OrderingItems and OrderingLinks
properly, to possibly benefit from HDR.

But still, I see a bunch of:

SELECT * FROM IBLM$COLUMNLOGDEF
WHERE IBLM$COLUMNLOGDEF.TABLE_NAME=? /* BIND_0 */
AND IBLM$COLUMNLOGDEF.OPERATION=? /* BIND_1 */
AND IBLM$COLUMNLOGDEF.COLUMN_NAME=? /* BIND_2 */


statements, when Locate() is executed.

What I'm trying to do is to Locate records in-memory as they all have
been already fetched, without go the server.

Any hints are appreciated!

Thanks.

--
With regards,

Thomas Steinmaurer
Upscene Productions
http://www.upscene.com
http://blog.upscene.com/thomas/

Download LogManager Series, FB TraceManager today!
Continuous Database Monitoring Solutions supporting
Firebird, InterBase, Advantage Database, MS SQL Server
and NexusDB!