It's probably pretty clear to most people what is going on when just a
reference to the image (/directory/name.img) is stored in the database so
I'll just discuss what additional overhead is incurred when the entire image
is stored in the database.
When you store the actual image in the database, the request from the client
for the image that would normally get handled relatively speedily and
efficiently by the Web server and file system (after all that is what they
are designed for) is actually passed off to ColdFusion. This ties up one
additional thread on the ColdFusion server for each image being processed.
You only get so many threads: usually 5 times the number of processors. If
you had a single CPU server, you could tie up all the available threads with
one page that had five images on it.
ColdFusion then makes a query to the database which is usually on a
different server. This takes network resources, from the NICs to the
bandwidth utilized to transfer the query and return the image data. If the
database is not on a separate server (which is not generally a good idea
because, among other things, you are tying up CPU cycles on your Web server)
then you still incur the overhead of processing the request, finding the
data in the database and passing this back the ColdFusion application server
all via ODBC, OLE etc.
On the database's end of things, RDBMSs usually store BLOB (Binary Large
Object) data relatively poorly since most RDBMSs store information in 2k
extents and 8k pages. What in effect happens is the RDBMS stores the image
elsewhere in the database and places a pointer where you would logically
expect the image data. This is also true for the TEXT datatype. When the
RDBMS goes to return the data, it has to "hunt" the image in a separate
spot.
In any case, you've added quite a bit more overhead to the Web server, the
database server, and the network. You've also increase the amount of time it
takes to process the request, making you site appear slower to outside
users. Some users may have special requirements that necessitate storing the
images in the database, but, in most situations, the gain is little to
nothing.
There are a lot of other smaller issues, such as the way ColdFusion handles
binary data and how that data is transferred between the database server and
the ColdFusion application server, but hopefully the above description will
give you a general idea of why developers always caution against storing
image data directly in the database.
Benjamin S. Rogers
Web Developer, c4.net
voice: (508) 240-0051
fax: (508) 240-0057
-----Original Message-----
From: Rick Lamb [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 08, 2001 5:40 PM
To: CF-Talk
Subject: RE: Storing images in SQL
Larry,
I've asked this question before also. And got similar responses as yours
which is appreciated. But is there any way you could tell us (a number of
people have asked this question) why it's slower and messier. I'm sure for
somebody that knows the details on what it takes to do this can logically
agree with you (and others who have said the same) and I too would like to
be one of those well informed people that come to the same conclusion for
the same reasons. Could you or anyone else familiar with the techniques
required for this please elaborate on this so I can say something other than
"it's a bad idea because that's what somebody said."
Thanks,
Rick
-----Original Message-----
From: Larry C. Lyons [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 08, 2001 3:28 PM
To: CF-Talk
Subject: Re: Storing images in SQL
Michael,
In a nutshell don't. Its far faster, easier and a lot less messy to
store the images's name and then dynamically construct the path
information.
regards,
larry
--
Larry C. Lyons
ColdFusion/Web Developer
EBStor.com
8870 Rixlew Lane, Suite 201
Manassas, Virginia 20109-3795
tel: (703) 393-7930 x253
fax: (703) 393-2659
http://www.ebstor.com
http://www.pacel.com
email: [EMAIL PROTECTED]
Chaos, panic, and disorder - my work here is done.
--
Michael Buffington wrote:
>
> Does anyone have any experience storing images in SQL 7.0, and retrieving
> them in CF?
>
> Any tips would help!
>
> Michael Buffington
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
