ODBC load performance

Connectivity
Enthusiast

ODBC load performance

Hi,

I'm trying to load a td table via odbc based tool (MS access, SQL Server, etc).
and experiencing load times of 20-30rows/sec on my dev and prod boxes. Dev is 1node 4480, prod is 8 node 5400

I need to load hundreds of thousands of rows via ODBC/Oledb/jdbc pretty quick. Please don't tell me this is as good as it gets.

I'm trying to capture the ODBC trace but haven't been successful, but I suspect that it's executing explicit statements ( INSERT into... values ('A','B'...) ) that need to be reparsed for each row.

But I can't think of any way to make a generic tool use the USING phrase.

1. Is this load time what people expect.

2. Can anybody give me some thoughts on how to improve the load preformance.

Thanks.
1 REPLY
Teradata Employee

Re: ODBC load performance

Hello,

Well we can expect abit more from ODBC, but definitely alot more from Teradata. Your best option to load data is to make a flat file and load it via FastLoad or MultiLoad or perhaps you can use TPT and DataDirect to accomplish that.

In SQL Server, if you are accessing Teradata as Linked-Server then I guess this is way too good what you are getting.

Apart of ODBC there are also other factors to look for, which includes network delay, network bandwidth, harddisk delay, ODBC data packet size, etc. And as ODBC is not a loading utility (and loading utilities dont use ODBC), so I suspect it must be firing either a separate INSERTs for each row, or INSERT...SELECT.

HTH.

Adeel