Is this still an issue?
I've lost touch with this thread.

Thanks,
Jason

----- Original Message -----
From: "Geoff Worboys" <geoff@...>
To: "Miguel" <IBObjects@yahoogroups.com>
Sent: Thursday, May 23, 2002 12:30 AM
Subject: Re: [IBO] Locate - More information...


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