Dear all,

I have a table containing names. When a user requests a particular
name, I want to also fetch the neighboring names in an alphabetical
sense. Example: viewing SMITH,JOHN; previous: SMITH,JAMES; next:
SMITH,PETER

The SQL I am using is:
SELECT fullname FROM mnames WHERE fullname>'SMITH,JOHN' ORDER BY
fname;
SELECT fullname FROM mnames WHERE fullname<'SMITH,JOHN' ORDER BY fname
DESC;

So I want to fetch the first row from each result set. There is an
index on the fullname column. The table contains maybe 50K names and
is accessed over a low-speed connection, so doing a client-based
'locate' is unviable.

Here is the problem: In IBX, simply opening two datasets with the
above SQL and reading the first record works FINE. But when I use
IBO, it is MUCH slower depending on the position of the name. For
example, fetching the 'next' name when the current one starts with Z
is as fast as IBX, but fetching the previous name is horribly slow.
Converse applies if the current one starts with A.

I am probably doing something wrong or suboptimally, can anyone help
me? Here is skeleton code.

TIB_Cursor* ibd = new TIB_Cursor(NULL);
ibt->StartTransaction();
ibd->IB_Transaction = ibt;
ibd->IB_Connection = pDatabase;
ibd->MaxRows = 1;
ibd->SQL->Clear();
ibd->SQL->Add([as above]);
ibd->Prepare();
ibd->Active = true;
ibd->First();
rec_count = 0;
while (!ibd->Eof && rec_count++ < 1) {
name = ibd->Fields->Columns[0]->AsString;
ibd->Next();
}
ibd->Active = false;
etc.

I don't believe this is to do with the server sorting the names, since
IBX runs fast. I'm open to just about any solution except creating
generators (I need to do the same thing for many other tables).

Just a side note with the code... the value '1' is actually passed as
a parameter and is not hard-coded, hence the ibd->Next(). If the 1
were hard-coded, I know this could be omitted.

Thanks!