Suggestions for storing images in SQL Server vs storing references to physical paths

Suggestions for storing images in SQL Server vs storing references to physical paths

Post by Tek Bo » Mon, 12 Nov 2001 05:23:11


I'm looking at developing an application which, among other things, will
store information on images associated with individual user's profiles --
such as glamour shots in a photographer's portfolio.  At this time, I'm not
concerned about actual implementation -- it's more of design issue, with
respect to scalability, performance and ease-of-use.

In the end, I'm going to be pulling some sort of data from a database --
binary or text -- and will be displaying it via ASP on a web page.  My two
options are: store the images physically in the web server as binary data,
and thus physically retrieve the file when required to do so; or store a
path (relative or absolute) in the database, keep the image files in a
directory somewhere on the web server, and then display an IMG tag on the
ASP page, which merely points to where the image is.

Does anybody have suggestions as to which architecture might be better, and
why?  I've faced this problem before, although it was never an issue, since
the sites didn't anticipate that much traffic and didn't have that much data
to store.  I've been using the latter option (store images on server; store
path references in DB) up until now, but because this site has the potential
to grow quickly (each model can have 0-6 images; administrators can create
an unlimited number of models; and if they sell this as an ASP-type service,
there will be 0-500 sites with at least one administrator per site).  I'll
be using SQL Server 7.0 or 2000, and ASP 2.0 / 3.0.......... I'm
disregarding hardware for now, and focusing primarily on software design as
the biggest potential source of problems.

Any ideas, or links to discussions concerning this type of issue, would be
greatly appreciated.  Thanks in advance!

Sincerely,

-=Tek Boy=-

 
 
 

Suggestions for storing images in SQL Server vs storing references to physical paths

Post by Cowb » Mon, 12 Nov 2001 07:51:23


On Sat, 10 Nov 2001 15:23:11 -0500, "Tek Boy"


>I'm looking at developing an application which, among other things, will
>store information on images associated with individual user's profiles --
>such as glamour shots in a photographer's portfolio.  At this time, I'm not
>concerned about actual implementation -- it's more of design issue, with
>respect to scalability, performance and ease-of-use.

>In the end, I'm going to be pulling some sort of data from a database --
>binary or text -- and will be displaying it via ASP on a web page.  My two
>options are: store the images physically in the web server as binary data,
>and thus physically retrieve the file when required to do so; or store a
>path (relative or absolute) in the database, keep the image files in a
>directory somewhere on the web server, and then display an IMG tag on the
>ASP page, which merely points to where the image is.

>Does anybody have suggestions as to which architecture might be better, and
>why?  I've faced this problem before, although it was never an issue, since
>the sites didn't anticipate that much traffic and didn't have that much data
>to store.  I've been using the latter option (store images on server; store
>path references in DB) up until now, but because this site has the potential
>to grow quickly (each model can have 0-6 images; administrators can create
>an unlimited number of models; and if they sell this as an ASP-type service,
>there will be 0-500 sites with at least one administrator per site).  I'll
>be using SQL Server 7.0 or 2000, and ASP 2.0 / 3.0.......... I'm
>disregarding hardware for now, and focusing primarily on software design as
>the biggest potential source of problems.

>Any ideas, or links to discussions concerning this type of issue, would be
>greatly appreciated.  Thanks in advance!

>Sincerely,

>-=Tek Boy=-

In general, I do not like storing blobs in a database, unless there is
some type of security issue where the database is known to be more
secure. While SQL Server can certainly handle the blob fields, it is
not as efficient as the other option (writing out an <IMG tag with a
path and file name).

Security - toss up, but SQL Server would most likely win, but just
barely.

Maintainability - File system probably wins, unless you cannot trust
those that clean up the files when no longer needed, then SQL Server
might be a better option. File system is more common and therefore
more understandable. I would therefore say file system.

Availability - File system has one point of failure beyond IIS; SQL
Server has two (ADO and SQL Server). If the File system is out, the
whole app is out. File system wins here.

Performance - I would bet on the file system, although very small
images may make a nod towards SQL Server if coded right.

The point I am making here is you have to look at your options and
decide which pieces are most important.

