2

I'm creating an Asp.Net MVC website.

I've in the past, for heavy application, multiple layer application, used the database to store files.

But now I'm questioning myself, is this a good idea for a website? In a performance view?

It has several pros to me:

  • Allows me to control easily if the connected user has the right to display the image(Required for my project)
  • Permits to be sure that we have consistent data(otherwise we can have an existing file but no info in the database and the opposite
  • I need a fail-over webserver, and those files will be imported from a third server, so if those files are in the database, I only need to have a working ASP.Net website and a replicated database on the failover server, no need to sync files.

But it has some cons too:

  • There SOME big files(it's a minority, but it will happen), like 100-200MB, and I'm not sure it's good to have this kind of file in a database?(it's more like a question ;) )
  • I'm not sure that it will have good performances?

What do you think? Is this reasonable? I searched on the Internet, but I didn't found some kinds of arguments for website. My question is mostly about FILESTREAM VS FILESYSTEM, I'm sure that FileStream is slower, but a lot? Because if it's only some percent, the gain of functionality worth it.

J4N
  • 19,480
  • 39
  • 187
  • 340

3 Answers3

7

There's a really good paper by Microsoft Research called To Blob or Not To Blob.

Their conclusion after a large number of performance tests and analysis is this:

  • if your pictures or document are typically below 256K in size, storing them in a database VARBINARY column is more efficient

  • if your pictures or document are typically over 1 MB in size, storing them in the filesystem is more efficient (and with SQL Server 2008's FILESTREAM attribute, they're still under transactional control and part of the database)

  • in between those two, it's a bit of a toss-up depending on your use

If you decide to put your pictures into a SQL Server table, I would strongly recommend using a separate table for storing those pictures - do not store the employee foto in the employee table - keep them in a separate table. That way, the Employee table can stay lean and mean and very efficient, assuming you don't always need to select the employee foto, too, as part of your queries.

For filegroups, check out Files and Filegroup Architecture for an intro. Basically, you would either create your database with a separate filegroup for large data structures right from the beginning, or add an additional filegroup later. Let's call it "LARGE_DATA".

Now, whenever you have a new table to create which needs to store VARCHAR(MAX) or VARBINARY(MAX) columns, you can specify this file group for the large data:

 CREATE TABLE dbo.YourTable
     (....... define the fields here ......)
     ON Data                   -- the basic "Data" filegroup for the regular data
     TEXTIMAGE_ON LARGE_DATA   -- the filegroup for large chunks of data

Check out the MSDN intro on filegroups, and play around with it!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • In my case, it will be stored in a "File" table, only with some metadata(name, size), so I suppose I can keep this field in the same table. You give me a lot of good comment, but in doesn't really help me to choose between filestream and filesystem, since your link is comparing BLOB vs Filesystem(very instructive BTW). – J4N Jan 05 '12 at 09:03
4

If the files are integral and actively changing part of the system and them have to be backed up along with the other data - you can store them inside the DB, but try to use the FILESTREAM fields if you use sql server 2005+ and your files are big enough - say 500k+

If the files are static content, you can store them outside with only pointers in DB. This not prevents to take into account all your custom permissions machine.

Storing and working with files inside DB is usually slower, than in filesystem, but all depends on your needs.

Oleg Dok
  • 21,109
  • 4
  • 45
  • 54
  • In my case, it's really business data, which will not change a lot(+10-20 files(0.5 to 200MB, most of file will be 2-3Mo) pro week). Do you know how much using filestream is slower than using filesystem? – J4N Jan 05 '12 at 09:09
  • 1
    PROPER using filestream is NOT slower than using filesystem, the DB is little slower when you store files in pure varbinary(max) columns – Oleg Dok Jan 05 '12 at 09:19
  • 1
    If you need these business data always be in sync with other data, especially in case of backup/restore operations - you need to keep these files in the DB – Oleg Dok Jan 05 '12 at 09:48
  • Thank you, have you a link with common things to be carefull with when using filestream? – J4N Jan 05 '12 at 11:59
  • Common things are... Two! 8-) Do not partly update the filestream records and don't even thing of touching its underlying files directly 8-) – Oleg Dok Jan 05 '12 at 12:05
3

Why use db instead of txt files? Because its faster it uses indexes. Storing whole files in db is never a good practice. Use db as index (pointers) to the normal img files.

As far as your pros / cons go:

Miha Trtnik
  • 236
  • 1
  • 3
  • 8
  • Thank you for your great arguments. But I thing there is a big difference between MySql and MS Sql, MS SQL has FILESTREAM type, which(I'm not an expert) are supposed to be much more efficient than a BLOB field. (and in my case, those files are only available for logged-validated-users(it's business stuff, only here for one process, so it will never be available through CDN) – J4N Jan 05 '12 at 08:52
  • Hi Miha, Can you please elaborate a bit more about "Use db as index (pointers) to the normal img files.". Do you mean store your images physically on disk in win directory and store the path in DB? " – Alok Jan 05 '12 at 09:08
  • @Alok - In most cases its best to use table for storing image data (title, id, type etc) so you can quickly find the image you want and storing physical image on the filesystem. There is a good discussion about this here: http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay – Miha Trtnik Jan 05 '12 at 13:03