Re: Storing word documents, spreadsheets... in SQL database

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 13:43:05 -0800

I'll believe WinFS when I see it. It's been pulled from Longhorn and "scheduled" to be released at a later date if they can ever figure out how to get it to work efficiently.
Even then we'll see that storing BLOBs in the database is 6x slower than storing them in a file and saving the path to the file instead. I've droned on and on about this for a decade. Virtually all of the facts are still true today--even though SS has gotten a lot faster at saving and retrieving BLOBs.
  a.. BLOBs require serialization so they can be transmitted to the server and stored as TEXT or IMAGE types in the database. This takes time and requires the packets to be sent via TDS to (and from) the server.
  b.. The coding of BLOBs is complex (to say the least). While ADO.NET makes it easier, there are still a half-dozen special rules that apply to BLOB handling.
  c.. Since most BLOBS are RO, it makes no sense to include them in every backup of the DBMS. This means you'll want to write special backups and move the BLOB data to tables in separate database segments. This makes more work for your DBA. I'm sure she/he will appreciate it.
  d.. When BLOBS are read into memory by ADO (any version) you have a binary chunk of memory that the source program (Word etc.) can't read. This means you'll have to take an extra step to save the data to a temporary file and use Word to open the file. When Word saves the temp file, it won't save it to the DB unless you write code in Word to override the Save and rerun the routine to save it to the database.
I could go on and on... most of my books detail the litany of issues. 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit. 
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message news:9avvt09m9iojpab9g5f89f1durn02tp5pk@4ax.com...
> On Sat, 8 Jan 2005 15:16:53 +0100, "Q" <Q@nomail.be> wrote:
> 
> ¤ Hey Bill,
> ¤ 
> ¤ What are the disadvantages of storing BLOBs in the database?
> ¤ By the way,it is not a homework assignment! At our firm, we have to
> ¤ distribute all kinds of documents
> ¤ to different people. Some should have access to documents, while others
> ¤ won't.
> ¤ The access to the documents depends on the contents of the document.
> ¤ We also want to track changes to the document, and we have to know who
> ¤ accessed the document and who did
> ¤ make some changes. That's why I wanted to store them in a database, and give
> ¤ permissions this way.
> ¤ Do you see another possibility?
> ¤ 
> 
> The primary disadvantage is that it's rather cumbersome to do this and results in a fair amount of
> storage and retrieval overhead.
> 
> Of course in a few years it will likely be a moot point as the file system and database system
> merges into WinFS. In addition, Sharepoint Services currently uses SQL Server for document storage.
> 
> 
> Paul ~~~ pclement@ameritech.net
> Microsoft MVP (Visual Basic)
Quantcast