At 11:47 AM 22-01-02 -0700, Jason Frey wrote:

>Ehh.. Not so good when your table has the possibility of having 120k
>records.

120K records in a listbox? Sheesh!

>I also remember seeing someone (Forgive me, I don't recall who) say that the
>way they get around this was to create another column in the table to hold
>the uppercase value of whatever they're interested in sorting by, index on
>that column, and do searching against that column. After thinking about,
>assuming that my understanding of upper is correct (That it doesn't use
>indexes when doing upper()), that would seem to be a better solution for
>this issue, in terms of performance (Though performance is still not going
>to be great in my large databases, from my experience.

Table-oriented GUIs applications are slow on client/server databases, even quite small ones.

>Maybe my experience
>is flawed, I'm not sure) than allowing an upper on the order by clause.

FB/IB don't support indexes on expressions...you are going to stump any query that does an order by on unindexed columns (which is what any expression-derived column will be). For lookups of < 200 rows, it won't be a grave issue. On 120K rows it will be.

>I guess I'm wondering how others have overcome case insensitive searching
>(as it seems that many people would have had to). I'd rather not re-invent
>the wheel if I don't have to. :)

By using indexed proxy search columns, populated by triggers. You might be interested in researching out metaphone and soundex if strict alphabetical order is not essential.

Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________