This is my second post about some of the performance improvements you will experience when upgrading to SQL Server 2008 and Visual Studio 2008 SP1.
In this one we will be taking FILESTREAM for a test ride.
During my session at Developer Academy 3 I will be describing how this new feature in SQL Server 2008 can be leveraged from Visual Studio 2008 (SP1) to provide the data integrity and manageability of SQL Server with the access speed of an NTFS file.
In preparation, I decided to measure the performance of FILESTREAM using the project attached to this post.
The project compares the reading and writing speeds to and from a BLOB using three methods:
Reading and writing from a plain vanilla NTFS file. This method is used when BLOBs are stored on the file system and only their URLs are stored in the database.
- SQL Server 2005:
Reading a BLOB from the database using DataReader in SequentialAccess mode. Writing a BLOB using the TEXTPTR and UPDATETEXT SQL Commands.
- SQL Server 2008:
Speeds are calculated as the throughput in bytes per second of reads/writes of 100 blocks of 102400 bytes each.
The following table presents the average and standard deviation of these speeds over 100 repetitions:
Using the code:
- You must be using SQL Server 2008 and Visual Studio 2008 SP1.
- Download the code from the bottom of this post.
- Run the RunThisFirst.sql against the server.
- Modify the connection string in the project Settings.
- The code should be self-explanatory, but let me know if not.
- Comparison to NTFS:
FILESTREAM is as fast as NTFS on writes and about 10% slower than NTFS on reads.
- Comparison to BLOB technologies on SQL Server 2005:
FILESTREAM improves throughput by a factor of 45 for writes and 7 for reads.
See you on Monday !