Parallel apply

Database

Parallel apply

Hi

We have a Data warehouse on Tera data 12.0 ,ODBC 3.5 .We have to feed the Teradata warehouse from an Oracle database. We have a third party tool which will connect to Tera data database using ODBC and will insert /update /delete records.

The insert rates are very high on the source oracle database and we have to apply the records at the same speed. We have a primary index defined on the Teradata target tables.

I have few questions

1) I guess we require multiple ODBC connections to Tera data database from the third party application to apply the records to Tera data to achieve reasonable apply rates(Parallel apply).
We will be inserting/updating/deleting rows to the same tables from multiple sessions. Would this have any performance impact(Locking etc )

2) We are looking at inserting 1000 rows per second in Teradata .Can someone share their experience with Tera data(Configuration / Best practices for this work load).

Regards
Lakshmi
Tags (1)
4 REPLIES
Senior Apprentice

Re: Parallel apply

Hi Lakshmi,
ODBC is the worst connection type for that scenario.
Your 3rd party tool should at least support "array inserts" to submit batches instead of single row inserts.

If you use multiple sessions you must choose the right PI to avoid locks/deadlocks, etc. etc.

Better contact your Teradata support, they can help you.

Re: Parallel apply

Our application supports Array Inserts.Not sure if this would be enough to achieve the high apply rate.
Enthusiast

Re: Parallel apply

In ODBC standard there's an option called SQL_ATTR_PARAMSET_SIZE

The ETL tool will name this option differently.  If you turn on ODBC tracing, you should spot this there. Otherwise, this feature is not turned on.

I tested DataDirect 6.0 + Informatica 8.6, the insert performance is under 100 rows/sec, which is very disappointing.

The network latency between ETL client host and TD server is about 1.5ms.

But other RDBMS can achieve 20K~60K rows/sec insert rate via DataDirect ODBC driver.

Just some information

N/A

Re: Parallel apply

Lakshmi,

Can you please let me know if you found any solution. I'm facing similar problem :(.

Thanks,

Nag