Which is better : Storing/retrieving images on/from SQL server or in a directory on server

Question

I am working on a project in Asp.net MVC and need to work with images. There is an SQL database with a Product table. Every product in the table will have it's own image. I have two ways to do this :

1) Save the image in a web directory and store the URL on database.

2) Store the image in SQL itself in binary format and then retrieve it.

Which is a better approach ? Mind you, I have no idea how second method works :-P . I will only learn this if there are merits to the second method

Answer 1

I prefer to put binary in the database, but that's primarily because

• I know where it is, and I don't need a systems guy to set up a network share or webserver.
• I can control who can retrieve the data through my application, rather than rely on permissions on the file system. I can do it on a very granular level very easily.
• I get much simpler transactional integrity when inserting files than I would with a file system.

The best argument against database storage are around size - the database might become unmanageable. Much more difficult to do database backups & maintenance; the database is one huge file, whereas if the files are external then you can have some over here and some over there.

If you're using Microsoft, perhaps the best compromise is the FileStream type. It stores the filepath in the database and the files in a folder, but the folder is essentially controlled by SQL. You 'insert' files to the database and they're saved in the folder, and when you 'select' from the database the server will retrieve them from the folder and serve through through the database. In this way you get the best of both worlds - transactions and control, plus the size efficiency of keeping the database small.

Answer 2

Something very similar was discussed where i had answered which was inevitably method 1. You can see here a similar discussion: How to add image support to client-server database application?

If you ask why, the fundamental reason why i prefer web method is that when any application demands the actual image, it has following single point of failures.

1. It becomes a must that finally the database needs to retrieve. The retrieval from DB is more expensive (to other transaction) than retrieval from file system.

2. Usually there is always some server layer (ASP or C#) which actually makes call to DB (clients, or web browser don't directly call DB). hence, the path has additional point of bottleneck. If there is a static URL (possibly with auth) - the browser can directly fetch rather than going via application server.

3. Most importantly I guess, when delivering images to many browsers (from a web servers) scaling is much easier that way rather than other way.

4. Last but most, in many cases, images retrieved using static URLs, can be easily cached by the browser itself to save more resource.

I agree with above answer and the criteria in "To BLOB or Not To BLOB" but most answers focus on speed of retrieval and size of database as a criteria.

My point is that there is additional criteria - is how often images are going to change, and when and how we want deliver these images is equally a critical criteria.

If content of the image is going to keep changing and application logic needs to process them before delivering it makes more sense to deliver via app server (i.e. you can keep in DB). Where as if images by nature itself is quite static - not changing often and needs to be delivered so many times, (set once read/deliver many times) scenario then, it the web is always a preferred method. this applies irrespective of performance of DB.

Answer 3

Although this has been asked on StackOverflow, I think there is an automatic assumption among programmers on this subject that may not always hold true, so I'm answering it here. There are situations where placing in the database may be better.

https://stackoverflow.com/questions/5613898/what-about-storing-images-in-sql-server There is a link here that shows file size may matter and placing small images in a database may be better for performance. Currently, 250K to 1 M seems to be the grey area. This is specific to SQL Server 2008. I'm guessing more relational databases are going to break their mold a bit and begin to improve their ability to handle large blobs of data.

They may even be better at indexing and searching blob content. Can't wait for the day when I can query all pictures for those Where Photos.File.FaceCount > 1 to get group photos without someone having to manually create the metadata.