Subject Re: [IBO] IBIncSearch: Incremental Search Performance Issues
Author Helen Borrie
At 01:04 AM 9/05/2005 +0000, you wrote:
>I've put together a simple incremental search app to test the
>performance of the TIBIncSearch component. I set up a sample app just
>as outlined in the Getting Started Guide, except that I used a 95,000
>record table with a fair number of columns and an index on the
>LAST_NAME field, upon which I decided to try the incremental search.
>For example, let's say I set SearchKeyByKey and SeekNearest to true.
>If I start by typing a "z" in for the search, the initial search takes
>about nine seconds, during which a dialog pops up that reads "Fetching
>Query Results Row #" XXXXX, while XXXXX increments to about 95,000.
>It seems that the IB_Grid is being populated with a huge number of
>values, which, though providing excellent performance in subsequent
>searches, heavily loads the server and provides rather poor
>performance for one-off ad hoc searches. For reference, I am
>connecting to the server across a 100 Mbit network, and server
>processor loading during the nine seconds that the above dialog is
>presented is about 25%.
>I haven't managed to locate any settings that improve performance by
>reducing the size of the result set initially returned by the server.
> Is there anything I'm missing in terms of how one should use
>IBIncSearch optimally against a large table? I guess there's always
>the option of writing custom query code and manually loading my grids,
>but I had read a lot about the efficiency of incremental searches in
>IBO and had hoped I might manage to avoid that.
>Thanks in advance for any suggestions you might provide.

OK, the first thing you have to understand about 2-tier client/server is
that, regardless of the interface you use, search performance is affected
(positively or negatively) on two overall factors:

1) the size of the set
2) the quality of the indexing on searched fields

If you submit a query that is going to search 95,000 rows, with no indexes
on the searched column(s), then you can expect "slow", even at LAN
speeds. The server will take a long time to find that record identified as
"STARTING WITH {the search string}". Always try to drill down your searches.

The quality of the indexes can be something of an art. If you are
searching for something starting with "Z", it could take a very long time
if you are operating on a set that is ordered in ascending order by that
key! It will also take a long time if there is no index on that key.

And, no, IBO never puts 95,000 records in a grid, not even on 31-inch
monitors. :-) A grid displays a portion of the row buffer. IBO does a
lot of work under the hood to shuffle the contents of the buffer in and out
to find what you are looking for in the shortest possible time. It
actually maintains two cursors on the server set so that it can work with
the non-scrolling server buffer in a way that provides a scrollling
"sliding window" on the client-side set.

Of particular interest to you for your incremental search will be the
OrderingLinks and SearchingLinks properties. Read the help to see how
these properties interact. For example, if you have both ascending and
descending indexes on your searched column, you can set up a single
OrderingItem that is aware of both indexes; link that OrderingItem to an
OrderingLink and link that to your search parameter through SearchingLinks.