Blob Performance

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill Hunt - 28 Oct 2003 18:05 GMT

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

Reply to this Message

Art S. Kagel - 28 Oct 2003 22:03 GMT

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
[quoted text clipped - 14 lines]
>
> Bill

Reply to this Message

rkusenet - 28 Oct 2003 22:35 GMT

> 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.

Reply to this Message

Art S. Kagel - 29 Oct 2003 00:44 GMT

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
[quoted text clipped - 6 lines]
>
> TIA.

Reply to this Message

Bill Hunt - 28 Oct 2003 23:50 GMT

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

> There are several things you can do to improve things:
>
[quoted text clipped - 29 lines]
> >
> > Bill

Reply to this Message

Art S. Kagel - 29 Oct 2003 00:45 GMT

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?
[quoted text clipped - 63 lines]
>> >
>> > Bill

Reply to this Message

Bill Hunt - 29 Oct 2003 16:02 GMT

Hi Art,
One last thing. I modified the blobload program that comes with esql/c
to use the LOCUSER functions like you said. My loc_read function always
seems to get called with 1024(bytes to transfer), i can't seem to get this
number larger. I tried setting loc_bufsize but it seems to be ignored. I
set loc_size = -1 and also set fetchbufsize to 32767. Anything else i can
do?

Thanks,
Bill

> Sorry, I try my best to avoid having anything more to do with WinXX than I have
> to.
[quoted text clipped - 68 lines]
> >> >
> >> > Bill

Reply to this Message

Art S. Kagel - 30 Oct 2003 14:47 GMT

Do you have a sniffer? The docs say that if loc_size = -1 the blob is sent as
a single unit. It would be interesing to watch the traffic on the port your
app opens and see if indeed the 1K 'reads' are being packaged up into 32K
blocks and send that way or are still being sent as individual 1K packets.

Art

> Hi Art,
> One last thing. I modified the blobload program that comes with esql/c
[quoted text clipped - 97 lines]
>> >> >
>> >> > Bill

Reply to this Message