I have worked on a project similar to what you are pushing. We used
XSLT for the look of the sites, and XML to get the individual data.
The clients could upload their own images, so we uploaded the images
and added the path to them. Consistency was the real key here. To
create the sites, we used the IIS objects to create new webs. The
users used an HTML/ASP app to manage their sites. If you want to see
how they look, start here: http://www.ehc.com. The client is different
(hospitals), but the methodology is the same.

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
Author: ADO.NET and XML: ASP.NET on the Edge (4Q 2001)
******************************************************
Think outside the box!
******************************************************

 
 
 

Suggestions for storing images in SQL Server vs storing references to physical paths

Post by PA » Tue, 13 Nov 2001 20:50:52


Hello,

They recommend to store images within your filesystem and put paths into a
database:

http://www.aspfaq.com/plain.asp?id=2149

From the other hand, there's a MS KB article which explains HOW this (storing
images into a db) can be done:

Q173308 "HOWTO: Displaying Images Stored in a BLOB Field"

http://support.microsoft.com/support/kb/articles/Q173/3/08.ASP

Regards,
--
Intermedia.NET (PAS)
Microsoft Certified Solutions Provider
Providing expertise in hosting applications
MS Exchange Hosting: http://www.intermedia.net/exchangehosting
Windows 2000 Web Hosting:  http://www.intermedia.net/webhosting
For a waiver of the set up fee use "IMFREE" code


> I'm looking at developing an application which, among other things, will
> store information on images associated with individual user's profiles --
> such as glamour shots in a photographer's portfolio.  At this time, I'm not
> concerned about actual implementation -- it's more of design issue, with
> respect to scalability, performance and ease-of-use.

> In the end, I'm going to be pulling some sort of data from a database --
> binary or text -- and will be displaying it via ASP on a web page.  My two
> options are: store the images physically in the web server as binary data,
> and thus physically retrieve the file when required to do so; or store a
> path (relative or absolute) in the database, keep the image files in a
> directory somewhere on the web server, and then display an IMG tag on the
> ASP page, which merely points to where the image is.

> Does anybody have suggestions as to which architecture might be better, and
> why?  I've faced this problem before, although it was never an issue, since
> the sites didn't anticipate that much traffic and didn't have that much data
> to store.  I've been using the latter option (store images on server; store
> path references in DB) up until now, but because this site has the potential
> to grow quickly (each model can have 0-6 images; administrators can create
> an unlimited number of models; and if they sell this as an ASP-type service,
> there will be 0-500 sites with at least one administrator per site).  I'll
> be using SQL Server 7.0 or 2000, and ASP 2.0 / 3.0.......... I'm
> disregarding hardware for now, and focusing primarily on software design as
> the biggest potential source of problems.

> Any ideas, or links to discussions concerning this type of issue, would be
> greatly appreciated.  Thanks in advance!

> Sincerely,

> -=Tek Boy=-

 
 
 

Suggestions for storing images in SQL Server vs storing references to physical paths

Post by Steven Parke » Tue, 20 Nov 2001 17:28:53


Cowboy,

Check www.sqlteam.com as this is a SQL issue more than ASP.

My recollection is they recommended storing the link in the DB, not the
image.  But check anyway.

Steve


> On Sat, 10 Nov 2001 15:23:11 -0500, "Tek Boy"

> >I'm looking at developing an application which, among other things, will
> >store information on images associated with individual user's profiles --
> >such as glamour shots in a photographer's portfolio.  At this time, I'm
not
> >concerned about actual implementation -- it's more of design issue, with
> >respect to scalability, performance and ease-of-use.

> >In the end, I'm going to be pulling some sort of data from a database --
> >binary or text -- and will be displaying it via ASP on a web page.  My
two
> >options are: store the images physically in the web server as binary
data,
> >and thus physically retrieve the file when required to do so; or store a
> >path (relative or absolute) in the database, keep the image files in a
> >directory somewhere on the web server, and then display an IMG tag on the
> >ASP page, which merely points to where the image is.

> >Does anybody have suggestions as to which architecture might be better,
and
> >why?  I've faced this problem before, although it was never an issue,
since
> >the sites didn't anticipate that much traffic and didn't have that much
data
> >to store.  I've been using the latter option (store images on server;
store
> >path references in DB) up until now, but because this site has the
potential
> >to grow quickly (each model can have 0-6 images; administrators can
create
> >an unlimited number of models; and if they sell this as an ASP-type
service,
> >there will be 0-500 sites with at least one administrator per site).
I'll
> >be using SQL Server 7.0 or 2000, and ASP 2.0 / 3.0.......... I'm
> >disregarding hardware for now, and focusing primarily on software design
as
> >the biggest potential source of problems.

