Hello Jason,

>> 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.
>
> If you are going to hold the entire table in memory then there would be no
> benefit from HDR. Also, you should set FetchWholeRows to true in this case,
> which will eliminate the unnecessary overhead of fetching individual rows.
> This is because your key and your row are more or less the same so there is
> no benefit from not fetching the entire row.
>
>
>> 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 */
>
> This is due to setting FetchWholeRows to false.

I have the following properties set for this particular TIB_Query.

AutoFetchAll := True
CallbackInc := -1
FetchWholeRows := True

>> 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.
>
> This will happen automatically when the query recognizes that all records
> have been fetched.
>
> There is the possibility you will see some activity with the server if a
> match is not found in your buffer. IBO will take a look on the server and
> see if it can find a match there and then bring it into your buffer.

Even with the settings above, I still have a large number (pretty much
the number of Locate() calls) 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 */

calls. In the Firebird 2.5 Trace API output.


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