current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 1 down vote favorite
1

This question already has an answer here:

We have a ASP.NET MVC project with AngularJS and Entity Framework database-first, a news portal already in production for about a year. The project is mainly focused on providing content for users (articles, news and etc) and of course there is a lot of images. Currently we are storing the images in the File System and in SQL Server we store only the path.

The site today has something like 2000+ images (every day a lot of new images are uploaded so this number can grow very fast). The size of the images are on the average of 1 MB (but can also be larger).

Our client has requested (really don't know the reasons..) for us to start saving the images inside the database, using SQL Server’s FILESTREAM. As a company, we had never developed any project using this technology. We already know that storing BLOB's (as VARCHAR(MAX)) is a really bad approach but what about FILESTREAM? Is it really worth it?

The main objectives are:

  • FILESTREAM will offer better performance over time?
  • What about the way to manage (save, retrieve) images? Is it a lot of different than using System.IO.File ?
  • What about EntityFramework 5? Is it compatible? (Couldn’t really find actual answers for this on the internet)

The user can upload up to 10mb. Also, we have plans to let them also upload files (.pdf, .doc, .xls, even .mp3 max 10mb)

share|improve this question

marked as duplicate by gnat, MichaelT, Dan Pichelman, GlenH7, Kilian Foth Nov 17 '14 at 8:29

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
    
How about storing the stuff in a varbinary field? – Knerd Nov 14 '14 at 13:35
    
@Knerd meta.stackexchange.com/questions/194476/… – gnat Nov 14 '14 at 13:44
    
It might be worth the time and effort to ask why they want it. It sound to me like they're trying to get the images into the database (for reasons which should be obvious... they'd rather not scatter files all over the hard drive), but they understand the disadvantages of BLOB and think that FILESTREAM will overcome those disadvantages. – Robert Harvey Nov 14 '14 at 16:43
1  
See my answer at programmers.stackexchange.com/a/261246/3762 , similarish question. – Wyatt Barnett Nov 14 '14 at 17:24
    
@Robert Oh boy I've already asked that. It's a long and very nasty story which involves bad stuff(corruption) that I can't comment in here. – jpgrassi Nov 14 '14 at 19:10
up vote 1 down vote accepted

Since you're talking about images in a context of a news portal, I imagine that those images are small JPEGs, not RAW files which can easily be 30 MB in size. In that case, FILESTREAM won't be recommended, because its primary purpose is to host large files within the database.

For smaller objects (less than 1MB), storing varbinary(max) BLOBs in the database can provide better streaming performance.

Source: Best Practices on FILESTREAM implementations

FILESTREAM has one major benefit: you can store very large blobs of data without being limited by the amount of memory on your server. By storing this data within the database instead of plain file system, you make it possible to unify data management. For instance, system administrators can define the backup strategy once for the database, and don't have to create a duplicate backup strategy for plain files.

But the benefit stops here. In other words, you get performance nearly identical to what you can get with plain files and the benefit of a single strategy, but there is nothing more there.

If you don't need this unified management of data, don't use FILESTREAM. Unfortunately, FILESTREAM is overrated in Microsoft community, leading many people to use it in projects which don't need it.

FILESTREAM will offer better performance over time?

One of the benefits I can see is that you won't need to query the path of the file any longer. Aside this, you won't see a benefit of using FILESTREAM compared to direct access to a file, because you simply add a layer of complexity.

Beware of the exact location of your actual files and the possible location of FILESTREAMed files: if currently, the files are stored locally, and in FILESTREAM, they will be moved to the machines hosing SQL Server, you'll have to consider the bandwidth usage between your app servers and your database servers.

What about the way to manage (save, retrieve) images? Is it a lot of different than using System.IO.File ?

No. Accessing files is really easy and similar to the way you access an ordinary file. You can easily find the examples on MSDN.

What about EntityFramework 5? Is it compatible? (Couldn’t really find actual answers for this on the internet)

It wasn't supported yet in 2011. I don't know if things changed since then, but in all cases, you'll probably circumvent Entity Framework if you need to access data stored in a FILESTREAM (which won't be difficult either).

share|improve this answer
    
Thanks for your input!. I forgot to say but the user can upload the images up to 10mb. And also, there are plans for also uploading files (.pdf, .doc, .xls, evem .mp3!). – jpgrassi Nov 14 '14 at 13:43
    
@jpgrassi: I think you should ask yourself two questions. (1) Do you need unified management of data? Probably not, unless you can clearly explain why you need it and what would be the undeniable benefits; see the second link in my answer. (2) If you decided to store this data in the database, what would you use, FILESTREAM or varbinary(max)? Here, see the first link in my answer (and the whitepaper the page links to). – MainMa Nov 14 '14 at 13:51
    
Well for me as a developer: 1- NO, we don't need unified management of files. It's working just fine the way it is. 2 - If we decided, it will be FILESTREAM. My problem is, I have to create a report, stating the pros and cons to managers, clients and all that corporate guys. Seriously if was only for me, I wasn't even having this discussion. – jpgrassi Nov 14 '14 at 13:57
    
@jpgrassi: well than you can use my answer as a starting point for your research for your report. I may also suggest to create a small prototype which actually uses the FILESTREAM: this way, you'll be able to see clearly how easy it is, and what problems you may have when implementing it in your application. – MainMa Nov 14 '14 at 14:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.