CODEWITHSUNDEEP
firebird
Phil
Phil

Reputation: 4069

Firebird Datatypes - BLOB Sub Type 1 or VARCHAR(8000)

I'm creating a column which needs to holds data that could potentially be very lengthy. It should not hit 8000 characters, so I was considering using the Firebird VARCHAR(8000) datatype - however I want to do this the right way.

Would it be more efficient to use varchar(8000) or BLOB sub_type 1? Wouldn't the varchar(8000) pre-allocate memory to accommodate the full 8000 chars?

Which would be better?

Upvotes: 1

Views: 2758

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109090

Neither choice is positively better under all circumstances, so in that regard your question is opinion-based and not really answerable.

The choice of VARCHAR(8000) has the advantage that the value is stored inline with the rest of the record, reducing the overhead of inserting and querying the data involved. As downsides it may indeed allocate more memory in certain buffers (I don't actually think this should really be considered a downside though, and I'm not sure of all implementation details there), and given the restriction of row sizes to 32768 bytes, a VARCHAR(8000) will - depending on the character set - take +/- 8000 - 32000 bytes from that maximum. The actual storage size will be smaller due to RLE compression of the record.

A blob sub_type text is stored out-of-band and only takes 8 bytes in the row itself, but the actual size can be slightly less than 4GiB for page size 4KB, and larger for larger page sizes (though some built-in functions may not work beyond 4GiB), but the blob needs to be created or read separately from the row itself, so inserting and retrieving values has additional overhead. Additionally, as blobs are allocated in pages, it can have additional storage inefficiencies (e.g. a blob of 8000 characters in a database with a page size of 16384 will use 16384 bytes).

In short, you will need to weigh your options, maybe even perform some testing, and then make a choice (and maybe revisit that choice when circumstances change).

For example, if most access to this table does not need the content of this field, then using blob will probably be the better choice (you'll only read the data when you really need it), on the other hand if each access to this table also needs this data, then using varchar will probably be the better choice as the data will be loaded inline without having to separately read the blob.

Upvotes: 5

Related Questions