Re: Odp: [firebird-support] Request for advice

[Alexandre Benson Smith]

Em 25/4/2014 01:31, liviusliv...@poczta.onet.pl escreveu:



Hi,

But you also misunderstud advice.
Blobs are not stored in table with its other fields. In record exists 
only blob id and blob is stored on separate pages. Then moving it to 
separate table do nothing.



But i always prefere store big data like document scans outside of 
database and in main database have only some hint about file name 
without final location.


Only application have knowlage about final location and can load document

Regards,
Karol Bieniaszewski



Nope...

If the blob is small enough to fit on the data page with the records it 
will be stored there. I don't remember the rule exactly, but it is how 
it works.


It reduces dramatically the number of records per page, once the most of 
the page are used to store the blob content.


see you !

Re: Odp: [firebird-support] Request for advice

[Mark Rotteveel]

On Fri, 25 Apr 2014 07:01:31 +0200, Thomas Beckmann
 wrote:
> You are right: Blobs are stored in separate pages. But having blob
> fields inside tables with other fields forces these pages to be
> allocated when record is created - whether the blob will contain data or
> not.

Blob pages are only allocated when a blob is created and filled, so if you
insert a record where the blob field is null, then no additional pages get
created.

> Furthermore, common (bad) practice of fetching all fields (select *
> from), sometimes even performed by database access layer of programming
> language against the developers wish, accesses this data and decreases
> performance.

That is only if you actually access the blob (as in: get the blob id and
then proceed to load the blob), or use an access component that does this
automatically for you.
 
Mark

Re: Odp: [firebird-support] Request for advice

[Thomas Beckmann]

Good morning Karol,

yes, storing big data chunks outside database file is an opinion we
regularly use, too. It has the serious disadvantage, that you've to deal
with different addressing and right schemes on different operating
systems and deployment environments (local/remote servers, UNC-paths
etc) and with particular backup mechanisms to keep everything
consistent, though.

You are right: Blobs are stored in separate pages. But having blob
fields inside tables with other fields forces these pages to be
allocated when record is created - whether the blob will contain data or
not. Furthermore, common (bad) practice of fetching all fields (select *
from), sometimes even performed by database access layer of programming
language against the developers wish, accesses this data and decreases
performance.

Thus, having blobs stored in different table(s) usually leads to better
performance, because blob access is more evident and people (hopefully)
think twice before fetching...

Regards, Thomas

> But you also misunderstud advice.
> Blobs are not stored in table with its other fields. In record exists
> only blob id and blob is stored on separate pages. Then moving it to
> separate table do nothing.
> 
> 
> But i always prefere store big data like document scans outside of
> database and in main database have only some hint about file name
> without final location.
> 
> Only application have knowlage about final location and can load document
> 
> Regards,
> Karol Bieniaszewski
> 
> - Reply message -
> Od: "Thomas Beckmann" 
> Do: 
> Temat: [firebird-support] Request for advice
> Data: pt., kwi 25, 2014 00:46
> 
> 
>  
> 
> As I fear, some people misunderstood Frank's hints, I'd like to clarify:
> I know no reason to store blobs in data table with other objects data,
> it should be stored in separate table(s).
> 
> This does not (significantly) grow database, does not make it
> significantly slower and does not have any impact on backup/restore: But
> performs ways better, if blob fields are not main data fields.
> 
> This "optimization" has completely different impacts than moving blob
> data to a different database: Not only that every select has to be
> evaluated on runtime by execute statement, it has to be fetched from
> "far" destination (with no optimization hints), and there are no means
> to keep referential integrity by database design between the two databases.
> 
> Thomas
> 
> Am 25.04.2014 00:29, schrieb Alexandre Benson Smith:
>>
>>
>> Em 24/4/2014 17:08, Mark Rotteveel escreveu:
>>> On 24-4-2014 20:49, Frank Schlottmann-Gödde wrote:
 As a compromise, I would suggest to place your blobs into a separate
 table, it will increase the size of your main database, but will also
 speed up the acccess to your blobs without interfering with everyday
 database operations.
