Datatable Unpivot is very slow. Any Alternatives?

Question

I have an excel worksheet which has about 200 rows and about 200+ columns. I need to unpivot this excel sheet and insert it into a sql server table. I am using a third party excel api which converts excel worksheet to a ADO .Net Datatable.

Original DataTable A enter image description here

For Unpivot opertation I am traversing through the original Datatable Rows and Columns and assigning the values to another DataTable which has been defined as per the

DataTable B UnPivoted enter image description here

After getting the new DataTable populated I would use SQlBulkCopy() on the datatable and save all the records to the SQLServer Table.

The code I am using for the Un-Pivot operation

//excelExport is the DataTable which stores the entire excel workSheet

DataTable newDatatable = new DataTable();   //For Un-Pivoting
newDatatable .Columns.Add(new DataColumn("EconomyID"));
newDatatable .Columns.Add(new DataColumn("SystemLanguage"));
newDatatable .Columns.Add(new DataColumn("VariableName"));
newDatatable .Columns.Add(new DataColumn("VariableValue"));
foreach (DataRow dr in excelExport.Rows)     //excelExport is the original datatable      
{
int colCount = 0;
       foreach (DataColumn dc in excelExport.Columns)
       {                    
          if (colCount >= 2)
          {
           DataRow dr2 = newDatatable.NewRow();                                           
           dr2["Economy"] = dr[1].ToString();
           dr2["SystemLanguageID"] = dr[2].ToString();
           dr2["VariableName"] = dc.ColumnName;
           dr2["VariableValue"] = dr[dc].ToString();
           newDatatable.Rows.Add(dr2);
          }
         colCount++;
       }
 }

Now the problem is since my original Datatable A has 200+ columns (and 200 rows). traversing each row and assigning the values to another newDatatable takes 10seconds. So the entire operation take 200 rows * 10 seconds ... basically forever (depending on the columns in excel/datatable); just for the creation of the newDatatable. Is there more efficient way of handling this Un-pivot operation. I know we have SSIS packages which have been built for excel to sqlserver import operation but our requirement is that the client should be able to upload the excel into the system and the process has to be realtime. Any suggestions?

If you wanted to do it on the excel side with VBA, [this SO answer](http://stackoverflow.com/a/10922351/293078) would be helpful. — Doug Glancy, Oct 18 '13 at 17:25

score 0 · Answer 1 · answered Oct 18 '13 at 15:36

With 200 columns the index lookup by column may be a culprit. You could change the lookup to go by column number instead of string name:

   foreach (DataColumn dc in excelExport.Columns)
   {                    
      if (colCount >= 2)
      {
       DataRow dr2 = newDatatable.NewRow();                                           
       dr2[0] = dr[1].ToString();
       dr2[1] = dr[2].ToString();
       dr2[2] = dc.ColumnName;
       dr2[3] = dr[colCount].ToString();
       newDatatable.Rows.Add(dr2);
      }
     colCount++;
   }

Although this is just a guess. I would invest in a good profiling tool to measure where the process is slow and fix the slowest parts first.

Gert Arnold · Answer 2 · 2013-10-18T20:02:45.330

Constructing a new DataTable is more expensive than using a cheap structure, like Tuples.

var tuples = new List<Tuple<string, string, string, object>>();
foreach (DataRow dr in excelExport.Rows)
{
    int colCount = 0;
    foreach (DataColumn dc in excelExport.Columns)
    {                    
        if (colCount >= 2)
        {
            tuples.Add(Tuple.Create(dr[1],
                                    dr[2],
                                    dc.ColumnName,
                                    (object)dr[colCount])
                                   );
        }
        colCount++;
    }
}

You can also skip the ToString() calls, as Tuple.Create will use type inference to create the right tuple. Maybe the last item can have an explicit type (not object) if all values happen to have the same data type, else you need the (object) cast.

Datatable Unpivot is very slow. Any Alternatives?

2 Answers2