Pro's en Cons for storing images in the database

[NilsBevaart]

We have 50 milion pdf images on a fileserver. These images are displayed on a webpage on basis of a unique number. The images are stored in a logical treestructure.

What are the pro's and cons for storing these files in a SQL Server with their ID and image data?
Is the control easier to manage, count, check if all files are available and security is better managed in a database?
Is the performance as quick? How about backup and recovery?

I see different views on this topic in certain examples. Before I start this quest, I apreciate all your expertese.

Nils Bevaart

 

[vongrunt]

To make it simple: with average 50kB .pdf, 50M files would occupy 2.5TB (terabytes). Wanna compete for WinterCorp award or what?

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[NilsBevaart]

The size is as large as it would be on disk. So this does not differ in either situation

Nils Bevaart

 

[r937]

when you have an image on the file server, the web server can use caching

when the image is in the database, it cannot

web server caching is one of the more basic ways to ensure a well performing web site

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[NilsBevaart]

The images are not used as decoration for a site. You search in a database for a unique PDF. This PDF is then displayed. These images will seldom be reused in a timespan for caching to be helpfull.

Nils Bevaart

 

[donutman]

The advantages of storing only file references has been stated many times, so let's state the advantages of storing them in the db. Can anyone think of one meaningful advantage?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

where did i say "decoration" please?

so, how exactly do you propose to search for them?

are you suggesting that the actual content of the PDF needs to be searchable?

ordinary sql might not be up to this task

if, instead, you're just searching things like the PDF name, date added, and so on, then you can store all those in the database and yet leave the PDF on the file system

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[NilsBevaart]

We have a seperate full-text database to search for and identify the pdf. One of the options is to view the original PDF. On this request, the PDF identification is known.
We do not need to search for the PDF, just to store and view it.

Nils Bevaart

 

[r937]

gee, thanks for that extra information

it now sounds like you just need to store and retrieve, not search

let's turn the question back to you, since you have already answered so much yourself

what exactly are you trying to achieve in the way of benefits from storing your fifty million pdfs in a database?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[NilsBevaart]

My idea is that if I store the images in a database, I can use a count function to quickly check if all documents are in place. I can also do a check with the source full-text data on a monthly basis if these match.

If you believe the Microsoft text, this would not hinder performance and scalability. However, reading a few pas threads on this subject, people tend to avoid this option. I have not, however, found why.

The size of this database is great, so I assume this will lead to some issues. Again, this is why I started the tread. To hear your views on pro's and cons.

Nils Bevaart

 

[donutman]

However, reading a few pas threads on this subject, people tend to avoid this option. I have not, however, found why.

One of the primary reasons prior to .NET was that it wasn't easy to get binary (image) data into a table. Another semi-valid reason was the additional complexity of a sensible backup strategy. Other reasons depend upon the application, e.g. in my situation overall performance is improved by maintaining the individual images on every workstation which trades performance and backup for additional maintenance hassles.
Now that it is understood that you have a full text search capability, it is clear that you already have the capacity and willingness to implement the solution you propose. So the question becomes, why not? The two pro reasons you provided are hardly compelling, but again, why not?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[chrissie1]

I keep them sepearate because if i need to change all my jpg to jp2 it will be much easier if they are out of the database and if they want to lake another compression technic that is even better then I could change faster then when they where in the database (I think) but mine are in jpg format (loseless) and I make thumbnails for faster viewing (combined with threading) it's really fast. Even with the 15Mb files we have. But like karl said it's up to you if you can't see the downside then go for it.

Oh BTW I have the pictures on another server aswell but I'm not really sure that helps.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting