Hi, Alexandre,
For the sample you gave (NFE), I agree with you, because the amount of files that will be generated will be very great and each file itself is not so big, probably they will not become a problem. And, in this case, they are part of a transaction. Probably not, but I´m not sure - one have to make comparisons to be sure about the best solution. I told in a generic way, specially were we have contracts, photos, and other no transactional documents.

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 ?
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.
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.
Friendly, best regards,Roberto Camargo.

--- On Thu, 4/19/12, Alexandre Benson Smith <iblist@...> wrote:

From: Alexandre Benson Smith <iblist@...>
Subject: Re: [firebird-support] why Blob is so slow ?
To: firebird-support@yahoogroups.com
Date: Thursday, April 19, 2012, 6:42 PM

Hi Roberto,

Em 19/4/2012 08:52, Tupy... nambá escreveu:
> Alexandre,
> At my point of view, I prefer avoid using BLOB fields. First of all, because these kind of field are not indicated for searches of any kind (most of them are pictures). Second,
> because
> normally they have very large content, what does the DB increase in a large amount. I think the most important property of the DB´s is the capability of searches. But having fields which  don´t allow us to do that, disturb the funcionality of DB´s.
> I prefer using to store files outside DB´s, storing inside them the path for the files. So, you have the speed at all operations (searches and backup´s/restores) and not a meaningfull increase of the DB´s.
>
> I´m not sure about the reasons for the backup/restore speed problem, but I believe that inside the DB happens almost the same as at OS environment = when adjacent areas are full, then the OS or the DB manager application most look for distant areas to store parts of the data, causing a data fragmentation. And to access the complete data, the OS or DB manager must "remount" them, before delivering to the client. And the DB itself suffers from the DB file fragmentation at disc level.
> At file servers, normally file fragmentation are low (you don´t edit them directly at the server) and still you can defragment the files.
> At SQL server, you find discussions about internal tables and indexes fragmentation, and you have commands to repair fragmentation.
> At Firebird/Interbase, nobody talks about that, but we know it happens and can became a problem, when the DB is greater in size. BLOB are worst for causing that, affecting not only the BLOB fields and data itself, but also fields and data of other data types. And you don´t have (i never see) commands for DB internal defragment.
> Try to do some experiences about that, making comparisons between different solutions for a same problem. May be imediatelly filled DB will not show great differences, but DB´s at common filling (day by day), after a great amount of time, will show meaningfull differences.
> Roberto Camargo,Rio de Janeiro / Brazil
>

In the past I used the approach of store just the filename, and I still
use in some cases, but when everything is inside the datase it's easier
to be sure that back-up/restore of everything is in place, to move the
content around, provide transaction control (all the ACID features) that
needs to be re-implemented if I work at filesystem level. Since you are
in Brazil I could point a case where the need to store blob's is almost
mandatory:
The storage of XML files of "Nota Fiscal Eletronica" (eletronic
invoice), We need to keep the data for the legal periods specified in
our legislation, and to handle thousands (millions ?) of individual
files on the filesystem is not the best option in my point of view, it's
much easier to be sure that everything is secure inside the database.

I disagree with you about the main feature of a RDBMS is search, search
is a part of the whole system, but the main feature in my point of view
is to store data. :) Of course there is no sense in store something if
you cannot search for it, but, you could have a product that stores the
data efficiently and not search it so efficiently called a RDBMS, but
the other way around is not possible. Quoting Ann Harrison from the top
of my head (probably not the exact words) "if you don't need a correct
answer, the answer is 13".

I don't use Blob's that much, but in some cases I think it's a good
sollution.

Anyway, thanks for sharing your thoughts, I know that store large binary
data inside/outside the database is the kind of thing that there is no
rule of thumb to choose between one or another, myself use both
approachs for distinct use cases.

My concerns is that something is "strange" regarding blob manipulation.
It's too slow to me.

see you !

Alexandre


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

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

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]