Subject RE: [IBO] Optimize Locate() for an entirely fetched TIB_Query result set
Author Support List
Thomas,

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


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


> Any hints are appreciated!

Hope this helps,
Jason LeRoy Wharton
www.ibobjects.com






=======
Email scanned by PC Tools - No viruses or spyware found.
(Email Guard: 7.0.0.26, Virus/Spyware Database: 6.17500)
http://www.pctools.com/
=======