Why is Teradata so slow to INSERT decimals and CLOBs with .NET?

Question

I have code that's trying to load data in batch through the Teradata .Net provider. I'm using the TdDataAdapter approach as follows:

 using (TdDataAdapter adapter = new TdDataAdapter())
 {
     insertCommand.UpdatedRowSource = UpdateRowSource.None;
     adapter.InsertCommand = insertCommand;
     adapter.UpdateBatchSize = 1000; // Tried numerous values
     adapter.Update(dataTable);
 }

The insert command in this instance is being changed based on the data that's going in. To be clear the code works, the data gets loaded as expected, but it's painfully slow. I know I could use FastLoad but getting that to work from .Net is almost impossible, however what I have noticed is that even though overall it is incredibly slow, adding any decimal or clob fields slows it down by a factor of 30 or so. It's actually quite incredible how much it slows it down. Some numbers:

1000 line CSV file with 20 columns (1 Clob and 1 Decimal) - 37 seconds
1000 line CSV file with 1 column (Decimal Only) - 14 seconds
1000 line CSV file with 1 column (Clob Only) - 28 seconds
1000 line CSV file with 18 columns (No Clobs or Decimals) - 1 second
1000 line CSV file with 19 columns (No Clobs) - 21 seconds
1000 line CSV file with 19 columns (No Decimals) - 30 seconds

The largest clob is 600 characters so it's not exactly huge, the file itself at it's largest is 404K. I know I could use a VARCAHR for the text here, that's not the point of the test. Something is causing enormous slow downs with Clobs and Decimals, has anyone else experienced this? The columns are defined as:

DecimalColumn DECIMAL(10,4),
ClobColumn CLOB

I've tried with with and without identity columns on the table, with and without indexes and primary keys etc. etc. Something is significantly slowing this down. The parameter types for the .Net command are TdType.Decimal and TdType.Clob. Again I know I could use different data types but that's not the point of the code. I need to support these data types.

For LOB types, the driver tries to optimize marshaling the data - deciding when it can "inline" the value with the rest of the data and when it needs to "defer" sending the LOB and wait for the database to elicit values field-by-field. That may preclude some of the "batch" optimization which tries to use iterated requests, and revert to the less-efficient but more flexible multi-statement request approach. — Fred
– Fred, Commented Jun 26, 2024 at 14:47
For TdType.Decimal there is no directly equivalent .NET data type; the data type conversion (which is single-threaded on the client) may be the issue there. Assuming the dataTable is using a floating-point type (float, double, or decimal) you may get better performance binding as TdType.Double and letting the database do the conversion to internal DECIMAL type. — Fred
– Fred, Commented Jun 26, 2024 at 14:54
For the decimal issue I will try populating the data table with the TdDecimal type, however, seems like a huge slowdown for only 1000 rows. — Yndigo Dream
– Yndigo Dream, Commented Jun 26, 2024 at 16:37
Converting the DataTable to TdDecimal as the data type really didn't improve performance at all, however, changing the data type to double, loading into a temporary table and then selecting into a decimal column works and is fast. Clobs are still slow, however as a stop gap, using a VARCHAR (64000) makes it much faster, although not ideal. — Yndigo Dream
– Yndigo Dream, Commented Jun 26, 2024 at 19:02

Yndigo Dream · Accepted Answer · 2024-07-02 14:35:19Z

0

Very little response coming from Teradata or anyone on the why here, so if you find this, the only way I've found to make this less painful is to use temporary Double and VARCHAR columns then select the data into the final table. Not great but the difference in speed is remarkable.

answered Jul 2, 2024 at 14:35

Yndigo Dream

115 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fred Over a year ago

I would just add that the parameter data types don't necessarily need to match the target column data types exactly. Teradata can do implicit conversion from DOUBLE to DECIMAL and from VARCHAR to CLOB - so you may not need temporary staging table.

Collectives™ on Stack Overflow

Why is Teradata so slow to INSERT decimals and CLOBs with .NET?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related