January 22, 2008 at 1:09 am
Hi Experts,
We are going to create Photo Printing & Social Networking site like Shutterfly.com,picsqare.com,kodakgallery.com,facebook.com.
Everything is running fine. But Now I am facing some problems.
When user Upload several(no limit) his/her Photo(s), I save those photo in Sql Server Database in Image datatype. & we are saving three sizes of every image. (one is original, second is thumbnail size & third one is larger then thumbnail and smaller then original)
I have some reason to save Images in sql Server.
1. In database it will take a little bit storage space.
2. We can take backup easilly of whole data.
but the main issue in this process is that we have to create images in everypage, everytime with the help of HttpHandler from byte array to image(.net datatype). & this will be a costly process when traffic will increase.
So now i am thinking to save uploaded images in our web server & image path in sql server database.
But in this process,there is an issue related to Image size. Because If save image physically then it will take its original size(may be in MB). Means if there are 1000 users & each user upload 20 MB data then it will take more then 1000x20 MB data.(because we have to save three sizes).
Facebook saves 5 sizes of every image. But i dont know that how they are saving images(in database or in web server).
Pls guide me experts !
Regards,
Anuj Rathi
January 22, 2008 at 2:47 am
It is better to save in sql server with location path. While getting in the page it will be easy to display with location path.
Generally images takes more diskspace you needed to go for higher capacity disk and you build logic in web page to storing the image in a compressed format and while displaying by un-compress.
January 22, 2008 at 5:52 am
You've already laid out the issues pretty well. You can either save it in the database where:
[highlight]You get a good, clean, synchronized backup
When the user is deleted, you also easily clean up their pictures
You have a single source to pull from and store
You suffer from having to stream the data into & out of the database, an expensive and probably slow I/O operation[/highlight]
Or, you can save it to a file where:
[highlight]You have smaller, more manageable databases
A nice simple retreival and storage mechanism that'll probably be more than a bit faster than streaming it in & out of a database
Your backups, and more importantly, restores, have to be synchronized in a way that could be very difficult to maintain [/highlight]
Now you just have to decide, for your application, do you need the clean backups & protection you get from the database, or is the speed of information retrieval from a file system worth the sacrifice on the backups?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 22, 2008 at 9:26 am
I don't think you have any space savings.
On the multiple sizes, there are places that store 1 or maybe 2 and then resize on the fly. Web servers are cheap and this might be the best solution there. easy to offload that stuff.
If you store in SQL Server, you can move all images to their own filegroup, which can make backup/restore easier. Depends on how you implement it. could just run separate filegroup backups, and then if some images are lost, not worry about it.
I like storing in the filesystem, which is how SQL server 2008 will work with their Filestream objects. They'll handle the synch. In 2005, you need to periodically, like daily, run queries to be sure that all the files in the file system are in the db, and vice versa and catch mistakes ASAP.
January 23, 2008 at 2:54 am
Hi,
Basically the issue is as to what will be the objective of the system i.e whether we are giving importance to the fast I/O operations or need a safe data storage mechanism for user specific data .If we store the images on a dedicated high capacity web server and also have a standby server(so that in case of any disaster we can recover the operation happened in the meanwhile) .
This can be a useful step in case when the client or user is very specific and purposeful about the data and later to add more this backup can be utilized as a added new feature in case of data loss of client during some natural loss of hard copy of images the backup mechanism can be useful from the perspective of site owner.:)
January 23, 2008 at 7:55 am
One of the respondents has already touched on the solution. Store only the original and resize the image on the fly using GDI+ code in the web application. This way if there is any real space savings by using SQL Server for image storage over using the filesystem you will be able to use that. However, I do have to agree that the space savings probably isn't going to be that much in comparison to saving the files to the filesystem. The biggest reason why you would see any savings at all is because of default allocation sizes enforced by Windows and determined by how you originally formatted the hard drive(s).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply