Subject Re: [firebird-support] Re: ORDER BY on large VARCHAR columns
Author Arno Brinkman

> First, thanks everyone for your time on this.
> Ok, so here's what I have settled on for the moment and how I got
> there.
> I first split the UNICODE VARCHAR(1024) over 16 VARCHAR(64)
> segments. I then individually indexed each of these 16 columns and
> change my code to replace an ORDER BY "big_ass_field" with an "ORDER
> BY Seg1, Seg2, Seg3, etc".
> This, as is probably not surprising, did not give me that much of an
> improvement. I have to imagine that the buffer pages where pretty
> damn huge.
> Then, I noticed that most of my uses of this field are generally
> less than 80 characters. So I go rid of the 16 VARCHAR(64) segment
> and replaced it with a single VARCHAR(84) segment that just took the
> first 84 chars of the VARCHAR(1024) field. I then refactored the
> ORDER BY clause to be "ORDER BY seg1, big_ass_field".
> This turned out to be much faster than making a distinct segment
> column for the entire string.
> For now the performance is acceptable. If I go to the FLOAT order
> field at some point in the future, I will report my approach and
> results.
> Thanks again everyone for your help.

You can play also with these entries in the configuration file :

SortMemBlockSize = 1048576
SortMemUpperLimit = 67108864

Arno Brinkman

Firebird open source database (based on IB-OE) with many SQL-99 features :

Support list for Interbase and Firebird users :

Nederlandse firebird nieuwsgroep :