Subject Re: performance problem with blobs. ### more info ###
Author aussie_1968
heya /heLen

thanx for the quick reply.
i must say, that i have not been working with dbses for the
last 4 years, so i am a bit rusty.

> What about the 40 Mb blob?
it gets inserted from a program. it is not part of the db when
i create it. 40Mb is just a test. the users will most probably
stick docs, pdf and jpegs in there, but it should also work with
larger files :)

> If there *is* actually a 40 Mb blob stored in the database, and your
> page size is the default 4Kb, that's a heck of a lot of page reads
> to retrieve one blob. (At least in a new database, it's likely the
> pages are contiguous...)
as i said, it is just a test. most of the data to be entered into the
db, are short notes, comments, names .....
so it would not make much senese to bump up the page size, just
because of maybe one larger file among thousands of litte comments.

> Is there anything interesting about the filesystem?
unfortunalety not.
linux: 60Gb ide drive using ext3
XP: 40Gb ide drive using ntfs

> There's not enough information here to even begin accounting for the
> platform difference.
tell me what you need an i will give it to you.

> Like, how much RAM is available?
hardware linux:
asus board with athlon 1800+
512Mb ram
intel Gb server ethernet card
60Gb hd with ext3 using ata133
custom Redhat 8.0 kernel with all patches
only quality components.
the system is idle most of the time. even when retrieving the
the blob, firebird does not get the cpu performance above 4%.
when using it as a file server it works great!!
when creating the database using ibExpert, the nearly 600 obj
get hammered in there within seconds. it is only possile to
count the steps of 100reds. all dataretrieval for treeViews
and list are hardly noticable.

hardware xp:
notebook with intel desktop cpu, 2.54GHz
768Mb ram
some 100Mbit network card
40Gb samsung drive
when getting files straight off the server the send/receive leds
on the GBit switch are permanently on.
when getting the blob from the server, the leds flash about every
.7 sec.

> what's the retrieval statement?
FbCommand dbCommand = new FbCommand( "select fileName, attachmentData
from VW_ATTACHMENT where attachment_id = " + pNode.getItemID(),
m_pMainWin.getDatabaseObj() );

CREATE TABLE attachment (
attachment_id INTEGER NOT NULL,
creationDate DATE,
contract_id INTEGER NOT NULL,
attachmentData BLOB,
fileName VARCHAR(255),
attachmentGroup_id INTEGER

select A.attachment_id, A.contract_id,, A.creationDate,
A.attachmentGroup_id, A.fileName, A.attachmentData
from attachment A

> How are you measuring retrieval time?
using a stopwatch in my mobile.
the 25min, by waiting for the mp3 to start playing while watching tv :)

> (What's the application code doing with the blob once it arrives?)
it writes it into a file and if possible executed. even if i do not
even use the stream it still takes that long.
for some reason it actually blocks when checking for NULL

if ( !dataReader.IsDBNull(1) )

> What transport protocol is being used?
i am using the .Net container for firebird (version 1.0)
in the visual studio C# .Net 1.0.
yes, i know i am still using the 1.0, but it works fine when the
db is on xp, so it should not be a problem with the older version.
network itself TCP

> What Server model (Classic/SS)?
SS on both machines
compiled the db on linux myself, no rpm. yes, did use the ss

serverVersion: LI-V6.3.0.4290 Firebird 1.5
ODS: 10.1
PageSize: 4096

serverVersion: WI-V6.3.0.4306 Firebird 1.5
ODS: 10.1
PageSize: 4096

>cache size?

what i do not understand is, why the SAME database has different
retrieval times on different OSes. when working with the database,
then the linux version is heaps faster!!