storing images in databases, question

[TotallySport]

Hi

I was always told that storing images in databases was a bad thing as it will reduce the quality of the image, however we are currently building an application which needs to maintain the images in one location and use them in different web sites.

There are two ways of doing it from what I understand.

Storing it in the wwwroot(is using asp.net on IIS), and then mapping to the file, and dynamically creating a location that suits the web site, and stroing on the linking up information in a database, the download side to this is if the application grows and we need to build up the servers is it will cause issues in the programming to get it work cross servers (sorry I don't know the correct terminology), the up side is the processing time on the database will be less, no sure about actual load times.

Or storing the images in the database, these images are only web quality so hi res isn't an issue but the site is very picture heavy, this doesn't have the cross server issues as above, but I have never done it and I am worried about strains on the database in processing, database sizes, and other issues which I might not have thought of.

Anyway, if anyone can shed any light on this, in terms of whats best, standard practise, things I may have to consider which are not mentioned, and information would be appriciated.:redface:

This isn't a php asp.net debate, you can do either in both and welcome comments from anyone, the issue is above.

 

[Faevilangel]

What I always have done is store the url to the image, rather than the image. Your DB would be huge if it stored the images.

If you want to create it across multiple servers then build the url as an absolute using the ip of the server.

 

[mobyme]

^ What he says.

 

[TotallySport]

What I always have done is store the url to the image, rather than the image. Your DB would be huge if it stored the images.

If you want to create it across multiple servers then build the url as an absolute using the ip of the server.

I agree the database will be huge, but either way I will have to store them, but how much inpact will it have on the database, if we store them there, that I cannot get my head around.

I cannot use the IP address like that, but I can map them creating dynamic urls so thats not the issue.

 

[Astaroth]

There is no image quality issue of storing images in the db it is simply a size and speed issue.

Just as you are proposing to have a single central db server for all the sites to call on you can likewise have a single central file server storing all the images.

There are very few instances where I would consider storing images in a database

 

[TotallySport]

There is no image quality issue of storing images in the db it is simply a size and speed issue.

Just as you are proposing to have a single central db server for all the sites to call on you can likewise have a single central file server storing all the images.

There are very few instances where I would consider storing images in a database

Thanks for the response, I wanted a simple answer just like that one.

 

[stugster]

All responses are correct so far Avoid storing the images in the database. Use a separate file system and only store the URL to the file.

 

[BrighterTools]

Store images on disk (either within the site, if they are all public) or build some a handler to get and serve the images. (zipping files will also reduce space here)

Storing the images will increase the size of the database, which can cause issues with large data logs and backing up etc.

HTH

 

[TotallySport]

All responses are correct so far Avoid storing the images in the database. Use a separate file system and only store the URL to the file.

thanks for the information but why?

 

[TotallySport]

Store images on disk (either within the site, if they are all public) or build some a handler to get and serve the images. (zipping files will also reduce space here)

Storing the images will increase the size of the database, which can cause issues with large data logs and backing up etc.

HTH

thanks for the response, but i am only storing images that will be used on the site in this way, so data logs and backing up isn't a massive issue, as backups will the an automitic thing and I have no problem backing up the who database.

My issue is with the impact on the DB, how much of an impact will it have?

 

[stugster]

Because when you grab information out a database, you're calling a process, which then pulls the data out a single file. The entire file is "opened" and the data is searched for within. Inevitably, storing images makes this database (the file) massive. Therefore longer to load up and even longer to search.

If you store the images in a file-store, the database is tiny, searches are fast, and you're better off.

 

[stugster]

http://stackoverflow.com/questions/815626/to-do-or-not-to-do-store-images-in-a-database

 

[nr-ts]

Avoid storing large chunks of binary (image) data in the database if avoidable, and just store the link to the fileserver where the image is located.

Think of it this way.... you could fit your car in your lounge each night while you sleep, rather than in the car park, but damn its a pain in the rear to get it in and out the lounge!!!!

 

[davek17]

We store a lot of our images and PDF's in a database as "Blobs" or binary info. The main reason is that we have to be super careful about the security of them and we do not want images lying around that could be copied or stolen.

Its very easy to do and it gives you great flexibility when growing and moving servers and it gives us a nice way to share images out across multiple websites and servers with no risk of breaking things because someone moved something. However I agree that it isn't the best possible method in a lot of cases.

If you;re using Microsoft tech and IIS then make sure you tweak IIS so that it can handle larger uploads. There is definitely a default that limits the upload size so google that.

It doesn't really matter about the size of your DB does it? The images will take up room anyway.

I can't really tell if it has affected efficiency. All our websites are pretty quick.

 

[TotallySport]

Because when you grab information out a database, you're calling a process, which then pulls the data out a single file. The entire file is "opened" and the data is searched for within. Inevitably, storing images makes this database (the file) massive. Therefore longer to load up and even longer to search.

If you store the images in a file-store, the database is tiny, searches are fast, and you're better off.

I have no issue with the the fact the database size is going to be massive while storing images, but i can have a massive database with no images and that would effect performance providing the sql and structure were both setup correctly.

my issue is, if I have a web page, and build an sql statement which pulls the images out of the database to display the page, and I mean all the images there maybe, if you using ebay's homepage, there must be about 40 or so images (maybe more), the sql and tables are build in a way thats the best setup possible, what difference in loading times would the customer see, and how much difference in processing power would it make on the servers?

Scalably the database is a better idea, traditionally the server is a better idea but may cause issues when we have to have multiple servers.

 

[stugster]

Scalably the database is a better idea, traditionally the server is a better idea but may cause issues when we have to have multiple servers.

No, "scalably", the database is a bad idea. In fact, storing the images in the database is a bad idea altogether.

I'm not sure why you think this is good practice.

As for security concerns, if the images are being displayed through any service (HTTP, HTTPS) the fact they originated from a database is irrelevant. In fact, I'd argue that it makes the job of securing the system even more difficult as you have to be very careful you're not going to be susceptible to SQL injections.

There's a reason I posted the link from StackOverflow; it shows the industry standard and best practices and the reasons for them

 

[TotallySport]

Avoid storing large chunks of binary (image) data in the database if avoidable, and just store the link to the fileserver where the image is located.

Think of it this way.... you could fit your car in your lounge each night while you sleep, rather than in the car park, but damn its a pain in the rear to get it in and out the lounge!!!!

but if my car had to be in a different country every week, then storing in the garage or house would be pointless.

 

[stugster]

but if my car had to be in a different country every week, then storing in the garage or house would be pointless.

Sorry, what?

 

[TotallySport]

No, "scalably", the database is a bad idea. In fact, storing the images in the database is a bad idea altogether.

I'm not sure why you think this is good practice.

As for security concerns, if the images are being displayed through any service (HTTP, HTTPS) the fact they originated from a database is irrelevant. In fact, I'd argue that it makes the job of securing the system even more difficult as you have to be very careful you're not going to be susceptible to SQL injections.

There's a reason I posted the link from StackOverflow; it shows the industry standard and best practices and the reasons for them

just to clarify, I am not worried about securty, these are just images used on the web pages to display the page. (i also think its fairly simple to protect against sql injection, theres about four things you need to put in place to make sure it shouldn't happen)

I don't think its best practise, infact I think its bad practise, however I was talking to someone else about it because I was always told that storing images in a database would be bad mainly because you loose image quality, not becuase of database size. They informed me that the sites they mainly build now store the images in a database, but most of his sites are not high in image counts per page so there is no real issue, and I was wondering if there was a better solution to what i wanted, and was my opinion being swade based on an old fashioned opinion that might no longer be relavent since I don't really do any developing and I'm not really in the loop.

And no one seems to be able to answer the processing time issue, which leads me to believe that its a traditional view why images shouldn't be stored in a database more than an efficiency one.

if the processing time and worse the system does as a whole is less or the same there is no deal downside for databases holding the web site images becuase all the other stuff doesn't matter. however if my server is doing to crash due to strains on the database then now that is an issue.

 

[TotallySport]

Sorry, what?

the issue in the being is about to using 1 image file on different web sites, traditional I would have the same image file in multiple folders, one for each image folder within each web site, but thats what i am trying to avoid, so the poster car analagy IMO was rubbish, becuase the car has to be in multiple places no just one.

 

[Highland Park]

IMHO it would be a bad idea. Storing images inside a database seems only to place work on the DBMS and brings no advantage that I can see.

I think it would also disadvantage each and every browser using the site since, without a URL for the image, it wouldn't be able to cache the image locally for future viewing.

 

[TotallySport]

caching the image would have been a reason not to use a database, but you can set it up to cache them;

http://www.4guysfromrolla.com/articles/042209-1.aspx

 

[stugster]

And no one seems to be able to answer the processing time issue, which leads me to believe that its a traditional view why images shouldn't be stored in a database more than an efficiency one.

Nobody has answered it because there are so many variables that giving you a ratio for your question isn't possible.

It doesn't make sense for you to have to call your database on every single page load, for every single user, just to download the logo, when it could be put in the root dir of your public_html and called up as "/logo.jpg" or however you want to call it in your code.

 

[TotallySport]

Nobody has answered it because there are so many variables that giving you a ratio for your question isn't possible.

It doesn't make sense for you to have to call your database on every single page load, for every single user, just to download the logo, when it could be put in the root dir of your public_html and called up as "/logo.jpg" or however you want to call it in your code.

if you page is database driven, you calling the database anyway, database commands are very quick normally anyway so there is no real draw back either.

infact the test would be fairly easy, put two pages together, which are identical in layout and content but draw the images differently and monitor the effects. (no i don't have the time to run the test, but would be interested)

 

[stugster]

I've given you the industry's stance on it. If you choose not to follow it, that's entirely your prerogative. It seems a silly waste of resources to have to look up an extra BLOB every time the page-loads.

 

[Highland Park]

caching the image would have been a reason not to use a database, but you can set it up to cache them;

http://www.4guysfromrolla.com/articles/042209-1.aspx

So you've found a solution to a problem you've created by storing images in the database. I'd be tempted to store only a URL and avoid the problem in the first place.

You've certainly not posted any advantages that would convince me to follow suit but you do seem determined to put them in a database. It certainly won't drop their quality any

 

[TotallySport]

we are developing something and this was an issue, I don't know which solution we will used, i just wanted to know if my idea of image storage in databases was outdated, which i think it is (I also no longer think its the industry standard), I now don't think the situation is as clear cut as I used to think, and i will no long rule out image storage in databases as I would before, but that doesn't mean its right for this project as there are alot of other contributing issues which i haven't mentioned, as they are not related to images on pages.

was very interesting though, thanks for the posts.

 

[Cohesive Computing]

Hi
:::
Anyway, if anyone can shed any light on this, in terms of whats best, standard practise, things I may have to consider which are not mentioned, and information would be appriciated.:redface:
:::

Which DB server are you using?

MS Sql Sever 2008 has a feature whereby blobs can be managed directly on the filesystem, and the client application accesses them via a filestream handle, managed by Sql Server. The advantage of having binary data in the DB is that it remains synchronised with associated metadata during DB backups.

With our own CMS all binary data is stored in the DB for management purposes, and a publishing process writes out the binary data to a filesystem location accessible to a webserver.

Does that help?

 

[TotallySport]

So you've found a solution to a problem you've created by storing images in the database. I'd be tempted to store only a URL and avoid the problem in the first place.

You've certainly not posted any advantages that would convince me to follow suit but you do seem determined to put them in a database. It certainly won't drop their quality any

sorry just to add, if we are mapping the images from one folder to use in multiple locations if the files are store in folders we would still have to develop a solution, so either way we will need additional development if we store them on a server or in a database, the point is which one would be more efficient on the server.

This is not an issue of one image going to be displayed in one location, its one image going to be displayed on completely different web sites, but the url the user sees will be from the web site it is being viewed on and the url will represent that.

 

[TotallySport]

Which DB server are you using?

MS Sql Sever 2008 has a feature whereby blobs can be managed directly on the filesystem, and the client application accesses them via a filestream handle, managed by Sql Server. The advantage of having binary data in the DB is that it remains synchronised with associated metadata during DB backups.

With our own CMS all binary data is stored in the DB for management purposes, and a publishing process writes out the binary data to a filesystem location accessible to a webserver.

Does that help?

thanks for that, and yes it does, we don't really have to worry about managing the images as such, as this will be done by the user through a built control panel, but can you advice on the processing issues?

 

[davek17]

TotallySport

Putting images in a database is good if you want to stop people stealing them off your website, they are also good for portability or replication of servers and also for security. We needed 1 place for images and documents that are at risk of the file structure changing at it helped us massively.

We also generate content that out of personal choice makes it very easy for us to control like this. For instance we need to encrypt and store under a much higher security than in a file store on mobile devices and we also need to sync and replicate which is far easier and more secure to do via Databases than in a file system.

If the DB is on the same server then I do not think you'll see much in the way of a performance hit, we certainly don't. If your DB is oin a seprate server then yes this is a performance hit due to the caching of files being far better but is it good enough, yes!

Its also down to what you're doing anyway, the resource and people we have here and our skills/preference too.

 

[stugster]

but the url the user sees will be from the web site it is being viewed on and the url will represent that.

If you're on linux, just symlink the image from the real dir to the other websites dir.

(Wait: I haven't even tried that myself, I'm not sure that would work, but I don't see why not)

 

[stugster]

Putting images in a database is good if you want to stop people stealing them off your website

Can you elaborate on this point a bit? I don't quite understand how it's good for this reason, but I might be missing something. Cheers

 

[TotallySport]

If you're on linux, just symlink the image from the real dir to the other websites dir.

(Wait: I haven't even tried that myself, I'm not sure that would work, but I don't see why not)

thanks buts it's IIS and SQL2008 I think, might be 2005 but I don't think so

 

[TotallySport]

TotallySport

We also generate content that out of personal choice makes it very easy for us to control like this. For instance we need to encrypt and store under a much higher security than in a file store on mobile devices and we also need to sync and replicate which is far easier and more secure to do via Databases than in a file system.

thanks for that its very interesting, can you explain the above a little more, i am not sure I understand it, or possibly give an example.

 

[MrAnchovy]

OK, I could do with this for some training materials that I want to have available so here goes:

This is what happens when someone loads a page in a web browser that is stored as a file on the web server:

• The page has a number of images in it
• The browser looks in its cache to see if it has a copy of those images
• Each cached image has an expiry date
• If the cached image has not expired the browser displays it - instant
• If the image has expired (or the used has forced a refresh by pressing F5 or otherwise), the browser asks the web server for the image. The last time the web server sent the image, it told the browser the modification date of the image so the browser sends that to the web server
• The web server looks at the Last Modified date provided by the browser, and looks at the modification date of the image file in the file system. If this is a commonly used image, the file header is probably cached so no disk access actually occurs.
• If the dates match, the web server sends the browser a '304 Not Modified' response and the browser displays the cached copy of the image - almost instant (basically the length of the ping time of the web server from the browser).
• If the dates don't match, the web server retrieves the image file from the file system (again it may be cached so not requiring any disk access), adds HTTP protocol headers etc. and sends it to the browser which displays it - quick, depends on the size of the file

This is what happens when someone loads a page in a web browser that is stored in a database:

• The page has a number of images in it
• The browser looks in its cache to see if it has a copy of those images
• By default, scripting languages (PHP, Java, ASP etc.) don't send expiry dates (the whole idea of a script is to send a dynamic response that changes on every page access) so you need to write code to do this
• If you have written your code to send the right headers and the cached image has not expired the browser displays it - instant
• Otherwise, or if the image has expired (or the used has forced a refresh by pressing F5 or otherwise), the browser asks the web server for the image. Again, if you wrote your script correctly, the last time the web server sent the image, it told the browser the modification date of the image so the browser sends that to the web server, otherwise by default this will not help.
• The web sever recognises that the image request is served by a script so it loads the script module or process (depends on the OS, server and script engine being used)
• The script initialises itself and works out a database query that matches the request from the browser
• The script makes a connection to the database (a process running on the same or some other server) and sends it the request
• The database looks for the record in its index stored in the file system (which may or may not be cached) and tells the script 'yes I have that record'
• If you have written code to send the right headers and store the right metadata in the database, the script can work out whether the image that was last sent is still valid and if it is tell the web server to respond with '304 Not Modified' - should be little more than the ping time until the load on the web server (that is now having to run the script engine and communicate with the database server) and/or the database server goes up when your response times start to grow - particularly if you are on a shared server where lots of people are hammering the database process
• The script asks the database to send it the record
• The database loads the data from the file system, wraps it with some SQL headers etc. and sends it to the script
• The script works out what HTTP headers to send so the browser caching bit right at the beginning works next time, and tells the web server to respond with these headers and the data retrieved from the database
• The web server adds a bit more HTTP protocol and sends the data to the web browser
• When everything is running fine, and if you have written your code correctly so that browsers can cache effectively, the effect in terms of response time may not be large. But the additional load on the server means that you will hit a response bottleneck much quicker (at a guess, at about 1/10th of the request traffic) than letting the web server do what it was designed for

Don't do this unless there is a very good reason to.

 

[MrAnchovy]

Putting images in a database is good if you want to stop people stealing them off your website

Can you elaborate on this point a bit? I don't quite understand how it's good for this reason, but I might be missing something. Cheers

Your script can look at the HTTP_REFERER (sic) header to make sure your image is not being linked to from another page. But if you are using Apache you can let the web server do this in mod_rewrite.

 

[stugster]

My point is though, if you're seeing the images on the website, you just save the image?

 

[Cohesive Computing]

::

... but can you advice on the processing issues?

If you're using MS technologies (asp.net / SQL Server/ IIS), you could write you're own http handler to serve images from the DB (or grab the source from the internet), and use the output cache of IIS to keep a hold of the images.

In this scenario the processing overheads involved retrieving a few smallish (64kb) images (per request) from the DB shouldn't be of much concern.

 

[Highland Park]

My point is though, if you're seeing the images on the website, you just save the image?

That's exactly what I was thinking - they choose SaveAs and use it at will. I guess then, at least, they'd be using their own bandwidth if they used it on their own website.