NEED TO KNOW: Is it better to save 100 thousand+ images in OS or in BLOB field

From a performance perspective, I would like to know what is more efficient. I will be
storing hundreds of thousands images in my database. These images range in size from
2k to 10k. Each image will be updated as often as once per week.

I know that there have been a lot of questions on this subject, but the quantity of
images I am dealing with is greater than what I have seen discussed on EE so far.

It seems that the general answer is to always store only the image path in the DB
and store the actual image in Windows. My question is whether Windows file system
can actually handle that many images in a directory without slowing down or dying.

BTW, I am using ASP 3.x on Windows Server 2003.

-- ScribbleMeat

ScribbleMeatAsked:
Who is Participating?
 
pcsentinelCommented:
I have done exactly that. We currently hold 750,000 images in our system.

The images are stored external to the database in a series of folders each folder containing approx 1000 images. The path to the image together with the image thumbnail and other info is stored in the database.

regards

0
 
adatheladCommented:
My opinion is like you already said - to store all images in the OS and not in SQL Server.

I don't know what limitations if any, there are with the Windows file system as it's not my speciality but I think that your question should be the opposite i.e. whether SQL Server could actually handle that many images without slowing down or dying

This is purely my thoughts as I have no real experience in large amounts of images like this, but I would have thought that Windows is far better equipped to cope with this number of images as it *is* a file sytem, whereas SQL Server was not designed to be a file system so doesn't behave well/efficiently as one.

Listening...
0
 
DreamMasterCommented:
As far as I can tell...you have done the best thing allready...

SQL Server is not very good when you use BLOB fields, there are other Databases that handle them much better. Also  dividing the amount of images into multiple folders is a good idea as requesting the folder listing can take ages or even freeze the OS with 750.000 images in them.

Regards,
Max.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ScribbleMeatAuthor Commented:
pcsentinal,

What about disk fragmentation? And what did you set your NTFS cluster size at? Should I put my images on their own partition and set the cluster size at or around what I expect the largest image to be (10k)?

-- ScribbleMeat
0
 
pcsentinelCommented:
We did some experimentation on this and found that in terms of speed over the network of obtaining an image, the cluster size really didn't make that much difference, but in terms of overall disk space used it did, however since our image sizes range from 2k to 63k there was no ideal so we just used the defaults.

Should you put the images on their own partition? Ideally yes, but of course this depends on your setup. The ideal which is what we employ is to store the images on their own machine.

Disk Defrag is done every other night during off peak times.

regards
0
 
ScribbleMeatAuthor Commented:
Thanks guys. I really appreciate it.

-- ScribbleMeat
0
 
DreamMasterCommented:
You're welcome ScribbleMeat..glad to have been helpful

Regards,
Max.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.