>>> As most blobs are stored out of band, I would expect that moving them to
>>> a separate table this doesn't have a big impact unless you have the
>>> habit of always retrieving and loading all fields of a table.
>>>
>>> Mark
>>
>> If the blob's are small enough to fit into the data page, it will
>> dramatically reduce the number of records per page, and ever if you
>> don't select it you will read the whole page, where most part of it is
>> the blob content.
>>
>> Moving the blob to a separate table would reduce this.
>>
>> see you !
>>
>>
> 
> -- 
> Mit freundlichen Grüßen,
> 
> Thomas Beckmann
> Diplom-Informatiker
> 
> Wielandstraße 14c • 23558 Lübeck
> Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
> Mail thomas.beckm...@assfinet.de 
> 
> ASSFINET-Logo
> 
> *ASSFINET Dienstleistungs-GmbH*
> Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
> i...@assfinet.de  • www.assfinet.de
> 
> 
> Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
> Registergericht Koblenz HRB 23331
> 
> Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
> richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
> informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
> Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist
> nicht gestattet.
> 
> 

-- 
Mit freundlichen Grüßen,

Thomas Beckmann
Diplom-Informatiker


Wielandstraße 14c • 23558 Lübeck
Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604
Mail thomas.beckm...@assfinet.de 

ASSFINET-Logo

*ASSFINET Dienstleistungs-GmbH*
Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn
i...@assfinet.de  • www.assfinet.de


Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Registergericht Koblenz HRB 23331

Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der
richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubt

Odp: [firebird-support] Request for advice

[liviusliv...@poczta.onet.pl]

Hi,

But you also misunderstud advice.
Blobs are not stored in table with its other fields. In record exists only blob 
id and blob is stored on separate pages. Then moving it to separate table do 
nothing. 


But i always prefere store big data like document scans outside of database and 
in main database have only some hint about file name without final location.

Only application have knowlage about final location and can load document

Regards,
Karol Bieniaszewski

- Reply message -
Od: "Thomas Beckmann" 
Do: 
Temat: [firebird-support] Request for advice
Data: pt., kwi 25, 2014 00:46
As I fear, some people misunderstood Frank's hints, I'd like to clarify:

I know no reason to store blobs in data table with other objects data,

it should be stored in separate table(s).



This does not (significantly) grow database, does not make it

significantly slower and does not have any impact on backup/restore: But

performs ways better, if blob fields are not main data fields.



This "optimization" has completely different impacts than moving blob

data to a different database: Not only that every select has to be

evaluated on runtime by execute statement, it has to be fetched from

"far" destination (with no optimization hints), and there are no means

to keep referential integrity by database design between the two databases.



Thomas



Am 25.04.2014 00:29, schrieb Alexandre Benson Smith:

>  

> 

> Em 24/4/2014 17:08, Mark Rotteveel escreveu:

>> On 24-4-2014 20:49, Frank Schlottmann-Gödde wrote:

>>> As a compromise, I would suggest to place your blobs into a separate

>>> table, it will increase the size of your main database, but will also

>>> speed up the acccess to your blobs without interfering with everyday

>>> database operations.

>> As most blobs are stored out of band, I would expect that moving them to

>> a separate table this doesn't have a big impact unless you have the

>> habit of always retrieving and loading all fields of a table.

>>

>> Mark

> 

> If the blob's are small enough to fit into the data page, it will

> dramatically reduce the number of records per page, and ever if you

> don't select it you will read the whole page, where most part of it is

> the blob content.

> 

> Moving the blob to a separate table would reduce this.

> 

> see you !

> 

> 



-- 

Mit freundlichen Grüßen,



Thomas Beckmann

Diplom-Informatiker



Wielandstraße 14c • 23558 Lübeck

Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604

Mail thomas.beckm...@assfinet.de 



ASSFINET-Logo



*ASSFINET Dienstleistungs-GmbH*

Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn

i...@assfinet.de  • www.assfinet.de





Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann

Registergericht Koblenz HRB 23331



Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der

richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben,

informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.

Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist

nicht gestattet.