I have stumbled upon a problem with Selects based on indices which do not seem to be updated as they should.

There are no Active Transaction but there are more reads than the actual number of records corresponding to my select conditions.

Here’s an example :

create table Test1(id1 integer not null primary key,

IndexedField1 integer not null);

create index x_test1_Field1 on test1(IndexedField1);

INSERT INTO TEST1 (ID1, INDEXEDFIELD1) VALUES (1, 1);

INSERT INTO TEST1 (ID1, INDEXEDFIELD1) VALUES (2, 2);

INSERT INTO TEST1 (ID1, INDEXEDFIELD1) VALUES (3, 3);

commit;

select count(*) from test1 where indexedfield1=2 > returns value 1 with 1 indexed read.

update test1 t1 set t1.indexedfield1=4 where id1=2;

select count(*) from test1 where indexedfield1=2 > returns value 0 with 1 indexed read !!!

select count(*) from test1 where indexedfield1=5 > returns value 0 with 0 indexed read.

 

The problem is that the index seems to keep having « old » values until there is not a single connection to the database (even if I commit every transaction).  This causes me real performance problems on 24/7 systems … some query wich ran in less than a second now take a minute to go.

I know I can rebuild the indices if I set them inactive/active but it is impraticable in a 24/7 system because simultaneous queries raise exceptions.

 

Firebird 2.5.1 and 2.5.2 32 bits and 64 bits Superservers on Windows

Thank you for any advice !

Evelyne Girard