At 10:20 PM 19/06/2008, you wrote:

>Hi All;
>
>I am using FB 2.1, and I have found that LPAD slows down (60 seconds or
>more for 30k records) a lot when used in a order by clause:
>
>select "EXP1" from "PROC" order by LPAD(lower(trim("EXP1")),18)
>
>or
>
>select LPAD(lower(trim("EXP1")),18) as "XX" from "PROC" order by "XX"
>
>But, it is very fast (0 seconds) if I do not use it in the order by
>clause:
>
>select LPAD(lower(trim("EXP1")),18) from "PROC"
>
>Why is this?.

Actually, it's THREE function calls per row at sort time that makes this SOOOO slow, as well as lack of any index. Create an expression index based on that expression - that will help - although it's still going to be a lot of work to sort 30K rows...so make sure you have plenty of RAM available.

./heLen