Subject Re: [firebird-support] BLOB performance
Author Alexey Kovjazin
Hello, Ann!

==========================cit ======================
> Is that true? Would I be better off moving the BLOB field into a
> separate table or even file?

No. Blobs are not part of the record, but are stored on data pages in
the database. When you retrieve the record, the "normal" fields you
request are returned. If you include the blob field, you get a number
that tells the system where to find the blob. You then go back and get
the blob if it exists. [_So blobs don't slow down "normal" fields_]
(selection is mine - AK).
Going back to get the blob is cheaper than opening a second table - let
opening a second file.

May be, I am wrong, but I think that BLOB reading and writing becomes
expensive when BLOBs are small and placed on the same data page with other
columns, am I right?

So, my assumption why small BLOBs are slowing down:

1) To read BLOBs engine needs to read page twice (in second time it will be
in cache, of course) because different APIs are using to get non-BLOB and
BLOB columns

2) Small BLOBs are not small from the point of view of data page - when
BLOBs occupy big % of data page, so fewer records can fit on page (the
situation looks worse when we have large fragmented records and small BLOBs)

As a summary, I did not recommend use BLOBs for small data when they may fit
on data page - VARCHAR in this case looks better.

Please resolve my doubts - was I wrong?

Sincerely yours,
Alexey Kovyazin