What exactly does it mean that storing “large blobs in the database reduces performance”?

Question

To someone who knows database internals this may be an easy question, but can someone explain in a clear way why storing large blobs (say 400 MB movies) in the database is supposed to decrease performance and what exactly does that mean? This is a claim often found throughout the internet, but I've never seen it really explained.

To be specific, I'm referring to SharePoint/MSSQL performance, i.e. file-upload performance, site browsing, displaying lists, document opening etc. - operations that are said to become slower once a database gets too big. Blob externalization to filesystem (which in SharePoint is called Remote Blob Storage, aka moving files out of the database, leaving only a reference) is supposed to solve this to an extent, but what exactly - at the bottom level - is the difference? It's obvious that backups would take longer with giant files stored in the database ... but what operations exactly are impacted and what's the underlying mechanism of it (i.e. in what way are files stored on filesystem outside of the database accessed or stored differently)?

Suppose a simple table containing columns ID(guid, PK), FileName(string), Data(varbinary(max)) - would large Data column really slow down operations such as displaying a list of files on a website (which I assume internally means running SELECT FileName FROM table), or inserting a new row? It's not like the actual binary content columns are indexed.

I know there have been some questions like this asked already, but I've not found an adequate explanation.

Answer 1

This really depends on the DB system, but one major thing you have to consider with BLOBs is transaction processing. By externalization to the filesystem, one takes changes to the binary data out of the transactions. That will typically result in faster write operations, opposed to the situation where the DB assures you ACID compliance with full rollback mechanisms etc.

Slower read operations hypothetically can also occur, when you retrieve data from your db from a BLOB table without actually selecting the BLOB data, since the DB may store the remaining rows more localized on disk, which will allow faster read access (but I guess most modern DB sytems are clever enough to store the actual binary data in a separated disk area or table space, so without testing this with a real world scenario one should not make any general assumptions here).

Answer 2

It's usually an issue with bandwidth. If you're serving up hundreds of videos an hour, then you're tying up the bandwidth in and out of the database, mostly copying buffers. It's also an issue if you have naive queries (possibly auto-generated by an ORM tool) that simply select all columns from a table. You're also subject to file fragmentation like a filesystem (except in this case it's record fragmentation), but (usually) without any tools to de-fragment. If you're also modifying the BLOB (e.g., you're supporting some kind of video editing), then the database will copy the entire BLOB to the rollback or redo segment, then write the updated BLOB to the database. So now you're copying those several hundred megabytes around, and tying up the redo segment until the transaction ends (not to mention the problems you can run into if the redo segment size is fixed).

Answer 3

You may want to look into SQL Server FileTables. The idea is to provide the best of both worlds: file system level access and performance, along with database access and integrated security and services. The database does have a performance over-head in some cases. Just compare a hard-coded HTML file on a webserver to one that has to fetch the contents from a database.

Imagine an application that didn't find storing blobs in the database was a significant limit to performance, but then the app grew to the point where it was. There's less of a coding change using FileTables. Also, you can manage the transaction at the database level and file level without a lot of coding. The file and meta data are available with SQL.

On the Windows Server, a share drive is created to access the files without using the database transaction over-head.

It's a common problem that Microsoft has attempted to handle "out of the box" with SQL Server 2012. Not a bad feature to justify an upgrade.

Answer 4

To know why this is ugly, you have to know how a database is saved on the hard drive (specifically rows). The physical contents of a row saved on the disk is divided into its static and dynamic counterparts. Fields such as int, byte, char(n) which have a fixed length are listed first. What follows is a number of fixed length which refers to the number of variable length fields to follow. All variable fields (regardless of the order of the columns presented to you, the programmer) are added at the end, each with a number of fixed length which determines how much space the variable length field occupies.

To give you a concrete example. Suppose my table is the following:

char(3) A
varchar(4) B
int C

Now suppose I do INSERT INTO mytable (A, B, C) VALUES ('AAA', 'B', 256). On the database, that row would probably be stored as the following:

Field A gets saved as you'd expect. Had I inserted 'A', it would have provided a special character to mark the premature end of the string after the first character, but it would occupy the same space.

Field C gets saved as the binary equivalent of 256. Why C and not B? C is the next static field with fixed length, and as such, it gets grouped together with all other static data in the database row.

Field D is meta information for the database which indicates that in the following variable length fields section, there will be precisely 1 field.

Field E is again meta information for the database which indicates that for this particular field, it is at most 1 character in length. This information is essential because otherwise the database would not know where field B ends and another variable length field begins.

All of this to demonstrate how databases handle saving variable-length fields. BLOB is very much a variable-length field to this effect. The database structure allows one row to contain both small and large values in the BLOB, however, there are other factors at play here. Databases normally deal with chunks of information since disks don't care about the contents but rather if it fits in a single chunk.

The database will try to fit as many rows into a single chunk without having to separate a row into two pieces, because otherwise the effect is the same as having a fragmented file on your hard drive. Once one chunk is loaded, if the row overflows that particular chunk, the hard drive must then search for the rest of it in another chunk. Worse still, there is no way a database can know that a row occupies more than a chunk without fully reading its contents since it is variable-length, so you cannot optimize by fetching both chunks at once.

Following this line of logic, if you could make a static-length BLOB, you wouldn't have this optimization problem, since the database could simply guarantee that the chunk size is larger than the minimum row size thereby ensuring that most rows won't have to be divided across multiple chunks. Of course, databases don't do this because it would mean dedicating precious space when you probably won't need it.

BLOBS are fine when you're dealing with relatively small amounts, but for large files like videos and the like, a common workaround is simply to save the file path in the database and let the software deal with loading the file which is almost always more efficient.

Hope that explains it. :)