Subject | Optimize Locate() for an entirely fetched TIB_Query result set |
---|---|
Author | Thomas Steinmaurer |
Post date | 2011-05-12T08:00:32Z |
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!
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!