How to Increase Client Response Buffer Size

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 like, for instance, between an on-premise 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

Please follow the instructions below to set the response buffer size in client applications to 1MB.

ODBC

Windows

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:

Response_Buffer_Size_Windows.png

Enter 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 & 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:

[sample]
Driver=/opt/teradata/client/15.10/odbc_64/lib/tdata.so
Description=Datometry Hyper-Q
DBCName=hq
LastUser=
Username=
Password=
Database=dbname
DefaultDatabase=defdb
MaxRespSize=1048576

JDBC

JDBC defaults to 1MB response buffer size. No changes are needed.

BTEQ

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.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.