During the various courses I've been teaching, people are interested in how FILESTREAM performance compares with storing BLOBs in the database itself. I have some performance graphs based on measurements the dev team made – these have been presented publicly by myself and the dev team so I can share them with you here.
There are three graphs below, showing the relative performance for read, insert, and update of:
- BLOB data stored in FILESTREAM format and accessed through the WIN32 streaming APIs. The times include getting a transaction context from SQL Server, getting the file path, doing the operation, closing the file, and committing the transaction in SQL Server.
- BLOB data stored in FILESTREAM format and manipulated through T-SQL
- BLOB data stored in varbinary(max) format (and obviously manipulated through T-SQL)
The data is the same in each test. The tests were performed on a 4-way box with a cold buffer pool. (Note that if this was for a warm-buffer pool, as the graph in the FILESTREAM whitepaper is for, the varbinary(max) and FILESTREAM T-SQL numbers would essentially swap.) One interesting point to note is that for smaller data sizes, it's faster to manipulate them through T-SQL than through the file system – this is expected based on research Jim Gray did when putting together the original TerraServer.