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.

I have a simple sample application with a smallish Firebird 2.5
database, which shows this problem. Care to have a look?

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!