CODEWITHSUNDEEP
c#asp.netperformanceloops
tedski
tedski

Reputation: 2311

Which is more efficient, looping through DataTable or more database calls?

Basically I have a DataTable with a rows containing part numbers and a couple of columns that contain information on those parts.

In order to compare those infos with the data we have in the database, I have determined I have one of two options.

Option 1 - Loop through each row and SELECT the data

void CompareData(DataTable dt) {
    foreach (DataRow entry in dt.Rows) {
        //select that row
        DataRow dbEntry = ExecuteQuery("SELECT * FROM Parts WHERE partno='" + entry["partno"] + "'").Rows[0];
        if (dbEntry["info1"] == entry["info1"]) {
            //do something
        } else {
            //do something
        }
    }
}

Option 2 - SELECT all data at once and compare via loops

void CompareData(DataTable dt, string[] parts) {
    DataTable dbEntries = ExecuteQuery("SELECT * FROM Parts WHERE partno IN('" + String.Join(parts, "','") + "')");
    foreach (DataRow entry in dt.Rows) {
        foreach (DataRow dbEntry in dt.Rows) {
            if (dbEntry["partno"] == entry["partno"]) {
                if (dbEntry["info1"] == entry["info1"]) {
                    //do something
                } else {
                    //do something
                }
            }
        }
    }
}

They both seem pretty inefficient, so I'm not really sure what to do. Would LINQ speed this process up? I've never really used it but just browsing around it looks like something that could help.

Upvotes: 3

Views: 1494

Answers (1)

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Make as few DB calls as possible. You'll be more efficient 99.9% of the time. (general rule to code by)

Upvotes: 4

Related Questions