Blob Performance
Problem: inserting a 3MB blob from a Windows XP client (blobload ESQL/C, and a VB/OLE DB app) into IDS 7.23 on Solaris took ~30 seconds, while FTP of the same file took seconds; a packet trace showed ~3000 packets instead of ~159. Art Kagel explained that blobs are shipped in 1K chunks by default and advised raising the SQL communications buffer (FETBUFSIZE/FetBufSize to 32767) and using the LOCUSER method with loc_size set to -1 in the loc_t structure (sample loc_open/read/close in his ul.ec in the IIUG repository). The poster saw some improvement from FET_BUFF_SIZE but inconsistent speeds; questions about equivalent tuning for JDBC and VB/OLE DB went unanswered, so no full resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello,
I am trying to track down a problem with inserting blobs. When i save a
blob to a table that is around 3 meg it consistently takes 30 seconds or so.
I am using the blobload esqlc program that comes with the client SDK(2.81).
I have the same problem using a VB app with the informix oledb driver. The
client is running windows XP. At first i thought it was a network issue on
the LAN, but i can ftp the file to the server in only a second or two. Then
I though it was something to do with how the DB was storing the blobs, but I
was able to unload and then load a blob(the same 3 meg file) from dbaccess
on the server in about 4 seconds. My server is Intel solaris running IDS
7.23UC1. One weird thing i noticed is that when i FTP'd the file to the
server i saw 159 packets go out across the network, when i ran blobload
there were over 3000 packets sent. Any idea what this could be or how I
could troubleshoot the problem.
Let me know if you need any more info.
Bill
On Tue, 28 Oct 2003 12:05:00 -0500, Bill Hunt wrote:
There are several things you can do to improve things:
1- Increase the size of the SQL Communications Buffer. This defaults to 4k.
export FETBUFSIZE=32767 (the max value) in the environment of the task or set
the global variable FetBufSize = 32767; in the program after opening the
database and before transferring any data.
2- By default BLOBS are copied to the server by a client in 1K packets (which
explains the 3000 packets for the 3MB BLBO!). You can change this in the
loc_t structure before inserting the BLOB by setting loc_size to -1 and using
the LOCUSER method (you can find sample loc_open, loc_read, and loc_close
functions in my ul.ec utility in the package utils2_ak in the IIUG Software
Repository, these are VERY simple functions).
Art S. Kagel
> Hello,
> I am trying to track down a problem with inserting blobs. When i save a
> blob to a table that is around 3 meg it consistently takes 30 seconds or so. I
> am using the blobload esqlc program that comes with the client SDK(2.81). I
> have the same problem using a VB app with the informix oledb driver. The
> client is running windows XP. At first i thought it was a network issue on
> the LAN, but i can ftp the file to the server in only a second or two. Then I
> though it was something to do with how the DB was storing the blobs, but I was
> able to unload and then load a blob(the same 3 meg file) from dbaccess on the
> server in about 4 seconds. My server is Intel solaris running IDS 7.23UC1.
> One weird thing i noticed is that when i FTP'd the file to the server i saw
> 159 packets go out across the network, when i ran blobload there were over
> 3000 packets sent. Any idea what this could be or how I could troubleshoot
> the problem.
>
> Let me know if you need any more info.
>
> Bill
> 2- By default BLOBS are copied to the server by a client in 1K packets (which > explains the 3000 packets for the 3MB BLBO!). You can change this in the > loc_t structure before inserting the BLOB by setting loc_size to -1 and using > the LOCUSER method (you can find sample loc_open, loc_read, and loc_close > functions in my ul.ec utility in the package utils2_ak in the IIUG Software > Repository, these are VERY simple functions). This is for ESQLC only. What is the equivalent of this in JDBC. TIA.
Thanks Art,
Is there anyway to change the packet size for a VB app using oledb?
Also there is a variable in setnet call FET_BUFF_SIZE that i set to 32767.
After setting this value i seem to get much faster transfer rates sometimes.
It actually seems that every other time I log in the speed goes from fast to
extremely slow. Our customer periodially saves large blobs to the DB(20-30
meg) and they are complaing about performace(several minutes to save a
file). The app is in VB.NET using the oledb provider from informix. The
only thing i can think to do is to create the save code in a DLL written in
esqlc and use the technique you describe to save the BLOB. Would this even
be possible?
Thanks again,
Bill
"Art S. Kagel" <kagel@bloomberg.net> wrote in message
news:pan.2003.10.28.16.03.04.132216.10594@bloomberg.net...
> On Tue, 28 Oct 2003 12:05:00 -0500, Bill Hunt wrote:
>
> There are several things you can do to improve things:
>
> 1- Increase the size of the SQL Communications Buffer. This defaults to
4k.
> export FETBUFSIZE=32767 (the max value) in the environment of the task
or set
> the global variable FetBufSize = 32767; in the program after opening
the
> database and before transferring any data.
> 2- By default BLOBS are copied to the server by a client in 1K packets
(which
> explains the 3000 packets for the 3MB BLBO!). You can change this in
the
> loc_t structure before inserting the BLOB by setting loc_size to -1 and
using
> the LOCUSER method (you can find sample loc_open, loc_read, and
loc_close
> functions in my ul.ec utility in the package utils2_ak in the IIUG
Software
> Repository, these are VERY simple functions).
>
> Art S. Kagel
>
> > Hello,
> > I am trying to track down a problem with inserting blobs. When i
save a
> > blob to a table that is around 3 meg it consistently takes 30 seconds or
so. I
> > am using the blobload esqlc program that comes with the client
SDK(2.81). I
> > have the same problem using a VB app with the informix oledb driver.
The
> > client is running windows XP. At first i thought it was a network issue
on
> > the LAN, but i can ftp the file to the server in only a second or two.
Then I
> > though it was something to do with how the DB was storing the blobs, but
I was
> > able to unload and then load a blob(the same 3 meg file) from dbaccess
on the
> > server in about 4 seconds. My server is Intel solaris running IDS
7.23UC1.
> > One weird thing i noticed is that when i FTP'd the file to the server i
saw
> > 159 packets go out across the network, when i ran blobload there were
over
> > 3000 packets sent. Any idea what this could be or how I could
troubleshoot
> > the problem.
> >
> > Let me know if you need any more info.
> >
> > Bill
On Tue, 28 Oct 2003 16:35:45 -0500, rkusenet wrote: Don't know the JDBC interface well. Check the manuals. Art S. Kagel >> 2- By default BLOBS are copied to the server by a client in 1K packets (which >> explains the 3000 packets for the 3MB BLBO!). You can change this in the >> loc_t structure before inserting the BLOB by setting loc_size to -1 and >> using the LOCUSER method (you can find sample loc_open, loc_read, and >> loc_close functions in my ul.ec utility in the package utils2_ak in the >> IIUG Software Repository, these are VERY simple functions). > > This is for ESQLC only. What is the equivalent of this in JDBC. > > TIA.
On Tue, 28 Oct 2003 17:50:07 -0500, Bill Hunt wrote:
Sorry, I try my best to avoid having anything more to do with WinXX than I have
to.
Art S. Kagel
> Thanks Art,
> Is there anyway to change the packet size for a VB app using oledb?
> Also there is a variable in setnet call FET_BUFF_SIZE that i set to 32767.
> After setting this value i seem to get much faster transfer rates sometimes.
> It actually seems that every other time I log in the speed goes from fast to
> extremely slow. Our customer periodially saves large blobs to the DB(20-30
> meg) and they are complaing about performace(several minutes to save a file).
> The app is in VB.NET using the oledb provider from informix. The only thing i
> can think to do is to create the save code in a DLL written in esqlc and use
> the technique you describe to save the BLOB. Would this even be possible?
>
> Thanks again,
> Bill
> "Art S. Kagel" <kagel@bloomberg.net> wrote in message
> news:pan.2003.10.28.16.03.04.132216.10594@bloomberg.net...
>> On Tue, 28 Oct 2003 12:05:00 -0500, Bill Hunt wrote:
>>
>> There are several things you can do to improve things:
>>
>> 1- Increase the size of the SQL Communications Buffer. This defaults to
> 4k.
>> export FETBUFSIZE=32767 (the max value) in the environment of the task
> or set
>> the global variable FetBufSize = 32767; in the program after opening
> the
>> database and before transferring any data.
>> 2- By default BLOBS are copied to the server by a client in 1K packets
> (which
>> explains the 3000 packets for the 3MB BLBO!). You can change this in
> the
>> loc_t structure before inserting the BLOB by setting loc_size to -1 and
> using
>> the LOCUSER method (you can find sample loc_open, loc_read, and
> loc_close
>> functions in my ul.ec utility in the package utils2_ak in the IIUG
> Software
>> Repository, these are VERY simple functions).
>>
>> Art S. Kagel
>>
>> > Hello,
>> > I am trying to track down a problem with inserting blobs. When i
> save a
>> > blob to a table that is around 3 meg it consistently takes 30 seconds or
> so. I
>> > am using the blobload esqlc program that comes with the client
> SDK(2.81). I
>> > have the same problem using a VB app with the informix oledb driver.
> The
>> > client is running windows XP. At first i thought it was a network issue
> on
>> > the LAN, but i can ftp the file to the server in only a second or two.
> Then I
>> > though it was something to do with how the DB was storing the blobs, but
> I was
>> > able to unload and then load a blob(the same 3 meg file) from dbaccess
> on the
>> > server in about 4 seconds. My server is Intel solaris running IDS
> 7.23UC1.
>> > One weird thing i noticed is that when i FTP'd the file to the server i
> saw
>> > 159 packets go out across the network, when i ran blobload there were
> over
>> > 3000 packets sent. Any idea what this could be or how I could
> troubleshoot
>> > the problem.
>> >
>> > Let me know if you need any more info.
>> >
>> > Bill