Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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

share|improve this question
4  
See this post: stackoverflow.com/questions/3748/… –  user2567 Nov 13 '11 at 13:15
1  
thanks mate.....great great link....i searched for "image sql asp.net" and didnt found it.... Please post this in an answer so that i can accept it –  Pankaj Upadhyay Nov 13 '11 at 13:21
1  
Relational databases can store binary data (blobs) but they weren't designed to do so. If you don't have a very specific reason to use a database, you should store just the path to the image. –  Yannis Rizos Nov 13 '11 at 13:22
    
ya...thats what i am going to do....Besides i know this and its way easier. :-) –  Pankaj Upadhyay Nov 13 '11 at 13:24

3 Answers 3

up vote 7 down vote accepted

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.

share|improve this answer
2  
the database might become unmanageable. The database will become unmanageable. –  Yannis Rizos Nov 14 '11 at 7:14
    
@YannisRizos And a million monkeys banging a keyboard will eventually write the combined works of Shakespeare. I mean within its reasonable life. –  Kirk Broadhurst Nov 14 '11 at 8:44
1  
I now... Little bitter, as I've spent past few weeks trying to migrate 2TB of images from mysql to S3. BTW "I never thought it would get that big", is not a good excuse for when the database actually becomes unmanageable. Your answer is solid, but since people tend to browse the bullets and not actually read the text a lot may miss the whole point. (their problem not yours) –  Yannis Rizos Nov 14 '11 at 8:57

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.

http://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.

share|improve this answer

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.

share|improve this answer
    
What is a web server as opposed to an app server? I don't think this answers the question at all - whether the files are retrieved via a file handler or a WCF service is not at all relevant to where they are stored. I would assume that the delivery mechanism would be the same regardless of the OP's choice of database or filesystem. –  Kirk Broadhurst Nov 14 '11 at 7:01
    
Case 1: URL= mydomain.com/getphoto.asp?q=x where asp in question is "processed by way of query to db and return the relevant MIME type". Case 2: URL = mydomain.com/images/photo_11234.jpg. here the web server doesn't require any computation except reading from file and deliver and also wont require to check with DB. I have used the terms just to distinguish two form where process the amount of work varies significantly with respect to delivery. If this is modify once deliver many times the web server approach is better. –  Dipan Mehta Nov 14 '11 at 9:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.