Non indexed reads from RDB$PROCEDURE_PARAMETERS

[Tomasz Dubiel]

Hello.

Firebird 3.0.10.

An application using Jaybird jaybird-5.0.6.java11 executes block and performs such query:

execute statement('select ' ||(select first 1 p.rdb$parameter_name from rdb$procedure_parameters p where p.rdb$procedure_name = :procedurestock and p.rdb$system_flag = 0 and p.rdb$parameter_type = 1) || ' from ' || :procedurestock || '(:idConfig, :inventorySource, :idKartoteka)')(idconfig := :id_config, inventorysource := :inventorysource, idkartoteka := :id_kartoteka).

The effect of this is 18 092 350 non indexed reads from RDB$PROCEDURE_PARAMETERS and 18 seconds of execution. I see this in Firebird Trace and Audit Services.

I post this problem here, because when I run this in IBExpert, I get instant results and only a few indexed reads from non system tables.

What is wrong?

Best regards,

Tomek.

[Tomasz Dubiel]

That was only a mild example :-). Right now I got 55 seconds of execution and 564 786 432 non indexed reads from RDB$PROCEDURE_PARAMETERS.

[Tomasz Dubiel]

*550 seconds. There is some serious problem.

[Mark Rotteveel]

I can't think of why executing it through Jaybird would make any
difference, except I wonder if maybe connection character set could have
an effect there.

Are you connecting with an explicit character set (encoding/lc_ctype or
localencoding/charSet), or are you relying on NONE. What connection
character set are you using in IB Expert.

To be clear, this might be a train of thought that goes entirely in the
wrong direction.

What happens if you replace

p.rdb$procedure_name = :procedurestock

with

p.rdb$procedure_name = cast(:procedurestock as char(31) character set
unicode_fss)

?

Also, what is the definition of that variable procedurestock?

Also, what is the dialect of the database? If it is dialect 1, you will
need to explicitly specify connection property sqlDialect=1.

You may want to consider asking on firebird-devel as well.

Mark
--
Mark Rotteveel

[Tomasz Dubiel]

WIN1250 here and there.

declare variable procedurestock t_string; (varchar)255).

Dialect 3.

Any changes will be hard to test, because I don't have a source code of this application. All I can tell is the info from Firebird Trace and mon$attachments table.

Best regards.

[Tomasz Dubiel]

OK, I enabled system tables in performance analysis and now I see non indexed reads, but still execution of a query is instant.

[Tomasz Dubiel]

I need to analyze it more to get the exact reason of slow execution.

[Mark Rotteveel]

On 10/04/2025 09:07, Tomasz Dubiel wrote:

As I also mentioned on firebird-support, probably the query needs to be
changed to use

p.RDB$PACKAGE_NAME is null AND p.RDB$PROCEDURE_NAME = :procedurestock

instead of just

p.RDB$PROCEDURE_NAME = :procedurestock

The index defined is (RDB$PACKAGE_NAME, RDB$PROCEDURE_NAME,
RDB$PARAMETER_NAME), and it can't use this index if you don't include
the prefix (and if there were a procedure in a package with the same
name, you'd select the wrong one).

Mark
--
Mark Rotteveel