Subject Re: ORDER BY on large VARCHAR columns
Author robert_difalco
First, thanks everyone for your time on this.

Ok, so here's what I have settled on for the moment and how I got

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

Thanks again everyone for your help.