DataTable.Load is slower than IDataAdapter.Fill?

Question

I am using the following code (Variant DataReader):

public DataTable dtFromDataReader(list<String> lstStrings)
{

    OleDBConn_.Open();
    using (OleDbCommand cmd = new OleDbCommand())
    {

        DataTable dt = new DataTable();
        OleDbDataReader reader = null;
        cmd.Connection = OleDBConn_;
        cmd.CommandText = "SELECT * from TableX where SUID=?";

        foreach (String aString in lstStrings)
        {
            cmd.Parameters.AddWithValue("?", aNode.SUID);
            reader = cmd.ExecuteReader();

            if (reader != null)
                dt.Load(reader);
            cmd.Parameters.Clear();
        }
        return dt;
    }
}

and compare it to (Variant DataAdapter):

public DataTable dtFromDataAdapter(list<String> lstStrings)
{
    dt = new DataTable();

    foreach (string aString in lstStrings)
    {
        sOledb_statement = String.Concat("SELECT * FROM TableX where SUID='", aString, "'");
        OleDbDataAdapter oleDbAdapter;
        using (oleDbAdapter = new OleDbDataAdapter(sOledb_statement, OleDBConn_))
        {
            GetOleDbRows = oleDbAdapter.Fill(dt);
        }
    }
}

When i connect to an offline database (microsoft access) my reading time is (~1.5k retrieved items):

DataReader 420 ms
DataAdapter 5613 ms

When reading from oracle server (~30k retrieved items):

DataReader 323845 ms
DataAdapter 204153 ms (several tests, times do not change much)

Even changing the order of the commands (dataadapter before datareader) didn't change much (i thought that there may have been some precaching..).

I thought DataTable.Load should be somewhat faster than DataAdapter.Fill?

And i still believe, even though i see the results, that it should be faster. Where am i losing my time? (There are no unhandled exceptions..)

Where are you doing your timings? One of those methods has the connection Open() call in it, the other doesn't. That could be part of the difference right there. — Ben Lesh, Sep 05 '12 at 13:39
Also , one example uses parameters and the other does not. You're also only reading a single row in one case, but DataAdapter.Fill reads them all. — John Saunders, Sep 05 '12 at 13:42
@blesh The Adapter handles the opening and closing of the connection in the fill method. — LarsTech, Sep 05 '12 at 13:42
@JohnSaunders i just testet it, and dt.load() loads the results as it should - so there are several rows saved aswell. — jaufer.k, Sep 05 '12 at 13:58
Is it possible that it's just SQL saving the query plan for that query and thus the second call is faster? — Ben Lesh, Sep 05 '12 at 14:03

score 2 · Accepted Answer · answered Sep 05 '12 at 13:56

2

Your comparison isn't really an Adapter vs DataReader with the way you have the code setup. You are really comparing the Adapter.Fill vs DataTable.Load methods.

The DataReader would normally be faster on a per-record basis because you would be traversing the records one at a time and can react accordingly when you read each record.

Since you are returning a DataTable in both instances, the Adapter.Fill method would probably be the optimal choice to use. It was designed to do just that.

answered Sep 05 '12 at 13:56

LarsTech

80,625
14
153
225

Both of them are internally calling `protected virtual int Fill(DataTable[] dataTables, IDataReader dataReader, int startRecord, int maxRecords)` in the DataAdapter. I'm not sure this is the answer. – Ben Lesh Sep 05 '12 at 14:02
(I'm not sure it's not the answer either, though) – Ben Lesh Sep 05 '12 at 14:03
Now that you told me what i did there, i see what i did there. Thank you! – jaufer.k Sep 05 '12 at 14:11
I think you're loosing time with adding the parameter and removing it I think if you add the parameter one time and inside your loop just change its value this would be faster and if you noticed you're not using parameters inside your adapter select statement. so this comparison is not correct also you don't have to check if the reader has data or not – Shehab Aug 04 '19 at 12:06

DataTable.Load is slower than IDataAdapter.Fill?

1 Answers1