BLOB performance on WAN

FanderlF · Post by **FanderlF** » Thu 10 Apr 2008 14:24

Hey everybody,

I'm developing a Software for automated document creating. I store Text as RTF in BLOB Fields in the Database. For the Document I need to retrieve about 400 Rows each containing 2 BLOB fields (containing RTF Data). Data in BLOB Fields can vary very much from NULL to one word, from several sentences to multiple pages (including emdedded pictures). So there is no chance to use VARCHAR2.

In our LAN at our site everything works perfectly performant, but if we test the program on other sites (each having at least a 30 MBit/s connection) retrieving of the Data gets very very slow. But only if we request BLOB (or CLOB) Fields.

In my example I got the following flow:

SELECT * FROM VIEW WHERE ID_DOCUMENT = ORDER BY POSITION

so I do only get the data I need and in the right order.

Now I step through each line and reading out the content:

Code: Select all

while(!oq_Query->Eof)
  {
    // Here I read out all fields of each record
    // using oq_Query->FieldByName()->AsString
    oq_Query->Next();
  }

If I do this at my site everything works performant (10 seconds max). If I do the same on an external site the same Query takes over 7 minuts.

I sniffed the network traffic which was about 6 megabyte. Copying a File of 6 megabyte to the other site took about 40 seconds.

We've got an Oracle8i Release 8.1.7.4.1 DB. Our IT Service told me that the DB traffic on the network is NOT throttled to the other plant.

In my eyes I see the problem when fetching the BLOBs. But if I set CacheLobs to true (which imho should be faster in my example) that doesn't change anything.
I converted the tables to CLOB, but that didn't change anything either.

I don't know where to search else... maybe there's an option in the components or the Oracle Client itselt (oci.dll) where I can change something concerning LOB caching.

Or are there any other tweaks I could do to improve performance?

Hope anybody has a clue...

else I would only have the chance to code a new application based on an application server.

Thanks in advance,

Florian Fanderl

P.S.: I also read the performance tuning tips in the manual, but that didn't help either

Plash · Post by **Plash** » Mon 14 Apr 2008 07:56

Possibly, data will be transfered faster if you store a whole document in one BLOB value.

FanderlF · Post by **FanderlF** » Mon 14 Apr 2008 08:22

well the problem is that one document consists of documentparts (about 400 in my case). Each of those parts has to stand alone as they can be used in other documents and are version managed (each part can have several revisions). You see the problem...
I store the final document in the DB, too. But if you want to edit anything in this document the whole document needs to be fetched and that needs really long...

well maybe it's a general problem of Oracle that LOBs on WAN (at least many LOBs) are slow and we should have taken an other design for our program/DB. Maybe a solution with an Application Server would have fit our needs better...

I just wanted to know if I have missed an optimation Option on your components.

Plash · Post by **Plash** » Mon 14 Apr 2008 11:20

There are no options that can increase performance of LOB read.