Storing a file in a database as opposed to the file system?

Question

Generally, how bad of a performance hit is storing a file in a database (specifically mssql) as opposed to the file system? I can't come up with a reason outside of application portability that I would want to store my files as varbinaries in SQL Server.

Answer 1

In my own experience, it is always better to store files as files. The reason is that the filesystem is optimised for file storeage, whereas a database is not. Of course, there are some exceptions (e.g. the much heralded next-gen MS filesystem is supposed to be built on top of SQL server), but in general that's my rule.

Answer 2

Have a look at this thread:

http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay#3756

Essentially, the space and performance hit can be quite big, depending on the number of users. Also, keep in mind that Web Servers are cheap and you can easily add more to balance the load, whereas the database is the most expensive and hardest to scale part of a web architecture usually.

There are some opposite examples (i.e. Microsoft Sharepoint), but usually, storing files in the database is not a good idea.

Unless possibly you write desktop apps and/or know roughly how many users you will ever have, but on something as random and unexpectable like a oublic web site, you may pay a high price for storing files in the database.

Answer 3

What's the question here?

Modern DBMS SQL2008 have a variety of ways of dealing with BLOBs which aren't just sticking in them in a table. There are pros and cons, of course, and you might need to think about it a little deeper.

This is an interesting paper, by the late (?) Jim Gray

http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45

Answer 4

I agree with @ZombieSheep. Just one more thing - I generally don't think that databases actually need be portable because you miss all the features your DBMS vendor provides. I think that migrating to another database would be the last thing one would consider. Just my $.02

Answer 5

The overhead of having to parse a blob (image) into a byte array and then write it to disk in the proper file name and then reading it is enough of an overhead hit to discourage you from doing this too often, especially if the files are rather large.

Answer 6

If you can move to SQL Server 2008, you can take advantage of the FILESTREAM support which gives you the best of both - the files are stored in the filesystem, but the database integration is much better than just storing a filepath in a varchar field. Your query can return a standard .NET file stream, which makes the integration a lot simpler.

http://msdn.microsoft.com/en-us/library/bb933995.aspx

Answer 7

We made the decision to store as varbinary for http://www.freshlogicstudios.com/Products/Folders/ halfway expecting performance issues. I can say that we've been pleasantly surprised at how well it's worked out.

Answer 8

I'd say, it depends on your situation. For example, I work in local government, and we have lots of images like mugshots, etc. We don't have a high number of users, but we need to have good security and auditing around the data. The database is a better solution for us since it makes this easier and we aren't going to run into scaling problems.

Answer 9

Not to be vague or anything but I think the type of 'file' you will be storing is one of the biggest determining factors. If you essentially talking about a large text field which could be stored as file my preference would be for db storage.

Answer 10

While performance is an issue, I think modern database designs have made it much less of an issue for small files.

Performance aside, it also depends on just how tightly-coupled the data is. If the file contains data that is closely related to the fields of the database, then it conceptually belongs close to it and may be stored in a blob. If it contains information which could potentially relate to multiple records or may have some use outside of the context of the database, then it belongs outside. For example, an image on a web page is fetched on a separate request from the page that links to it, so it may belong outside (depending on the specific design and security considerations).

Our compromise, and I don't promise it's the best, has been to store smallish XML files in the database but images and other files outside it.

Answer 11

We store it in database and here is the detailed analysis of why its better.

http://akashkava.com/blog/?p=127