Insert Bulk data (millions) into Teradata using VS20013, c#

Database
Enthusiast

Insert Bulk data (millions) into Teradata using VS20013, c#

Hi,

  I am using VS2003 with Teradata to create and fertch data. My requirement is, having millions of records need to insert through ASP.NET c# code.

I am using .NET Data Provider for Teradata v 15.01. My Code is like below in c# code

 string sUser1 = ConfigurationManager.ConnectionStrings["Entities"].ConnectionString;//Working

        TdConnection cons = new TdConnection();

        cons.ConnectionString = sUser1;

        cons.Open();

        for (int i = 0; i < 1000; i++)

        {

          //using (TdConnection cons = new TdConnection())

          //{

          TdCommand cmd = cons.CreateCommand();

          cmd.CommandText = "insert into tableName (name,flag,code,updby,date1,creby,crdate,desc) values ('Fred" + i + "'," + 1 + "," + 1 + "," + 1 + "," + null + "," + 1 + "," + null + ",'desc')";

          cmd.ExecuteNonQuery();

          //}

        }

With this records get added but took so much time.

So can anybody guide me how to increase performance and insert data fast.

Thanks, 

Mahendra

2 REPLIES
Enthusiast

Re: Insert Bulk data (millions) into Teradata using VS20013, c#

Hi,

Can you provide us some more info? Which is the target PI of the table? Can you check the % of uniqueness of that column(s)?

Thanks

Teradata Employee

Re: Insert Bulk data (millions) into Teradata using VS20013, c#

You have few choices to improve the performance:

1- Do not hard code values into the Command-Text; Instead pass the parameters using TdCommand.Parameters collection. There are lots of example on MSDN and in the .NET Data Provider documentation / Samples. The samples are  in the "Program Files (x86)\Teradata\NET Data Provider for Teradata\15.01\samples" directory.

2- Use TdDataAdapter to pass updates in Batches. MSDN has topics on Batch-Update and the .NET Data Provider documentation also has sections/pages on this topic.