Issue Details (XML | Word | Printable)

Key: CORE-2329
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Paulius Pazera
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

significant performance degradation in v2.1.2rc1 (comparing to v1.5)

Created: 17/Feb/09 12:44 PM   Updated: 12/Nov/09 03:34 PM
Component/s: None
Affects Version/s: 2.1.2
Fix Version/s: 2.1.2

Time Tracking:
Not Specified

Environment: dual intel xeon, suse linux, classic firebird, db created with default parameters executing 'create database' within isql (e.g. forced writes were off, page size 4k, etc)

Planning Status: Unspecified


 Description  « Hide
test case below shows ~3 and ~7 times (!) slowdown comparing 2.1.2rc1 with 1.5.5. To reproduce -- create database, re-connect isql using localhost and execute the script provided below. Note how long 'select * from sp;' takes to insert and to scan records. For example, on our dual xeon server classic firebird v1.5 takes ~4.73 seconds to insert and ~3.10 seconds to scan 1M records while classic v2.1.2rc1 takes ~13.45 seconds to insert and ~21.53 seconds to scan the same 1M records on the same hardware

set stats on;
create table tbl(i1 integer, i2 integer, i3 integer, i4 integer, i5 integer, i6 integer, i7 integer, i8 integer, i9 integer, i10 integer,
                       i11 integer, i12 integer, i13 integer, i14 integer, i15 integer, i16 integer, i17 integer, i18 integer, i19 integer, i20 integer);
set term ^;
create procedure sp
returns (i integer)
as begin
  i=0;
  while(i<1000000) do begin
    i=i+1;
    insert into tbl values (:i, :i, :i, :i, :i, :i, :i, :i, :i, :i, :i, :i, :i, :i, :i, :i, :i, :i, :i, :i);
  end
  suspend;
end
^
set term ;^

select * from sp; /* 4.73 sec fb1.5.5 vs 13.45 sec v2.1.2rc1 (almost 3 times slower) */
commit;

select count(*) from tbl;

drop procedure sp;
commit;

set term ^;
create procedure sp
returns (i integer)
as
  declare variable i1 integer;
  declare variable i2 integer;
  declare variable i3 integer;
  declare variable i4 integer;
  declare variable i5 integer;
  declare variable i6 integer;
  declare variable i7 integer;
  declare variable i8 integer;
  declare variable i9 integer;
  declare variable i10 integer;
  declare variable i11 integer;
  declare variable i12 integer;
  declare variable i13 integer;
  declare variable i14 integer;
  declare variable i15 integer;
  declare variable i16 integer;
  declare variable i17 integer;
  declare variable i18 integer;
  declare variable i19 integer;
  declare variable i20 integer;
begin
  i=0;
  for select i1, i2, i3, i4, i5, i6, i7, i8, i9, i10, i11, i12, i13, i14, i15, i16, i17, i18, i19, i20
    from tbl
    into :i1, :i2, :i3, :i4, :i5, :i6, :i7, :i8, :i9, :i10, :i11, :i12, :i13, :i14, :i15, :i16, :i17, :i18, :i19, :i20
  do begin
    i=i+1;
  end
  suspend;
end
^
set term ;^

select * from sp; /* 3.10sec fb1.5.5 vs 21.53sec fb2.1.2.18116rc1 (almost 7 times slower) */
commit;

drop procedure sp;
commit;
drop table tbl;
commit;


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 17/Feb/09 05:12 PM
The major reason of this slowdown (as compared with v2.1.1) has just been fixed.