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:
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.
0 Comments