> >Any ideas, or links to discussions concerning this type of issue, would
be
> >greatly appreciated.  Thanks in advance!

> >Sincerely,

> >-=Tek Boy=-

> In general, I do not like storing blobs in a database, unless there is
> some type of security issue where the database is known to be more
> secure. While SQL Server can certainly handle the blob fields, it is
> not as efficient as the other option (writing out an <IMG tag with a
> path and file name).

> Security - toss up, but SQL Server would most likely win, but just
> barely.

> Maintainability - File system probably wins, unless you cannot trust
> those that clean up the files when no longer needed, then SQL Server
> might be a better option. File system is more common and therefore
> more understandable. I would therefore say file system.

> Availability - File system has one point of failure beyond IIS; SQL
> Server has two (ADO and SQL Server). If the File system is out, the
> whole app is out. File system wins here.

> Performance - I would bet on the file system, although very small
> images may make a nod towards SQL Server if coded right.

> The point I am making here is you have to look at your options and
> decide which pieces are most important.

> I have worked on a project similar to what you are pushing. We used
> XSLT for the look of the sites, and XML to get the individual data.
> The clients could upload their own images, so we uploaded the images
> and added the path to them. Consistency was the real key here. To
> create the sites, we used the IIS objects to create new webs. The
> users used an HTML/ASP app to manage their sites. If you want to see
> how they look, start here: http://www.ehc.com. The client is different
> (hospitals), but the methodology is the same.

> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> Author: ADO.NET and XML: ASP.NET on the Edge (4Q 2001)
> ******************************************************
> Think outside the box!
> ******************************************************

 
 
 

Suggestions for storing images in SQL Server vs storing references to physical paths

Post by Finn Newic » Wed, 21 Nov 2001 01:51:28


Store the link, that way when your database is queried it only has to return
a few characters (=a few bytes)  rather than thousands (even the smallest
JPEG will typically be a couple of K). Also it makes it much easier to use
external programs on the stored images (eg batch processing in Photoshop
etc).

The only advantage that I can think of of storing the images directly in a
database is to keep everything together, but that shouldn't really be an
issue providing you make the interface to the images/database reliable.

Have fun
Finn


> Cowboy,

> Check www.sqlteam.com as this is a SQL issue more than ASP.

> My recollection is they recommended storing the link in the DB, not the
> image.  But check anyway.

> Steve



> > On Sat, 10 Nov 2001 15:23:11 -0500, "Tek Boy"

> > >I'm looking at developing an application which, among other things,
will
> > >store information on images associated with individual user's
profiles --
> > >such as glamour shots in a photographer's portfolio.  At this time, I'm
> not
> > >concerned about actual implementation -- it's more of design issue,
with
> > >respect to scalability, performance and ease-of-use.

> > >In the end, I'm going to be pulling some sort of data from a
database --
> > >binary or text -- and will be displaying it via ASP on a web page.  My
> two
> > >options are: store the images physically in the web server as binary
> data,
> > >and thus physically retrieve the file when required to do so; or store
a
> > >path (relative or absolute) in the database, keep the image files in a
> > >directory somewhere on the web server, and then display an IMG tag on
the
> > >ASP page, which merely points to where the image is.

> > >Does anybody have suggestions as to which architecture might be better,
> and
> > >why?  I've faced this problem before, although it was never an issue,
> since
> > >the sites didn't anticipate that much traffic and didn't have that much
> data
> > >to store.  I've been using the latter option (store images on server;
> store
> > >path references in DB) up until now, but because this site has the
> potential
> > >to grow quickly (each model can have 0-6 images; administrators can
> create
> > >an unlimited number of models; and if they sell this as an ASP-type
> service,
> > >there will be 0-500 sites with at least one administrator per site).
> I'll
> > >be using SQL Server 7.0 or 2000, and ASP 2.0 / 3.0.......... I'm
> > >disregarding hardware for now, and focusing primarily on software
design
> as
> > >the biggest potential source of problems.

> > >Any ideas, or links to discussions concerning this type of issue, would
> be
> > >greatly appreciated.  Thanks in advance!

