FileTable performance test

By Mirek on (tags: Entity Framework, FILETABLE, SQL, categories: architecture)

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:

 

   1: private static long TestLoad10BigFiles(int startidx)
   2: {
   3:     byte[] fileData = File.ReadAllBytes(@"D:\temp\bigdocument.pdf");
   4:     Stopwatch clock = new Stopwatch();
   5:     DocumentsView doc = null;
   6:     using (var ctx = new DocumentsContext())
   7:     {
   8:         ctx.Database.Initialize(false);
   9:         clock.Start();
  10:         for (int idx = startidx; idx < startidx + 10; idx++)
  11:         {
  12:             doc = new DocumentsView { name = idx.ToString() + ".pdf", file_stream = fileData };
  13:             ctx.DocumentsView.Add(doc);
  14:             ctx.SaveChanges();
  15:         }
  16:         clock.Stop();
  17:     }
  18:     return clock.ElapsedMilliseconds;
  19: }

 

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

29635, 26582, 27609, 27534, 50632, 52636, 54887, 65178, 27730, 28882, 29193, 25734, 31621, 31686, 33995, 35256,
37840, 34097, 29223, 25200
AVERAGE: 35258

FileTable approach

5746, 5283, 5397, 4479, 5824, 7261, 5683, 7455, 5727, 7279, 5788, 6118, 6141, 5989, 6495, 7619, 7017, 5830, 5609, 5570
AVERAGE: 6115

As shown using File Table to store blob data is about 6 times faster  than storing files in regular sql table.