> I wait that Jason read it.

Please read my "Demonstration Notes" at the bottom.

> My TIBOQuery is:

> OrderingItems:
> ID_P=ID_P;ID_P DESC
> ID=ID;ID DESC

> OrderingLinks:
> ID_P=ITEM=1;POS=0
> ID=ITEM=2;POS=0

> OrderingItemNo: 1

> KeyLinks:
> ID

This looks good, although I dont think you need the ID entries in the
orderinglinks - since you will never actually order by that field
directly. But as long as it is never used it should not matter
whether it is there or not.

> Developer Express component calls Locate by ID or ID_P. ID is my
> primary key, my tables is indexed by ID ASC / DESC and ID_P ASC /
> DESC, 4 indexes. ID_P is the parent.

This concerns me on a few points...

- I dont see any need for both asc/desc indexes. AFAICT from what
has been said so far the descending indexes will never be used and
will simply add overhead and potential confusion to the optimiser.

- more importantly is your statement that ID is the primary key AND
that you have indexed by ID ASC /DESC. This gives you a total of
three indexes associated with the field ID (because IB/FB will
automatically create an internally defined index to support the
primary key). Having multiple indexes against the same field is
likely to confuse the IB/FB optimiser and is NOT recommended.

IMO you table should be setup with a primary key on ID (and no
other indexes) and only one ascending index on ID_P (with possibly
another field for sorting within a branch - but only if the number of
entries per branch is likely to be large).

I dont think this has any bearing on your particular problem, but
simpler is often better and less confusing.


Demonstration Notes:

I just setup a demo using the employee.gdb database in which I setup
TIBOQuery with the following properties...

object EmployeeTestDQ: TIBOQuery
OrderingItemNo = 1
OrderingItems.Strings = (
'Dept=DEPT_NO')
OrderingLinks.Strings = (
'DEPT_NO=ITEM=1;POS=0')
IB_Connection = Connect
IB_Transaction = Transact
KeyLinks.Strings = (
'EMP_NO')
RecordCountAccurate = True
RequestLive = True
SQL.Strings = (
'SELECT * FROM EMPLOYEE')
end

I then tried to perform two different locates, one by key the other by
the DEPT_NO field (which I know has an index in the database - an
automatically generated one due to it being a foreign key, but that
should not impact the results of this test).

I then tried to do a locate by key...

procedure TForm1.LocateByKeyBtnClick(Sender: TObject);
begin
with EmployeeTestDQ do
begin
if not Active then
Open;
if Locate( 'EMP_NO', 24, [] ) then
ShowMessage( 'Found By Key' );
end;
end;

Which worked and resulted in the following SQL monitor output
(only the relevant part shown):

/*---
PREPARE STATEMENT
TR_HANDLE = 14899372
STMT_HANDLE = 14900072

SELECT EMP_NO
FROM EMPLOYEE
WHERE EMP_NO=?

PLAN (EMPLOYEE INDEX (RDB$PRIMARY4))

FIELDS = [ Version 1 SQLd 1 SQLn 1
EMPLOYEE.EMP_NO = 24 ]
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 14899372
STMT_HANDLE = 14900072
PARAMS = [ Version 1 SQLd 1 SQLn 1
EMPLOYEE.EMP_NO = 24 ]
----*/

Notice the line "PLAN (EMPLOYEE INDEX (RDB$PRIMARY4))" which indicates
an index based lookup of the required value was performed.

I then tried to do a locate by DEPT_NO (not a key value)...

procedure TForm1.LocateByOrderBtnClick(Sender: TObject);
begin
with EmployeeTestDQ do
begin
if not Active then
Open;
if Locate( 'DEPT_NO', 623, [] ) then
ShowMessage( 'Found By Order' );
end;
end;

Which worked and resulted in the following SQL monitor output
(only the relevant part shown):

/*---
PREPARE STATEMENT
TR_HANDLE = 14899372
STMT_HANDLE = 14899928

SELECT EMP_NO
FROM EMPLOYEE
WHERE (((DEPT_NO = ? /* LOC_5 */ )))

PLAN (EMPLOYEE INDEX (RDB$FOREIGN16))

FIELDS = [ Version 1 SQLd 1 SQLn 30
EMPLOYEE.EMP_NO = <NIL> ]
----*/
/*---
DESCRIBE INPUT
STMT_HANDLE = 14899928
PARAMS = [ Version 1 SQLd 1 SQLn 1
< SQLType: 452 SQLLen: 3 > = <NIL> ]
----*/
/*---
EXECUTE STATEMENT
TR_HANDLE = 14899372
STMT_HANDLE = 14899928
PARAMS = [ Version 1 SQLd 1 SQLn 1
[LOC_5] = '623' ]
----*/

Notice the line "PLAN (EMPLOYEE INDEX (RDB$FOREIGN16))" which
indicates that an index based lookup was performed to find an entry
matching the specified department.

This output comes from creating a special locate cursor inside the
TIBOQuery. This cursor retrieves the key value for the selected
record and then positions the main query to that key value. Watching
the output further I see the refining cutting in with the OLNK_*
parameter on the main query being set to the matching department.

The main problem that I can see is that the locate cursor is not using
the ordering of the original query - from the code it looks like it
should be, but from the SQL monitor it is not happening. This could
be a problem because the locate is not guaranteed to place you on the
first instance of a record that would located according to the order
of the main dataset. In one variation of my test (where I made the
order DEPT_NO, LAST_NAME, FIRST_NAME) I was placed on the second
occurance of DEPT_NO=623.

I would add to this that the VCL documentation for Locate does not
insist that Locate should find the _first_ occurance of a particular
record, only that it should return true if a match is found. Perhaps
DevExp take this into account.

I would also add that in my example (and your ID_P example) there are
insufficient fields in the order by clause to guarantee that the main
cursor and locate cursor would retrieve records in the same order
anyway (SQL does not guarantee ordering of records unless the ORDER BY
clause is explicit).

Jason, are you about? Any comments on this?


In other words: All seems to be working pretty much as I expected -
and it appears to be matching your requirements.

So you need to do more testing at your end to try and discover why it
is not working this way in your instance. Perhaps you can setup a
demo database and app (without DevExp, just using explicit locate
calls as I did above) and send it to me privately.


--
Geoff Worboys
Telesis Computing