138

I'm building an ASP .NET web solution that will include a lot of pictures and hopefully a fair amount of traffic. I do really want to achieve performance.

Should I save the pictures in the Database or on the File system? And regardless the answer I'm more interested in why choosing a specific way.

Braiam
  • 1
  • 11
  • 47
  • 78
StefanE
  • 7,578
  • 10
  • 48
  • 75

9 Answers9

190

Store the pictures on the file system and picture locations in the database.

Why? Because...

  1. You will be able to serve the pictures as static files.
  2. No database access or application code will be required to fetch the pictures.
  3. The images could be served from a different server to improve performance.
  4. It will reduce database bottleneck.
  5. The database ultimately stores its data on the file system.
  6. Images can be easily cached when stored on the file system.
Andy
  • 17,423
  • 9
  • 52
  • 69
Akbar ibrahim
  • 5,110
  • 3
  • 26
  • 23
  • 2
    Also, in SQL Server when you store the image as an "Image" field, this is effectively what SQL is doing - storing a pointer to the file on disk somewhere. This is how it gets around the 8KB page limit. – Zhaph - Ben Duguid Feb 18 '09 at 16:55
  • 10
    This actually has been addressed is SQL Server 2008. There was a new type introduced FILESTREAM http://technet.microsoft.com/en-us/library/bb895234.aspx which allowes to take advantage of "performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data" – kristof May 12 '09 at 13:25
  • Yep you wrer right. my question is what is the size of blob? or how much memory it can store? – Ameer Mar 04 '13 at 08:45
12

In my recently developed projects, I stored images (and all kinds of binary documents) as image columns in database tables.

The advantage of having files stored in the database is obviously that you do not end up with unreferenced files on the harddisk if a record is deleted, since synchronization between database (= meta data) and harddisk (= file storage) is not built-in and has to be programmed manually.

Using today's technology, I suggest you store images in SQL Server 2008 FILESTREAM columns (at least that's what I am going to do with my next project), since they combine the advantage of storing data in database AND having large binaries in separate files (at least according to advertising ;) )

devio
  • 36,858
  • 7
  • 80
  • 143
12

The adage has always been "Files in the filesystem, file metadata in the database"

Matt Darby
  • 6,294
  • 4
  • 27
  • 35
7

Better to store files as files. Different databses handle Blob data differently, so if you have to migrate your back end you might get into trouble.

When serving the impages an < img src= to a file that already exists on the server is likely to be quicker than making a temporary file from the database field and pointing the < img tag to that.

I found this answer from googling your question and reading the comments at http://databases.aspfaq.com/database/should-i-store-images-in-the-database-or-the-filesystem.html

sparklewhiskers
  • 910
  • 8
  • 19
6

i usually like to have binary files in the database because :

  • data integrity : no unreferenced file, no path in the db without any file associated
  • data consistency : take a database dump and that's all. no "O i forgot to targz this data directory."
chburd
  • 4,131
  • 28
  • 33
5

Storing images in the database adds a DB overhead to serve single images and makes it hard to offload to alternate storage (S3, Akami) if you grow to that level. Storing them in the database makes it much easier to move your app to a different server since it's only the DB that needs to move now.

Storing images on the disk makes it easy to offload to alternate storage, makes images static elements so you don't have to mess about with HTTP headers in your web app to make the images cacheable. The downside is if you ever move your app to a different server you need to remember to move the images too; something that's easily forgotten.

Adam Hawes
  • 5,439
  • 1
  • 23
  • 30
4

For web based applications, you're going to get better performance out of using the file system for storing your images. Doing so will allow you to easily implement caching of the images at multiple levels within your application. There are some advantages to storing images in a database, but most of the time those advantages come with client based applications.

Dillie-O
  • 29,277
  • 14
  • 101
  • 140
0
  1. Here is a step-by-step example (general approach, Spring implementation, Eclipse) of storing images in file system and holding their metadata in DB -- http://www.devmanuals.com/tutorials/java/spring/spring3/mvc/Spring3MVCImageUpload.html
  2. Here is an example too -- http://www.journaldev.com/2573/spring-mvc-file-upload-example-tutorial-single-and-multiple-files
  3. Also you can investigate a codebase of this project -- https://github.com/jdmr/fileUpload . Pay attention to this controller.
Lord Nighton
  • 1,670
  • 21
  • 15
0

Just to add some more to the already good answers so far. You can still get the benefits of caching from both the web level maybe and the database level if you go the route keeping you images in the database.

I think for the database you can achieve this by how you store the images with relation to the textual data associated with them and if you can the access to the images into a particular query so that the database can cache the query (just theory though so feel free to nuke me on that part).

With the web side, I would guess since you're question is tagged up with asp.net that you would go the route of using a http handler to serve up the images. Then you have all the benefits of the framework at your disposal and you can keep you domain logic cleaner with only having to pass the key to your image to the http handler.

MotoWilliams
  • 1,538
  • 1
  • 12
  • 22