Re: Blobs and SQL Server

Tech-Archive recommends: Fix windows errors by optimizing your registry

Older versions of SQL Server required the blob be loaded in chunks, older
versions of ADO required you use the getchunk method, but newer versions
allow you use to stream object to push it in. The client will consume
virtual ram on the host it is running on, not necessarily the SQL Server
box.

There is a cpu and disk IO hit while inserting the blob. I have not
observered a RAM hit.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



<bormesh@xxxxxxxxx> wrote in message
news:1156277942.710422.326970@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Thanks for the information Hilary,

When I'm inserting the blob into the database, I was under the
impression that it had to be split up into a series of smaller files.
If a large file is inserted, then it will require many small chunks. As
we insert, I assume we're going to be using RAM to store the chunks and
then we'll hit back and forth between the file system and the database,
all the while using up our memory. Am I correct in this assumption? I
guess I'm just wondering at the feasiblity of actually inserting a
larger blob (maybe not as huge as 500 mb) on a slower computer
(assuming the computers that we're developing for have an average of
512MB of RAM). Or does the amount of RAM on the client system not
matter, and only the specs of our Database machine matter?

I hope this makes sense... I can give you more information if you need
it.

Thanks,
Rob Heinen


Hilary Cotter wrote:
Yes, there is a performance hit while importing blobs, mainly disk and to
a
lesser degree cpu. 500 Mgs is a large chunk of data, there is no easy way
to
push it in. Any way you could extract the textual content and store it in
the db?

In general you store the blob in the database for manageability reasons.
For
performance reasons you store the path in the db and keep the blob in the
file system. Research has indicated that you should store the blobs in a
directory structure maintained by a hash so that you have a large number
of
subdirectories. retrieving a blob this way offers best seek performance
in
NTFS. Also web servers are optimized for sending files, databases aren't.
You can also send the file to the client asynchronously using an iframe,
if
you are retrieving it from the db it would be done serially.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



<bormesh@xxxxxxxxx> wrote in message
news:1156194912.604401.188440@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hey Hilary,

Mike from microsoft.public.sqlserver.programming told me to talk to
you about my question. Here's my original post:

I'm looking for some information about storing BLOBs in SQL. A bit of
background: my company is developing a document management system.
Previously, we had stored the files on the file structure and used a
pointer in the database. We're not switching because the next iteration
of our system is going to allow for full text searches, and this
becomes a simple task when the entire document is stored in the
database.

My question is how performance will be affected when I want to import
large documents/files into the database? Will I see a huge slow down if
I import a 500 MB file? I've researched this a bit, and I don't want to
use a bit array to import the document into the database. I'd rather
just import it (but will memory be affected horribly?)

If anyone could help me out a bit, or just point me in the right
direction, that would be great.



Thanks for your help,

Rob Heinen




.

Relevant Pages

  • RE: How to make your SQL Server database grow exponentially with linear data
    ... > We have this database that we use to collect 1000's of rows of data a day. ... > SQL Server reports no free space. ... turns out the problem are with the blob fields. ... > and inserted a new row we probably would have been able to shrink the ...
    (microsoft.public.sqlserver.server)
  • Re: How to make your SQL Server database grow exponentially with linear data
    ... Turns out it's a bug in SQL Server, ... database, which means there was only about 17gb of actual data in the ... separate data file, so when it gets out of control again we can just move ... turns out the problem are with the blob fields. ...
    (microsoft.public.sqlserver.server)
  • Re: manipulating blobs in sqlplus
    ... SQL Server). ... There is also no size limit on the blob field ... site outside of the database and have a script load them in. ... 15 returning theBlob into l_blob; ...
    (comp.databases.oracle.server)
  • How to make your SQL Server database grow exponentially with linear data
    ... We have this database that we use to collect 1000's of rows of data a day. ... SQL Server reports no free space. ... turns out the problem are with the blob fields. ... of a bigger blob, but they are much lower on the exponential curve than the ...
    (microsoft.public.sqlserver.server)
  • Re: Hello: not so much php, more like HTML..?
    ... Jerry would tell you to just serve them up straight from the database, and forget about the filesystem, I'm not so sure:). ... I suspect the answer is that for n files, us the pth root of n as the number of subdirs, where p is the depth of the subdirs...but a lot depends on caching algorithms in the directories. ... I shudder to think how long adding slashes to a 60Mbyte binary image might take, or indeed how much virtual memory holding it in a 60Mbyte php string might use.. ... Anyone ever handled BLOB objects that large, ...
    (comp.lang.php)