ankurdk
asked on
Ideal way of storing large number of .jpg images
Hi,
We have a large number of .jpg images (about half a million! and growing) each of size upto 200 KB. We have a Windows based application WITH SQL SERVER 2008 as BACKEND, which accesses these images not all at once but on demand (for e.g: on click of a button) NOW we have two options to access these images :
1) We store images in SQL Database in bit form.
2) We store images physically on Windows Drive on Server and access their path.
Which of these or otherwise for that matter would be the best way to store and access these images not compromising on SPEED of accessing since they all are located in the SAME location! Any suggestion for an ideal way of storing images are also welcome.
Thanks.
We have a large number of .jpg images (about half a million! and growing) each of size upto 200 KB. We have a Windows based application WITH SQL SERVER 2008 as BACKEND, which accesses these images not all at once but on demand (for e.g: on click of a button) NOW we have two options to access these images :
1) We store images in SQL Database in bit form.
2) We store images physically on Windows Drive on Server and access their path.
Which of these or otherwise for that matter would be the best way to store and access these images not compromising on SPEED of accessing since they all are located in the SAME location! Any suggestion for an ideal way of storing images are also welcome.
Thanks.
John Tsioumpris
I think that both is the ideal..you store the images on a Database as blob and also you store a "backup" file path of the images...so either way you have always availability
@John,
He asked which way would be faster and I see no reason for storing them in both the database and on disk. That's what backups are for.
@ankurdk
I believe that storing them in the database would come out ahead, but I don't know that for sure.
Reason I say that is because you are already making a call to the DB to fetch the record and SQL Server has already gone out to disk to read it. I'm assuming though that you'd be grabbing the picture along with getting other data in the record and not just going after the picture on it's own. If you are already going after other data in the record, then it's just a matter of transferring a little more data. There's no real additional overhead in the processing server side.
If the picture was on disk though, you'd be making a call to SQL, getting the record, transferring that, then coming back to make another call to the OS for the file, which has to locate it on disk and then read it in. I think the time to do that would out weigh the additional time required for SQL just to transfer the picture data.
But as I said, I don't know for sure. Lacking someone else jumping in that has tested something like this already, I think you'll just have to test the setup and find out which is actually better.
Certainly though handling the photos from a maintenance standpoint is far easier if they are on disk and that's why most developers go that route with applications. But if speed is the major factor, then keeping it all in SQL I would think would be the better choice.
Jim.
He asked which way would be faster and I see no reason for storing them in both the database and on disk. That's what backups are for.
@ankurdk
I believe that storing them in the database would come out ahead, but I don't know that for sure.
Reason I say that is because you are already making a call to the DB to fetch the record and SQL Server has already gone out to disk to read it. I'm assuming though that you'd be grabbing the picture along with getting other data in the record and not just going after the picture on it's own. If you are already going after other data in the record, then it's just a matter of transferring a little more data. There's no real additional overhead in the processing server side.
If the picture was on disk though, you'd be making a call to SQL, getting the record, transferring that, then coming back to make another call to the OS for the file, which has to locate it on disk and then read it in. I think the time to do that would out weigh the additional time required for SQL just to transfer the picture data.
But as I said, I don't know for sure. Lacking someone else jumping in that has tested something like this already, I think you'll just have to test the setup and find out which is actually better.
Certainly though handling the photos from a maintenance standpoint is far easier if they are on disk and that's why most developers go that route with applications. But if speed is the major factor, then keeping it all in SQL I would think would be the better choice.
Jim.
Speed either would be just FAST...for only 200Kb pictures...
There have being some tests that Firebird SQL server has being faster than filesystem for similar cases so MsSQL should be on par.
There have being some tests that Firebird SQL server has being faster than filesystem for similar cases so MsSQL should be on par.
Do you know the Filestream feature? https://www.emoreau.com/Entries/Articles/2009/09/Microsoft-SQL-Server-2008--The-FileStream-feature.aspx
Nature of files's use?
Here is the point, blob, or any means it is effectively dead space consumed I the database as well as in memory of SQL server.
Much depends on the application that accesses the data and its capability.
In such a large number of image files:
One deals with managing the storage structure I.e. You can not have all the files, within a single directory structure.
The second deals how the application in use works.
I.e. If the application can only get data from the database, you are limited to having the images in the DB.
If the application can access network shares, then storing the image data within the file system while the database includes the reference.
In this case you would use a split reference
1) defines the server\sharename where the root of the fileshare structure is
2) the relative path of the file
What this does is provide the flexibility to transition between file servers without the need to mass correct file references.
Often files should be stord within the filesystem.
The exclusion, exemption are use of a few files in a DB I.e. Company logo, trademark, etc.
Here is the point, blob, or any means it is effectively dead space consumed I the database as well as in memory of SQL server.
Much depends on the application that accesses the data and its capability.
In such a large number of image files:
One deals with managing the storage structure I.e. You can not have all the files, within a single directory structure.
The second deals how the application in use works.
I.e. If the application can only get data from the database, you are limited to having the images in the DB.
If the application can access network shares, then storing the image data within the file system while the database includes the reference.
In this case you would use a split reference
1) defines the server\sharename where the root of the fileshare structure is
2) the relative path of the file
What this does is provide the flexibility to transition between file servers without the need to mass correct file references.
Often files should be stord within the filesystem.
The exclusion, exemption are use of a few files in a DB I.e. Company logo, trademark, etc.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Dear All,
Many thanks for your valuable suggestions. Here, I would like to add up another query :
If we choose the method of File System i.e storing the files on the disk itself instead of the DB, what is the ideal number of files that should be stored in
one directory with each file having the size as mentioned in the question?
As @David Johnson mentioned that about 200 files in a directory would be faster, so can we take this as an ideal number of files to be stored in one directory or there can be more ?
Many thanks for your valuable suggestions. Here, I would like to add up another query :
If we choose the method of File System i.e storing the files on the disk itself instead of the DB, what is the ideal number of files that should be stored in
one directory with each file having the size as mentioned in the question?
As @David Johnson mentioned that about 200 files in a directory would be faster, so can we take this as an ideal number of files to be stored in one directory or there can be more ?
Since you will have referential entry in the DB, that points directly at the file.
The complexity of structured might not be needed.
I.e. Using the structure as based on the two first characters or through random distribution at the time of an image upload.
Nature of files..
The complexity of structured might not be needed.
I.e. Using the structure as based on the two first characters or through random distribution at the time of an image upload.
Nature of files..
I recently learned some lessons the hard way about using images with SQL Server.
I had a client who already had his images in an Access database that he wanted to upsize to SQL Server.
They were all .bmp files that he converted from phone pictures because he was told that was the only format he could use.
He inserted the images into an OLEObject table field using an OLEBoundObjectFrame control.
To extract the images to a file, he had to extract using the OLEBound Field or control's built-in "open-in-server" application (paint) so he could save as an image file.
Image files inserted this way are stored as a "package", not a "blob", so import/export of images using blob and filestream methods resulted in files that would not open in picture viewers - worthless.
Also, "packages" only display in OLEBoundObjectFrame controls.
Blobs display in "Image" controls, and not OLEBoundObjectFrame controls.
We had to buy a 3rd party tool to extract the hundreds of embedded images to image files so we could re-upload them as blobs using filestream method.
Once we got the images reuploaded as blobs, our code and the images work as designed - flawlessly! He could even use .png files as well!
Once you learn how to successfully handle image files in SQL Server and display them on screen, and you have the space in SQL Server, it works great.
I had a client who already had his images in an Access database that he wanted to upsize to SQL Server.
They were all .bmp files that he converted from phone pictures because he was told that was the only format he could use.
He inserted the images into an OLEObject table field using an OLEBoundObjectFrame control.
To extract the images to a file, he had to extract using the OLEBound Field or control's built-in "open-in-server" application (paint) so he could save as an image file.
Image files inserted this way are stored as a "package", not a "blob", so import/export of images using blob and filestream methods resulted in files that would not open in picture viewers - worthless.
Also, "packages" only display in OLEBoundObjectFrame controls.
Blobs display in "Image" controls, and not OLEBoundObjectFrame controls.
We had to buy a 3rd party tool to extract the hundreds of embedded images to image files so we could re-upload them as blobs using filestream method.
Once we got the images reuploaded as blobs, our code and the images work as designed - flawlessly! He could even use .png files as well!
Once you learn how to successfully handle image files in SQL Server and display them on screen, and you have the space in SQL Server, it works great.
This is exactly the case my Article covers : storing Images in low sized .jpgs that are converted to .bmp on the fly :https://www.experts-exchange.com/articles/33716/Defeating-the-device-independent-bitmap-dib-format.html