Blobs in SQL

Can anyone tell me what the pros/cons are of using blobs in sql?
We are looking at scanning certain paperwork and storing it with the
appropriate records in our database. The two options are to store the images
as blobs or to store them as file system objects with a referral pointer in
the db.
I have heard a number of different people say that storing blobs is
inherently evil but they haven't explained why that is.
Any help will be appreciated.This is one of those topics that has been debated countless times, and there
is no universal truth.
Storing in the database
-transactional integrity
-maintenance simplicity
Storing in the filesystem
-performance
-efficiency
-accessibility
"Jawallaby" wrote:
> Can anyone tell me what the pros/cons are of using blobs in sql?
> We are looking at scanning certain paperwork and storing it with the
> appropriate records in our database. The two options are to store the images
> as blobs or to store them as file system objects with a referral pointer in
> the db.
> I have heard a number of different people say that storing blobs is
> inherently evil but they haven't explained why that is.
> Any help will be appreciated.
>|||Thanks Dennis.
When you list performance as a pro of using the filesystem what are the
factors that increase performance? What is the performance hit when storing
in the db?
Most if not all of these files will be less than 1mb. There is a potential
for roughly 20 different files to be stored for each person/record in the db
and there are roughly 46000 records active.
I am trying to decide if this is worth worrying about or not and just don't
know enough about blobs in sql.
"Dennis Forbes" wrote:
> This is one of those topics that has been debated countless times, and there
> is no universal truth.
> Storing in the database
> -transactional integrity
> -maintenance simplicity
> Storing in the filesystem
> -performance
> -efficiency
> -accessibility
> "Jawallaby" wrote:
> > Can anyone tell me what the pros/cons are of using blobs in sql?
> >
> > We are looking at scanning certain paperwork and storing it with the
> > appropriate records in our database. The two options are to store the images
> > as blobs or to store them as file system objects with a referral pointer in
> > the db.
> >
> > I have heard a number of different people say that storing blobs is
> > inherently evil but they haven't explained why that is.
> >
> > Any help will be appreciated.
> >
> >|||Dennis,
We've been scanning and storing document images as 'blobs' in our Informix
db
basically for years. We opted for the transactional integrity &
maintenance simplicity
over the performance and efficiency. Most of our scanned .pdf documents
are
fairly small and average around 100k.
We're not a highly transactional environment and we may scan (insert or
update)
from 50 to 100 documents a day.
Using this method we totally eliminate any file naming concerns, pathing,
directory
file limit issues, etc. (since I've never tried to maintain a pointer /
file based I can't
really speak to the up and down side of the method)
We're currently in the process of migrating to Sql Server and I've verified
that the
Sql Server Image column works in a very similar manner to the Informix Byte
(blob)
columns. The up side of going to Sql Server is that we can now manage our
image table insert, updates & deletes using stored procedures. We don't
have that
ability with Informix using oledb connectivity.
You can see the system in action at the following urls by clicking the blue
permit
or certificate numbers at
http://apps.wrd.state.or.us/apps/wr/wrinfo/wrinfo.php?search_type=SNPID&snpid=64734
The downside can be database backups ... we currently have around 80G of
scanned imagesand a full db backup takes about 3 to 4 hrs to DLT tape.
Hope this helps.
Barry
in Oregon
"Jawallaby" <Jawallaby@.discussions.microsoft.com> wrote in message
news:24797C1E-595B-4C48-A4C7-AE264E8423BA@.microsoft.com...
> Thanks Dennis.
> When you list performance as a pro of using the filesystem what are the
> factors that increase performance? What is the performance hit when
> storing
> in the db?
> Most if not all of these files will be less than 1mb. There is a potential
> for roughly 20 different files to be stored for each person/record in the
> db
> and there are roughly 46000 records active.
> I am trying to decide if this is worth worrying about or not and just
> don't
> know enough about blobs in sql.
>
> "Dennis Forbes" wrote:
>> This is one of those topics that has been debated countless times, and
>> there
>> is no universal truth.
>> Storing in the database
>> -transactional integrity
>> -maintenance simplicity
>> Storing in the filesystem
>> -performance
>> -efficiency
>> -accessibility
>> "Jawallaby" wrote:
>> > Can anyone tell me what the pros/cons are of using blobs in sql?
>> >
>> > We are looking at scanning certain paperwork and storing it with the
>> > appropriate records in our database. The two options are to store the
>> > images
>> > as blobs or to store them as file system objects with a referral
>> > pointer in
>> > the db.
>> >
>> > I have heard a number of different people say that storing blobs is
>> > inherently evil but they haven't explained why that is.
>> >
>> > Any help will be appreciated.
>> >
>> >