Hi,

I test general impact of compression and I found that for FB 2.5 and FB3 is critical to take another DB page from cache.

Also any cache miss significantly reduce performance.

With current compression and if we have a number lest say 0x3337 stored in three columns.

BIGINT it will be uncompressed: 3x ( 6x 0x0, 0x33, 0x37). Total: 24 bytes.

BIGINT compressed: 3x ( 2 bytes to compress zeroes + 3 bytes for uncompressable data). Total: 15 bytes.

SMALLINT uncompressed: 3x (0x33, 0x37). Total: 6 bytes.

SMALLINT compressed: 1 byte for uncompressable header + 3x (0x33, 0x37). Total: 7 bytes.

The performance gain can vary from few percent to ten times and it is NON LINEAR.

Conclusion of my testings:

1. Size of compressed row has significant impact on performance

2. SMALLINT will litle help but

3. VARCHAR and CHARACTER SET has much more impact. Mainly UTF8.

4. If performance is not at first place, use INTEGER because upgrade is very problematic (you have to drop all PK, FK and indexes)

Some results from our testing that's focused to new RLE:

http://www.elektlabs.cz/fbrle/

Slavek

Ing. Slavomir Skopalik
Executive Head
Elekt Labs s.r.o.
Collection and evaluation of data from machines and laboratories
by means of system MASA (http://www.elektlabs.cz/m2demo)
-----------------------------------------------------------------
Address:
Elekt Labs s.r.o.
Chaloupky 158
783 72 Velky Tynec
Czech Republic
---------------------------------------------------------------
Mobile: +420 724 207 851
icq:199 118 333
skype:skopaliks
e-mail:skopalik@...
http://www.elektlabs.cz

 

On Fri, Jun 30, 2017 at 10:30 AM, Slavomir Skopalik skopalik@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

advantage of SmallInt vs INT vs BIGINT is data density (keep record shortest as possible).

If you have one column, it is irrelevant, if you have ten columns, you will see speed improvement.

Did you test that, or just assume it?  Remember that data compression also affects

record size.

Cheers,

Ann