CODEWITHSUNDEEP
performancedatatabledataset
Dio Phung
Dio Phung

Reputation: 6282

What is the faster way to access a DataTable/DataRowsCollection?

I have a datatable with 100,000+ DataRow. Which method is faster to access the collection? Is there any faster way to process the rows collection ? Method 1:

var rows= dsDataSet.Tables["dtTableName"].Rows;
int rowCount = dsDataSet.Tables["dtTableName"].Rows.Count;

for (int c = 0; c < rowCount; c++)
{
    var theRow = rows[c];        
    //process the dataRow 
}

Method 2:

for (int c = 0; c < dsDataSet.Tables["dtTableName"].Rows.Count; c++)
{
    var theRow = dsDataSet.Tables["dtTableName"].Rows[c];
    //process the dataRow 
}

Upvotes: 1

Views: 960

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1063569

It is worth noting the most direct way to access cells is via the DataColumn indexer; the data is actually stored in the columns, not the rows (no: really).

So something like:

var table = dataSet.Tables["dtTableName"];

// HERE: fetch the DataColumn of those you need, for example:
var idCol = table.Columns["Id"];
var nameCol = table.Columns["Name"];

// now loop
foreach(DataRow row in table.Rows)
{
    var id = (int)row[idCol];
    var name = (string)row[nameCol];
    // ...
}

However, frankly if you want the best performance, I would start by saying "don't use DataSet / DataTable". That is actually a very complicated model designed to be all kinds of flexible, with change tracking, rule enforcement, etc. If you want fast, I'd use a POCO and something like "dapper", for example:

public class Foo {
    public int Id {get;set;}
    public string Name {get;set;}
}
...
string region = "North";
foreach(var row in conn.Query<Foo>("select * from [Foo] where Region = @region",
         new { region })) // <=== simple but correct parameterisation
{
    // TODO: do something with row.Id and row.Name, which are direct
    // properties of the Foo row returned
    var id = row.Id;
    var name = row.Name;
    // ...
}

or even skip the type via dynamic:

string region = "North";
foreach(var row in conn.Query("select * from [Foo] where Region = @region",
         new { region })) // ^^^ note no <Foo> here
{
    // here "row" is dynamic, but still works; not quite as direct as a
    // POCO object, though
    int id = row.Id;        // <=== note we can't use `var` here or the
    string name = row.Name; // variables would themselves be "dynamic"
    // ...
}

Upvotes: 1

Related Questions