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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Storing a file in a database as opposed to the file system?

Examples related to sql-server

Examples related to database

Examples related to filesystems

Examples related to storage