images and SQL

images and SQL

Post by <chad> » Thu, 22 Jun 2000 04:00:00


Hi,
Can someone please fill me in on the performance issues of embedding image
files in a SQL server 7 database?

I am working on a large image database collection and am trying to decide
whether i should keep the files (up to 10000) in a directory on the server,
or in the database?
This will be for a project like photodisc so speed, performance, efficiency
and reliability are my main concerns.

So would you advise on having the images in database or in file system for
serving on the internet?

thanks

chad

 
 
 

images and SQL

Post by Neil Pik » Thu, 22 Jun 2000 04:00:00


Chad - ease of access is better with files, referential integrity is better
with embedded.  Performance shouldn't be much different.

Quote:> Can someone please fill me in on the performance issues of embedding image
> files in a SQL server 7 database?

> I am working on a large image database collection and am trying to decide
> whether i should keep the files (up to 10000) in a directory on the server,
> or in the database?
> This will be for a project like photodisc so speed, performance, efficiency
> and reliability are my main concerns.

> So would you advise on having the images in database or in file system for
> serving on the internet?

Q.     How do I store/retrieve text and image data in SQL Server?

A. To store/retrieve this sort of data within TSQL scripts you have to use the
WRITETEXT and READTEXT commands rather than standard INSERT/SELECT statements.  
These are documented, with examples, in the books-online but are basically a
real pain to use.  There are more manageable commands available from within the
relevant programming languages - e.g. RDO and ADO from VB/C can use GetChunk
and AppendChunk commands - but you still have to manage the image/text
chunks/blocks of data at a time.  About the only upside of storing this sort of
data within SQL Server is that it can be kept transactionally consistent with
the other data.  

For native ODBC access use the SQLPutData and SQLGetData commands.

If you just want to insert/retrieve an entire image/text then look at the
TEXTCOPY program (textcopy /? for parameters) in the <sql>\BINN directory.  It
is a command-line program along the lines of BCP.

There is a very useful (free) dll available from
http://www.chriscrawford.com/DBImage (courtesy of Chris Crawford).  It has
three functions:

 GetImage Used for getting an image from the database
 StoreImage Used to send an image to the database
 GetWebImage Used to send a DB image to a browser

If transactional consistency doesn't matter, or can be achieved
programmatically, then it is easier to store the data outside the database as
an ordinary file.  Within the database just hold a UNC pointer to where the
file is held.  This usually makes it much easier to display/edit the data as
the name can simply be passed to whatever tool is doing the manipulation.

===

v1.03 2000.05.09
Applies to SQL Server versions  : All
FAQ Categories                  : Database Administration, Application Design
and Programming
Related FAQ articles            : n/a
Related Microsoft Kb articles   : Q194975 - "Sample Functions Demonstrating
GetChunk and AppendChunk"
Other related information       : n/a
Authors                         : Neil Pike
 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (428 entries) see
 forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7
- SQL Public)
 or www.ntfaq.com/sql.html
 or www.sql-server.co.uk
 or www.mssqlserver.com/faq