Solved

Store images to SQL database vs file system

Posted on 2009-04-13
3
1,402 Views
Last Modified: 2012-08-13
Using:
  MS SQL 2005/2008
  ASP.NET (C#) 2.0/3.x

We are developing a classifieds type website where a user can upload 1 to 5 images. Each image will be less than 50K in size, and will have thumbnails created for each image uploaded. Some of the benifits of using SQL database to store images, deleting a user record, the users images can be deleted at the same time. This helps with backup issues and orphan images are addressed also.

Does it make sense performance-wise to go in this direction rather than storing the images in the file system?

In this type of site, what would be the most effecient way to retrieve multiple images at once to show on a page. An example would be where a website user clicks on an ad, and the page would serve up the ad text and any thumbnail images related to that ad ID, and each would be clickable to view the larger image. I have done this storing images in the file system, but I'm a little fuzzy on retrieving multiple thumbnail images and also links to the larger images from a SQL server.

Also, should thumbnails be created and stored as seperate images in the database or generated on the fly when the page is requested?

Thanks Lots!
0
Comment
Question by:IntelOne
  • 2
3 Comments
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 24134388
If you are using SQL Server 2008, then there is a hybrid approach using the new FILESTREAM data type.  Take a look at this article:
SQL Server 2008: The New Data Types
http://www.simple-talk.com/sql/learn-sql-server/sql-server-2008-the-new-data-types/
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 300 total points
ID: 24134985
Definitely outside the database...

Even MS have recognised it and created a new type/function, FILESTREAM as acperkins mentions above. It is good, but it also means that SQL Server "OWNS" the images. They are stored externally, but considered part of the database "suite" so to speak. Meaning included in backups and all that stuff without bloating the actual database. It is a slightly different approach, and does take a bit of management, for a start the SQL Server service owner effectively becomes the owner, so it does require a bit of planning.

I still prefer external files when they are more "user bound" images. For example, a product image from the inventory master is ideally suited to FILESTREAM, we as user supplied images can be more easily managed over a shared network drive space.

The other thing is if external packages need to interact with the image. FILESTREAM is really a finished goods type proposition, true it can manage versions a lot better, but is more of a submission of "here it is" and the resulting file is no longer recognised (well by name at least) - SQL does it's own thing with the actual files.

So, horses for courses as to when filestream is best used over external files with a path held in the database. Main thing is "who is going to own the file" and may well depend on how other applications are going to interact with it.

In your case, being classifieds, it would be a toss of the coin. There are distinct advantages in using FILESTREAM, after all it is like an inventory type system, and will be backed up as well, and then there are flexibilities in external images. Over the web, you need to consider speed and traffic. With SQL being the owner, it will also be delivering the image. Externally, you might be able to get away with an image library on the web server and relieve some network traffic.

Bottom line. External - your choice filestream or flat files with a path in the DB - either way.

here is some other links for you :

MS details : http://technet.microsoft.com/en-us/library/bb933993.aspx
intro / discussion : http://www.sqlmag.com/articles/index.cfm?articleid=101388&   (might need to register, and is worthwhile)
with samples : http://blogs.msdn.com/manisblog/archive/2007/10/21/filestream-data-type-sql-server-2008.aspx
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 300 total points
ID: 24134994
Oh, and just so you are not feeling alone, have a search in EE for Filestream and sort by date - there is quite a lot of good reading, and fixes, and help, and code examples...

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL 2012 Syntax Error 5 25
Data center mess 4 49
sqlseverexpress 2008 agent xps question 1 12
SQL Transaction logs 8 11
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now