In my last post I have described a workaround for using a FileTable in Entity Framework 6.0. In this post I would like to show you the results of performance test of storing big documents in sql FileTable.
I have put in comparison storing 10 pdf documents 16MB each in database.
First application is a Entity Framework Code First based app with mapped stored procedures. Those procedures handles storing and deleting files in sql FileTable. Retreiving data is made with use of sql view mapped to virtual entity. All that is possible when we use and configure EF to connect to an existing database.
The second tested app also uses EF Code First, but this time I let the EF create database automatically. File data is stored in regular table in varbinary(max) column.
Testing method for both cases looks the same:
For a short explanation, in line 3 one big file content is loaded into a byte array. In line 8 the database initializer is called so this operation does not count into overall testing interval. In a for loop (lines 10-15) new document is created with different name but the same binary content and then is saved to the database.
And here are the results (each in milliseconds for storing 10 big files). The testing method was run 20 times for each approach:
Regular varbinary column approach
As shown using File Table to store blob data is about 6 times faster than storing files in regular sql table.