Storing and retrieving images with MVC and Sql Server

Question

I am designing an prototyping an app the needs to store images, similar to facebook. This will be a public facing site and I am not sure how many users I will end up with but what I am looking for is a way to efficiently retrieve them.

So far I am thinking of storing them in SQL Server varbinary columns. Is this a good idea? I have the upload code and the storage code for that. My concern is retrieving them. I can retireve and build the image tag on the fly but I am worried about having to hit the database for each one.

I have been thinking about getting all images for a user and caching them in the asp.net cache for 10 to 30 seconds. I have never had to do something like this so I would be interested in hearing a few different approaches. Obviously the images can vary in size and I was thinking about defining a size limit, but I haven't gotten that far yet.

Thanks for your help. Paul Speranza

Answer 1

I think that IIS already does quite a good job at caching static content so I would probably just save the images on disk and save information about their placement in the DB.

Answer 2

First, you could re-size your images prior to putting them in sql server. Also, you could make a http handler that retrieves images from sql server, but I would set the cache to be like a minute or two, not less. They don't change that much.

context.Response.Cache.SetExpires(DateTime.Now.AddMinutes(10));

Another option is to store on the file server, you can write these files anywhere IIS has permissions, and store the file "url" like "C:\images\test.jpg" in the database. But then you have to write something to delete the file when the record is removed.

Answer 3

Do not store images in your database. It will hurt the performance of both your database and your web site.

The only exception is if you're going to use SQL 2008's FILESTREAM type, which basically uses the file system to store the files and just keeps a reference to it in the DB.

Definitely don't store raw image data in a varbinary column.