SQL FileStream and Entity Framework Performance

Question

Hi all, 

According to this white paper, there are two way of accessing Filestream data. 

One way is by Win32 API and another is thru T-SQL query. In the paper, it also stated that FileStream performance gain only occur if it is access via Win32 and the performance will more worst than Varbinary if it is access via T-SQL.

Entity Framework always generate T-SQL query. Thus, is it a good idea of using FileStream with Entity Framework and what is it performance gain compare to varbinary(max)? 

Answer 1

Hello,

the main advantage of using FileStream is by accessing it as a stream. If you access it with T-SQL query you degrade it to varbinary(max) with some additional infrastructure costs and demands - the white paper contains the graph comparing the performance (throughput). In such case there is no reason to even use file streams. Also the paper you mentioned is old. .NET 3.5 includes managed API to access file stream. Despite the API available, Entity Framework always accesses the file stream as varbinary(max).

Best regards,
Ladislav

Answer 2

While I agree that the main benefit of FileStream is to use the streaming feature, in our case we still gained something from using FileStream + EF: memory usage was significantly lower when we had many operations going on at the same time.

This may have to do with the specifics of what you're doing and/or the amount of data you're storing, so my recommendation would be to test and figure out if you still gain some benefit.