Last post Mar 10, 2010 03:31 PM by atconway
Mar 09, 2010 10:33 AM|egraffius|LINK
I am working on an online classified ad system that will allow the use of multiple images per each ad. The images will be less than 50k. I know there are tons of articles in the debate over storing these in a DB vs. a file system. If I could model a
site that has great performace and is image intensive, it would be cars.com. They seem to use a files system. Can you get performance like they have with a MSSQL2005/2008?
From what I hear, folders with more than 3000 images cause performance issues. Looking at their structure, it appears they have a numbering scheme for their file system to organize and manage folder size.
Cars.com has an image directory structure like so.
The images sizes are 2k, 16k, and 50k respectively.
How do you manage these images on the file system. Once the add is deleted from the database, how do you make sure the associated files on the files sytem are deleted?
ASP .net 2. 0
asp .net 3.5
Mar 09, 2010 10:40 AM|SGWellens|LINK
Once the add is deleted from the database, how do you make sure the associated files on the files sytem are deleted?
You write good, fault tolerant code. Maybe you delete the images first and if successful, then delete the record in the database.
It's possible they have a low priority thread that looks for orphaned images and deletes them.
It's possible they datetime stamp the image files to match the expired date of ads. Then, occassionally, they search for and delete 'old' files.
Mar 09, 2010 12:14 PM|atconway|LINK
I know there are tons of articles in the debate over storing these in a DB vs. a file system.
Yes you are correct. Since you asked about it, read the following for performance consideration such as you have mentioned:
Which is better ? Storing images in BLOB or in the File System ?
Once the add is deleted from the database, how do you make sure the associated files on the files system are deleted?
I typically do this at the same time. Really deleting one or the other and then having an orphan either on the FileSystem or the DataBase are both an issue, but it is probably preferable to delete the file off of the filesystem 1st, and then the record
for the database. You could always run a service in the background, to check all of the records in the database and their associated files, and to delete any orphan records in the database that do not have an associated file.
But honestly, a simple 'Delete' statement on the database by ID should rarely have issues, so if you build solid code both deletions should work as designed.
One note of advice, in order to 'Delete' from the FileSystem, you may need to elevate the permissions of the current user via Impersonation. Many times the current user would not have access to delete files, and therefore impersonating a user for a small
scope to delete the file off of the file system will suffice. Look to the following for more information:
Mar 09, 2010 12:41 PM|hans_v|LINK
SQL Server offers a new feature: FileStream Storage. You can read about it in this white paper:
An answer to the often debated question Which is better ? Storing images in BLOB or in the File System ? can be found also:
a quot from this white paper:
Based on the research cited later in this white paper, BLOBs smaller than 256 kilobytes (KB) (such as widget icons) are better stored inside a database, and BLOBs larger than 1 megabyte (MB) are best stored outside the database. For those sized between
256 KB and 1 MB, the more efficient storage solution depends on the read vs. write ratio of the data, and the rate of “overwrite”.
The images sizes are 2k, 16k, and 50k respectively.
So according to this White paper, you better store the images in the database
Mar 10, 2010 12:01 AM|KraGiE|LINK
You usually don't want to store images in the database. For sites that have a lot of dynamic images, they store the images on separate servers that serve images. This avoids an additional dependency on the database servers, and quickly responds to the
Deleting images can be done, but when you're talking about terabytes of storage, you find that it's more costly to delete the image one at a time than to delete them in a batch processing. The image mapping can be done easily through the application tier.
that url will usually be used to direct traffic to a certain netscalar that will parse out the location of that image.
Mar 10, 2010 12:19 PM|egraffius|LINK
I have been looking around for a while and this seems like the mainstream current way of doing it. For a small scale example in a hosted environment, I guess I could have a seperate server with a webservice to read and write the images and that would be
my image server. Alternately I could just used the file system on the same domain until the site grows out of it. What's your opinion on that?
What do think about using a seperate DB just for images? Assuming the images are small size as in my case around 50K max each.
Mar 10, 2010 12:24 PM|egraffius|LINK
Thanks atconway, these areticles were very helpful.
Mar 10, 2010 03:31 PM|atconway|LINK
For a small scale example in a hosted environment, I guess I could have a separate server with a webservice to read and write the images and that would be my image server.
If images are going to be a primary focus of your site, and specifically managing their locations, etc. I think the idea above is best. Farm those images off onto their own disk or server, and wrap a service around it that can expose all the functionality
you need to work with them. Again without knowing everything about your project it is hard to say exactly, but this presents a more scalable and organized design. This way you can manage adding space easier without disruption of service to the site itself.