Low Performance When Querying DataTable C # - Plan Coded Ui Test

Question

Low Performance When Querying DataTable C # - Plan Coded Ui Test

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I have a performance problem when querying the numbers of a DataTable-X with a DataTable-Y , I have to check if the DataTable-X number exists in DataTable-Y (and if I find it complements DataTable-X with data from DataTable-Y ), the way I'm doing right spend of 50 minutes .

Number of Rows of each DataTable :

DataTable-X => 38.258 linhas (10 colunas)

DataTable-X => 61.235 linhas (4 colunas)

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

Follow 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";   
}

c#

asked by anonymous 31.12.2018 / 13:05

2 answers

Navbar Bootstrap Problem in python's print

score 2 · Answer 1

You are iterating over these arrays 2342728630 times, converting strings and comparing across all iterations. No wonder it's taking too long.

I think the least you can do is to create an associative array to avoid one for inside another, so you only have 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 give a good relief but still wait a few minutes of processing. Want more performance? Split for multi-core use.

Edit

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

score 1 · Answer 2

Here's another way to approach 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 rows of the two DataTable and then intersects one rows. Finally, use this intersection to go through DataTable-X and get, for each line, DataTable-Y to change values.