Roger,
Thanks for sharing your personal experience, nothing better than them. 
Roberto Camargo.

--- On Fri, 4/20/12, rcrfb <r.craemer@...> wrote:

From: rcrfb <r.craemer@...>
Subject: [firebird-support] Re: why Blob is so slow ?
To: firebird-support@yahoogroups.com
Date: Friday, April 20, 2012, 10:13 AM



--- In firebird-support@yahoogroups.com, Ann Harrison <aharrison@...> wrote:
>
> On Thu, Apr 19, 2012 at 11:13 AM, Tupy... nambá <anhanguera@...>wrote:
>
> >
> > But, having many NFE (as many as the transactions), don´t you agree that
> > these BLOB´s will be a great source of fragmentation inside the DB ?
> >
>
> Err, no.  It's not.  I'm not 100% sure what you mean by fragmentation, but
> all data, metadata, blobs, internal structure and state are kept on fixed
> sized pages in a single file.  Yes, if you're running on a disk that's full
> and fragmented, that file will be scattered around the disk, but inside,
> it's quite tidy.
>
>
> > And, if I´m sure about my thinkings, as Firebird doesn´t have a way to
> > defragment inside the DB, you don´t have a way to resolve this.
> >
>
> When pages are released, they're reused.
>
>
> > May be, for having a good solution for such kind of business, one had to
> > use a MS SQL Server to periodically defragment the DB. Or another DB name
> > that has this funcionality. I searched something like this at Postgres and
> > I found a command named VACUUM that does something like this. Think about
> > all of this, if you want. If have to have BLOB´s, I think Firebird is not a
> > good solution for a great number of them. My thought, you don´t need to
> > agree.
>
>
> The PostgreSQL vacuum is similar to Firebird's continuous, on-line garbage
> collection, except that it's a separate, off-line command.
>
> Good luck,
>
> Ann
>
>
> [Non-text portions of this message have been removed]
>

Some years ago (at the time of Version 1.5) I observed the same behaviour backing up a database containing a lot of BLOBs.
While storing the 'normal' data was reasonably fast, it slowed down when it come to store the BLOBs.
Digging some deeper into that problem I found that the (System-File)-IO used uniformly large blocks while storing the non-BLOB tables. But when it came to the BLOB data the datablocks written to disk varied in size.
It seemed, dumping a BLOB is a two stage job: first dump the 'normal' data of the table's record, then dump the associated BLOB's data; then continue with the next record. So for dumping a table containing a BLOB the IO seemed to be the problem.
To resolve the problem we stored the BLOBs direct on disk and held only a reference in the Database (as already suggested).

Actually I don't work with firebird anymore, so I can't verify these observations anymore, but maybe these informations can help you.


Roger



------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links





[Non-text portions of this message have been removed]