Subject | Class 2 driver modifications have 2x insert performance over pure type 4 for many small inserts. |
---|---|
Author | Ryan Baldwin |
Post date | 2003-03-04T10:30:03Z |
Hi,
When running some benchmarks on the type 2 driver modifications I came up
with some interesting results. These show that when many small records are
inserted the driver in type 2 mode performs alomost twice as quick as the
pure type 4 driver. The numbers I got are:
Type 4 Type 2
Run#1 Run#1
benchmarkPreparedInsert: 12628 benchmarkPreparedInsert: 6880
benchmarkNonPreparedInsert: 10475 benchmarkNonPreparedInsert: 5398
benchmarkBigBlobInsert: 16234 benchmarkBigBlobInsert: 10655
Run#2 Run#2
benchmarkPreparedInsert: 11246 benchmarkPreparedInsert: 6479
benchmarkNonPreparedInsert: 9925 benchmarkNonPreparedInsert: 5117
benchmarkBigBlobInsert: 15912 benchmarkBigBlobInsert: 10596
Run#3 Run#3
benchmarkPreparedInsert: 11286 benchmarkPreparedInsert: 6519
benchmarkNonPreparedInsert: 9804 benchmarkNonPreparedInsert: 5098
benchmarkBigBlobInsert: 16103 benchmarkBigBlobInsert: 10595
In these tests records are inserted into a table looking like this:
CREATE TABLE ATABLE (
FIELD1 INTEGER NOT NULL,
FIELD2 CHAR(16),
FIELD3 VARCHAR(64) CHARACTER SET ISO8859_1 COLLATE EN_UK,
FIELD4 VARCHAR(256) CHARACTER SET ISO8859_1 COLLATE EN_UK,
FIELD5 BLOB SUB_TYPE 1,
FIELD6 INTEGER,
PRIMARY KEY (FIELD1) ) ;
In each run 10000 records are inserted. In the first two the blob field is
filed with very small blobs. On the last test the blob field is filled with
1k blobs. When the blob field is filled with 50k blobs the numbers look more
like:
Type 4
Run#3 Run#3
benchmarkPreparedInsert: 13510 benchmarkPreparedInsert: 6539
benchmarkNonPreparedInsert: 10525 benchmarkNonPreparedInsert: 4997
benchmarkBigBlobInsert: 258792 benchmarkBigBlobInsert: 217914
Here the 50k blobs are only inserted in the last test. It shows that the
type 2 modifications begin to loose there edge under these circumstances.
I found these results interesting - if only that it shows how much room
there might be for improvement in the type 4 jgds implementation as this is
all thats different between the versions.
I was supprised by these numbers - I re-ran and re-checked the tests many
times though.
These tests were performed against firebird 1.0.0 on win2k on a p6 1.2Ghz.
Thanks
Ryan
When running some benchmarks on the type 2 driver modifications I came up
with some interesting results. These show that when many small records are
inserted the driver in type 2 mode performs alomost twice as quick as the
pure type 4 driver. The numbers I got are:
Type 4 Type 2
Run#1 Run#1
benchmarkPreparedInsert: 12628 benchmarkPreparedInsert: 6880
benchmarkNonPreparedInsert: 10475 benchmarkNonPreparedInsert: 5398
benchmarkBigBlobInsert: 16234 benchmarkBigBlobInsert: 10655
Run#2 Run#2
benchmarkPreparedInsert: 11246 benchmarkPreparedInsert: 6479
benchmarkNonPreparedInsert: 9925 benchmarkNonPreparedInsert: 5117
benchmarkBigBlobInsert: 15912 benchmarkBigBlobInsert: 10596
Run#3 Run#3
benchmarkPreparedInsert: 11286 benchmarkPreparedInsert: 6519
benchmarkNonPreparedInsert: 9804 benchmarkNonPreparedInsert: 5098
benchmarkBigBlobInsert: 16103 benchmarkBigBlobInsert: 10595
In these tests records are inserted into a table looking like this:
CREATE TABLE ATABLE (
FIELD1 INTEGER NOT NULL,
FIELD2 CHAR(16),
FIELD3 VARCHAR(64) CHARACTER SET ISO8859_1 COLLATE EN_UK,
FIELD4 VARCHAR(256) CHARACTER SET ISO8859_1 COLLATE EN_UK,
FIELD5 BLOB SUB_TYPE 1,
FIELD6 INTEGER,
PRIMARY KEY (FIELD1) ) ;
In each run 10000 records are inserted. In the first two the blob field is
filed with very small blobs. On the last test the blob field is filled with
1k blobs. When the blob field is filled with 50k blobs the numbers look more
like:
Type 4
Run#3 Run#3
benchmarkPreparedInsert: 13510 benchmarkPreparedInsert: 6539
benchmarkNonPreparedInsert: 10525 benchmarkNonPreparedInsert: 4997
benchmarkBigBlobInsert: 258792 benchmarkBigBlobInsert: 217914
Here the 50k blobs are only inserted in the last test. It shows that the
type 2 modifications begin to loose there edge under these circumstances.
I found these results interesting - if only that it shows how much room
there might be for improvement in the type 4 jgds implementation as this is
all thats different between the versions.
I was supprised by these numbers - I re-ran and re-checked the tests many
times though.
These tests were performed against firebird 1.0.0 on win2k on a p6 1.2Ghz.
Thanks
Ryan