TPT performance with SQL Server

Connectivity
Enthusiast

TPT performance with SQL Server

Can anyone suggest TPT parameters that would make extract/load from SQL Server faster?

3 REPLIES
Apprentice

Re: TPT performance with SQL Server

Hi,

I suspect that there is little that can be done from the TPT side to change performance of this part of your processing.

 

You might want to look at the DataBlockSize attribute. That defaults to 64KB but can go up to @3MB.

Other than that possibly the main mechanism for speeding up the extract of SQL server data is the SQL that is used and SQL server options, table design etc.

 

If you've got a TPT job already coded, try running the sql outside of TPT. Compare the performance of that query with the same SQL running within the TPT job. Focus (obviously) on the duration of this part of the job, not the whole job - i.e. loading into TD).

 

To get a realistic comparison, perhaps run the SQL 'natively' and write out to a data file. Then run the SQL from within a modified TPT job which simply writes out to a file.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: TPT performance with SQL Server

you havent mentioned why you think its slow?

Highlighted

Re: TPT performance with SQL Server

WhereScape RED allows you to specify the number of read instances in TPT ODBC.  However, I have never measured an increase in SQL Server's throughput beyond 2 instances.  Four instances will degrade throughput.

DataBlockSize, when coordinated with the Teradata Tools & Utilities bundled Data Direct ODBC driver setting for Oracle has shown significant performance improvement.   Sadly, this is not true for SQL Server - there is no buffer setting in the Data Direct SQL Server ODBC driver.


However, I'd still execute a careful test measurement process to prove what is optimal on your SQL Server platform.