TechTalk: Blocking data transfer can improve ODBC performance.

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

Q: I wrote a program that downloads a large number of records from our AS/400 to a client PC. The program is written in Visual Basic and uses the Client Access ODBC driver. It works correctly but is a bit slow. Is there anything I can do that might improve the performance?

A: When data is transferred between the AS/400 and an ODBC client, it is moved in blocks. Some communications overhead is associated with each block transmitted to the client PC. Fortunately, you can control the block size on the client to optimize it for your particular environment. The Client Access ODBC driver's default block size of 32KB is optimized for general use and should work for most installations. However, if you are transferring a large amount of data to the PC, you may want to increase this value to decrease the number of blocks transmitted, thereby reducing communications overhead.

To change the size of the blocks transmitted with the Client Access ODBC driver, you need to edit the ODBC.INI file in the Windows directory. Using NOTEPAD.EXE or some other text editor, locate the section for your data source name and add or change the "BlockSizeKB" entry to a value between 1 and 512. This is the size in KB of the transfer block. Experiment with different values to see what works best in your environment. Also, for this setting to have an effect, be sure the "RecordBlocking" entry is set to the default value of "2."

- Brian Singleton