> > >Sincerely,

> > >-=Tek Boy=-

> > In general, I do not like storing blobs in a database, unless there is
> > some type of security issue where the database is known to be more
> > secure. While SQL Server can certainly handle the blob fields, it is
> > not as efficient as the other option (writing out an <IMG tag with a
> > path and file name).

> > Security - toss up, but SQL Server would most likely win, but just
> > barely.

> > Maintainability - File system probably wins, unless you cannot trust
> > those that clean up the files when no longer needed, then SQL Server
> > might be a better option. File system is more common and therefore
> > more understandable. I would therefore say file system.

> > Availability - File system has one point of failure beyond IIS; SQL
> > Server has two (ADO and SQL Server). If the File system is out, the
> > whole app is out. File system wins here.

> > Performance - I would bet on the file system, although very small
> > images may make a nod towards SQL Server if coded right.

> > The point I am making here is you have to look at your options and
> > decide which pieces are most important.

> > I have worked on a project similar to what you are pushing. We used
> > XSLT for the look of the sites, and XML to get the individual data.
> > The clients could upload their own images, so we uploaded the images
> > and added the path to them. Consistency was the real key here. To
> > create the sites, we used the IIS objects to create new webs. The
> > users used an HTML/ASP app to manage their sites. If you want to see
> > how they look, start here: http://www.ehc.com. The client is different
> > (hospitals), but the methodology is the same.

> > Gregory A. Beamer
> > MVP; MCP: +I, SE, SD, DBA
> > Author: ADO.NET and XML: ASP.NET on the Edge (4Q 2001)
> > ******************************************************
> > Think outside the box!
> > ******************************************************

 
 
 

Suggestions for storing images in SQL Server vs storing references to physical paths

Post by Norm Powro » Wed, 28 Nov 2001 07:18:40


I realize that I am coming to the party a bit late, but I wouldn't
mind soliciting a few opinions on this particular topic, as I have a
similar problem.

In my case, I have a (relatively) large number of bitmaps that are
currently stored outside the database. The database contains
filenames, and the ASP code in the web application constructs the
final filename (i.e. the physical location on the Web server).
Presentation on the client machine is handled via a custom ActiveX
control that asynchronously downloads the bitmap and displays it.

The database, and the collection of bitmaps, are maintained by a
"builder" program that provides complete control over what data is
available, how the textual data links to graphics, etc.

The problem -- maintaining the graphics files outside the database is
a PITA, since every maintainer needs to have write access to the web
location, and needs to be able to map a path to the directory. As
such, each maintainer needs to know where things are, and how they are
organized. As well, since maintainers can deal with multiple
databases, each database/web combination is different, so they need
access to multiple locations around the network. Deployment to other
locations is also a pain, as we have to deal with gathering and
distributing entire collections. Arguments about "cost of SQL space
versus cost of Web space" are a non-issue, as the customer owns all
the machines involved, so financial issues are non-existent.

Because of these issues, I have been thinking about moving the
graphics into the database for maintenance and development purposes.
My idea is to house the graphics in the DB until the web application
is deployed to a specific server. During deployment, an administrative
function will "export" the bitmaps, effectively recreating the
individual files. Web clients will be served from these individual
files rather than from the internal database data. The database will
serve up only textual data during use, but the BLOBs will still be in
the DB.

That's the plan, however the comments from you folks about performance
concern me. The web application is designed to give maximum
performance, and it does this quite well. What kind of performance
issues am I likely to run into if my database suddenly grows by the
addition of several hundred BLOBs, even if I am not actually getting
data from the BLOB table?

I invite any and all discussion on this idea. I haven't implemented
the migration of the graphics to the DB as yet; I'm still in design
mode so I have maximum flexibility at this point.

Cheers
Norm

On Mon, 19 Nov 2001 16:51:28 -0000, "Finn Newick"


>Store the link, that way when your database is queried it only has to return
>a few characters (=a few bytes)  rather than thousands (even the smallest
>JPEG will typically be a couple of K). Also it makes it much easier to use
>external programs on the stored images (eg batch processing in Photoshop
>etc).

>The only advantage that I can think of of storing the images directly in a
>database is to keep everything together, but that shouldn't really be an
>issue providing you make the interface to the images/database reliable.

>Have fun
>Finn

[snip]