Increasing a client's response buffer size can greatly improve the performance of queries that return large result sets. In particular on long-distance links such as those between an on-premises data center and a cloud provider.
Consider the following example of transferring a 100MB result set over a link with 50ms latency using the default of 8KB (or 64KB on newer driver versions) and 1MB buffer size:
|Buffer Size||Effective throughput w. 50ms latency||Transfer time for 100MB result set|
|8KB||8KB/50ms = 160KB/s||10min 25s|
|64KB||64KB/50ms = 1280KB/s||1min 18s|
|1MB||1MB/50ms = 20MB/s||5s|
To set the response buffer size in client applications to 1MB, see the instructions for your database driver:
The response buffer size for a DSN can be adjusted in the DSN's configuration under "Options" -> "Advanced" -> "Maximum Response Buffer Size", as shown below:
1048576 as new value and close the dialog windows with "OK". Then close and reconnect your client's ODBC connection using the updated DSN.
Unix and Linux
The response buffer size for a DSN can be adjusted by adding the option
MaxRespSize=1048576 to the DSN definition in
odbc.ini, as shown at the end of this sample entry:
JDBC defaults to 1MB response buffer size. No changes are needed.
The response buffer size for BTEQ can be set using the session parameter
respbuflen. At the beginning of the BTEQ script, add the following line:
.session respbuflen 1048418
Note that the maximum value is
1048418, as BTEQ adds a few bytes to the user-supplied value.