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)


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.

Like this post? Share on: TwitterFacebookEmail

Related Articles


Firebird Community



Gems from Firebird Support list