Reputation: 2702

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

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.

Upvotes: 96

Answers (11)

D.A.H

Reputation: 919

Interesting topic.

There is no absolutely one correct answer to this question.

There are few key elements to consider:

What’s your database engine?
What’s the route of file from database to end user and/or backwards?
What are the security requirements?

If files are meant for public audience and accessible via website, you shouldn’t even consider storing files in database. Use some smart indexing for files instead.

If files are containing highly sensitive information, then it might be worth of storing these into database. But you have to implement proper safe gateways too.

If performance is crucial, it’s better do not store files in database.

Backup and restoring and migrating of database might become a nightmare if database grows big just because of files. If you are DBA, then you would like to kill the person who “invented” an idea to put files into database.

I recommend to use storing files into database at last option, when there is absolutely no any better alternative available.

Upvotes: 1

Will Dean

Reputation: 39500

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

To BLOB or Not To BLOB: Large Object Storage in a Database or a Filesystem

Upvotes: 8

Michael Stum

Reputation: 180944

Have a look at this answer:

Storing Images in DB - Yea or Nay?

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 (e.g., 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 public web site, you may pay a high price for storing files in the database.

Upvotes: 87

Jon Galloway

Reputation: 53125

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.

Getting Started with FILESTREAM Storage

Upvotes: 39

Marcus Downing

Reputation: 10141

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.

Upvotes: 3

N8g

Reputation: 636

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.

Upvotes: 0

Lance Fisher

Reputation: 25813

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.

Upvotes: 26

Shawn Miller

Reputation: 7080

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.

Upvotes: 2

Jon Limjap

Reputation: 95432

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.

Upvotes: 2

martinsb

Reputation: 945

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

Upvotes: 1

ZombieSheep

Reputation: 29953

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.

Upvotes: 8

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

Answers (11)

Related Questions