Dany Schaer wrote:
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?
Dmitry Yemanov answers:
Regardless of its last argument value, LPAD/RPAD returns VARCHAR(32K) as a result. Sorting works with full record lengths. So, for 30K records, the engine has to sort 900MB.