BLOB performance on WAN
Posted: 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:
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
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();
}
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