--- In firebird-support@yahoogroups.com, "markd_mms" <spam@...> wrote:

>
> i'm in the process of porting a music catalogue maintenance program
> from clarion (which natively handles large datasets well) to c++.
>

That probably depends on perspective, why do you say 120000 records is
'large' ;)

> entering new title into database revolves around a screen that lists
> *all* the titles in the catalogue (about 120000) which takes 10
> seconds or so to load in order (unordered takes almost no time). the
> data entry operators like this because it allows them to locate where
> an item might be and then quickly scroll up and down to see if it's
> already been entered but with a spelling mistake.
>
> i realise this is more of ye olde paradox way of doing things but
> they've said it's a compulsory feature so there's not much i can do.

What a global list is a compulsory feature? Or do they mean that 'the
ability to see if it's already been entered' is the compulsory feature?

Anything involving a grid of more than a few hundred rows is almost
without exception useless in the real world.

A far more efficient way would be to use a technique such as soundex
to test for existence. You could store the soundex value for each
title in an indexed field. During the 'Add' process, you could pop up
a window to say 'These titles may be similar'.

There are lots of ways of implementing a soundex, here is one using
Firebird stored procedures.

http://fbtalk.net/viewtopic.php?id=182

> i
> was wondering if i could fudge some similar kind of functionality by
> doing something like...
>
> SELECT FIRST 10 ...
> FROM ...
> WHERE param = 'whatever they enter'
> ORDER BY title
>
> but also selecting items preceeding whatever they're searching for?
> kinda like
>
> SELECT PREVIOUS 10 ...
> FROM ...
> WHERE param = 'whatever they enter'
> ORDER BY title
>
> so it might return
>
> whatever they eeter
> whatever they enter
> whatever they etner

You could probably do this in a stored procedure, I don't think you
can do an order by in the middle of a union statement, otherwise you
could do it there too. Of course you would need both ascending and
descending indices on title for it to work quickly.

Adam