Search code examples
ado.netteradatasql-insert

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


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.


Solution

  • 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.