Low Performance when viewing Datatable C# - Plan Coded Ui Test

Question

I’m having a little trouble checking the numbers of a DataTable-X with a DataTable-Y, I have to check if the number of DataTable-X exists in the DataTable-Y (and if the DataTable-X with the data of DataTable-Y), the way I’m doing spending right 50 minutes.

Number of Lines of each Datatable:

DataTable-X => 38.258 linhas (10 colunas)

DataTable-X => 61.235 linhas (4 colunas)

In practice, the loop occurs DataTable-X x DataTable-X times, that’s a lot, but 50 minutes I think too much time.

Follows the code:

for (int i = 0; i < dTx.Rows.Count; i++)
{    
    bool ganbis1 = false;
    foreach (DataRow oLinha in dTy.Rows)
    {
        if (oLinha[0] != "")
        {
            if (Convert.ToInt64(oLinha[0].ToString()) ==             
                 Convert.ToInt64(dTx.Rows[i][1].ToString().Replace(" ","")))
            {
                // dTx = "Coluna1", "Coluna2", "Coluna3", "Coluna4",
                // "Coluna5", "Coluna6", "Coluna7", "Coluna8", "Coluna9", "Coluna10"
                
                // dTy = "Coluna1", "Coluna2", "Coluna3","Coluna4"
                // Preenche coluna 7
                
                if ((Convert.ToString(oLinha[1]) == "") || 
                    (Convert.ToString(oLinha[1]) == "#N/A"))
                {
                    dTx.Rows[i][7] = "#n/D";
                }
                else
                {
                    dTx.Rows[i][7] = Convert.ToString(oLinha[1]);    
                }
                // Preenche coluna 8
                if ((Convert.ToString(oLinha[2]) == "") || (Convert.ToString(oLinha[2]) == "#N/A"))
                {
                    dTx.Rows[i][8] = "#n/D";
                }
                else
                {
                    dTx.Rows[i][8] = Convert.ToString(oLinha[2]);
                }
                // Preenche coluna 9
                if ((Convert.ToString(oLinha[3]) == "") || (Convert.ToString(oLinha[3]) == "#N/A"))
                {
                    dTx.Rows[i][9] = "#n/D";
                }
                else
                {
                    dTx.Rows[i][9] = Convert.ToString(oLinha[3]);
                }
                // Preenche coluna 10
                if ((Convert.ToString(oLinha[4]) == "") || (Convert.ToString(oLinha[4]) == "#N/A"))
                {
                    dTx.Rows[i][10] = "#n/D";
                }
                else
                {
                    dTx.Rows[i][10] = Convert.ToString(oLinha[4]);
                }            
                ganbis1 = true;
            }
        } 
    }   
}

if ((ganbis1 == false) && (i != dTx.Rows.Count-1))
{
    // Quando não for encontrado preencher com #n/D
    dTx.Rows[i][7] = "#n/D";
    dTx.Rows[i][8] = "#n/D";
    dTx.Rows[i][9] = "#n/D";
    dTx.Rows[i][10] = "#n/D";    
}

Answer 1

You’re iterating over these 2342728630 arrays, converting strings and comparing in all iterations. No wonder it’s taking so long.

I believe that the least you can do is create an associative array to avoid one being inside another is, so you only need to go through each array once, reducing the iterations to 99493.

Dictionary<long, DataRow> dic = new Dictionary<long, DataRow>();

foreach (DataRow oLinhaX in dTx.Rows) { 
    dic.Add(Convert.ToInt64(oLinhaX[1].ToString().Replace(" ",""))), oLinhaX);
}

foreach (DataRow oLinhaY in dTy.Rows) {
    if (oLinhaY[0] == "") continue;

    long oLinhaYInt = Convert.ToInt64(oLinhaY[0].ToString());

    if (dic.ContainsKey(oLinhaYInt)) {
        DataRow oLinhaX = dic[oLinhaYInt];
        //seu código aqui
    }
}

This should be a good relief, but still wait a few minutes of processing. Want more performance? Leave for multi-core use.

Edit

It was not mentioned in the question, but this solution only works if the relationship between the tables is one-to-one.

Answer 2

Here’s another way to address the problem:

var rowsX = dtX.Rows.Cast<DataRow>().Select(r => r[1].ToString().Trim());
var rowsY = dtY.Rows.Cast<DataRow>().Where(r => r[0].ToString() != string.Empty).Select(r => r[0].ToString());

var rows = rowsX.Intersect(rowsY);
var dataRowsX = dtX.Rows.Cast<DataRow>().Where(r => rows.Contains(r[1]));

foreach (var row in dataRowsX)
{
    var dataRowY = dtY.Rows.Cast<DataRow>().FirstOrDefault(r => r[0].ToString() == row[1].ToString());

    // código...
}

Basically compares the lines of the two DataTable and then intersect in one.
Finally, it uses this intersection to traverse the DataTable-X and obtaining, for each line, the DataTable-Y for changing values.