Subject Firebird 2.5.x slow queries problem
Author Sylvain Mazaudier
Post date 2015-05-20T08:51:37Z
Hello.

We have a problem under Firebird 2.5.x.

If a remote client connects to a server over a network with low flow , we have delays on requests .

After having turned the problem in every way, I give you the result of my analysis.

Test realized in client / server on a computer with a reduced flow 128k under Firebird 2.5 client and server.

SCRIPT :

CREATE TABLE TEST (
    ROW1  VARCHAR(255),
    ROW2  INTEGER
);
 
INSERT INTO TEST (ROW1, ROW2) VALUES ('value1', 1);
INSERT INTO TEST (ROW1, ROW2) VALUES ('value2', 2);
INSERT INTO TEST (ROW1, ROW2) VALUES ('value3', 3);
INSERT INTO TEST (ROW1, ROW2) VALUES ('value4', 4);
.....
 
Query select 1: SELECT ROW1 FROM TEST;
 
Query select 2: SELECT ROW2 FROM TEST;


Résults in IBExpert monitor :
Query select 1:1.5 sec
Query select 2:5.3 sec


[13/05/2015 17:50:50] - [IB API call : isc_dsql_free_statement]
select ROW2 FROM TEST
 
.....
 
[13/05/2015 17:50:50] - [IB API call : isc_dsql_free_statement]
select f.rdb$field_name,
f.rdb$field_source,
f.rdb$null_flag,
f.rdb$default_source,
fs.rdb$null_flag,
fs.rdb$field_name,
fs.rdb$field_type,
fs.rdb$field_length,
fs.rdb$field_scale,
fs.rdb$field_sub_type,
fs.rdb$segment_length,
fs.rdb$dimensions,
d.rdb$dimension,
d.rdb$lower_bound,
d.rdb$upper_bound,
fs.rdb$character_set_id,
f.rdb$collation_id,
cr.rdb$character_set_name,
co.rdb$collation_name,
f.rdb$field_position,
fs.rdb$computed_source,
fs.rdb$character_length,
fs.rdb$default_source,
f.rdb$description,
fs.rdb$collation_id
,fs.rdb$field_precision
from rdb$relation_fields f
left join rdb$fields fs on fs.rdb$field_name = f.rdb$field_source
left join rdb$field_dimensions d on d.rdb$field_name = fs.rdb$field_name
left join rdb$character_sets cr on fs.rdb$character_set_id = cr.rdb$character_set_id
left join rdb$collations co on ((f.rdb$collation_id = co.rdb$collation_id) and
(fs.rdb$character_set_id = co.rdb$character_set_id))
where f.rdb$relation_name = 'TEST'
order by f.rdb$field_position, d.rdb$dimension
 
[13/05/2015 17:50:54] - [IB API call : isc_start_multiple]
...
[13/05/2015 17:50:55] - [Commit]
Transaction ID: 2953

**********************************************************

The internal query for ROW1 is :

 
select RDB$FIELD_SOURCE from RDB$RELATION_FIELDS
where (RDB$RELATION_NAME = 'TEST') and
(RDB$FIELD_NAME = 'ROW1')


We don't understand why are the internal Firebird Queries very different and why does it takes 4 more time to execute on ROW2 ?


Regards.