Firebird PSQL performance trick: parameters x variables

Posted on in firebird

There is a small and easy PSQL trick that makes your code run much faster.

Here is a simple code in its slower form:

create or alter procedure p1 (i1 integer)
as
begin
    while (i1 > 0) do
        i1 = i1 - 1;
end

execute procedure p1(10000000)

This code in my machine takes around 3.2 seconds to run in Firebird 4.

Here is its BLR compiled code:

blr_version5,
blr_begin,
   blr_message, 0, 2,0,
      blr_long, 0,
      blr_short, 0,
   blr_message, 1, 1,0,
      blr_short, 0,
   blr_receive, 0,
      blr_begin,
         blr_stall,
         blr_label, 0,
            blr_begin,
               blr_begin,
                  blr_label, 1,
                     blr_loop,
                        blr_begin,
                           blr_if,
                              blr_gtr,
                                 blr_parameter2, 0, 0,0, 1,0,
                                 blr_literal, blr_long, 0, 0,0,0,0,
                              blr_assignment,
                                 blr_subtract,
                                    blr_parameter2, 0, 0,0, 1,0,
                                    blr_literal, blr_long, 0, 1,0,0,0,
                                 blr_parameter2, 0, 0,0, 1,0,
                              blr_leave, 1,
                           blr_end,
                  blr_end,
               blr_end,
         blr_end,
   blr_send, 1,
      blr_begin,
         blr_assignment,
            blr_literal, blr_short, 0, 0,0,
            blr_parameter, 1, 0,0,
         blr_end,
   blr_end,
blr_eoc

What is more important in this code is usage of blr_parameter2 inside blr_loop. blr_parameter2 internals has two internal "slots", one for the value and another for an "is null" flag.

So every time a parameter is read or assigned two slots must be accessed and the "is null" slot is accessed in a not very optimal way. It's kind of high level access using lower levels as building blocks. This for sure could be optimized in new versions.

Variables has single slot with both information.

Knowing that, let's change the code:

create or alter procedure p2 (i1 integer)
as
    declare variable v1 integer;
begin
    v1 = i1;

    while (v1 > 0) do
        v1 = v1 - 1;
end

execute procedure p2(10000000)

This code is apparently slower as it does an additional assignment but variables are faster to access than parameters.

In my machine this code takes around 2.3 seconds to run.

Here is its BLR:

blr_version5,
blr_begin,
   blr_message, 0, 2,0,
      blr_long, 0,
      blr_short, 0,
   blr_message, 1, 1,0,
      blr_short, 0,
   blr_receive, 0,
      blr_begin,
         blr_declare, 0,0, blr_long, 0,
         blr_assignment,
            blr_null,
            blr_variable, 0,0,
         blr_stall,
         blr_label, 0,
            blr_begin,
               blr_begin,
                  blr_assignment,
                     blr_parameter2, 0, 0,0, 1,0,
                     blr_variable, 0,0,
                  blr_label, 1,
                     blr_loop,
                        blr_begin,
                           blr_if,
                              blr_gtr,
                                 blr_variable, 0,0,
                                 blr_literal, blr_long, 0, 0,0,0,0,
                              blr_assignment,
                                 blr_subtract,
                                    blr_variable, 0,0,
                                    blr_literal, blr_long, 0, 1,0,0,0,
                                 blr_variable, 0,0,
                              blr_leave, 1,
                           blr_end,
                  blr_end,
               blr_end,
         blr_end,
   blr_send, 1,
      blr_begin,
         blr_assignment,
            blr_literal, blr_short, 0, 0,0,
            blr_parameter, 1, 0,0,
         blr_end,
   blr_end,
blr_eoc

Now blr_parameter2 run a single time (before blr_loop) and blr_variable is used in the loop.

This trick is not necessary in EXECUTE BLOCK code.

Firebird automatically does it in the generated BLR code.

You might be wondering why it does not do it automatically in stored routines. If it does, a simple code (not accessing a parameter millions of times) would be a bit slower and if the routine is called many times the performance would be degraded.

← Back to home