SqlDataReader vs SqlDataAdapter: which one has the better performance for returning a DataTable?

Question

I want to know which one has the better performance for returning a DataTable. Here for SqlDataReader I use DataTable.Load(dr)

Using SqlDataReader:

public static DataTable populateUsingDataReader(string myQuery)
{
    DataTable dt = new DataTable();
    using (SqlConnection con = new SqlConnection(constring))
    {
        SqlCommand cmd = new SqlCommand(myQuery, con);
        con.Open();
        SqlDataReader dr = null;
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        if (dr.HasRows)
        {
            dt.Load(dr);
        }
        return dt;
    }
}

using SqlDataAdapter:

public DataTable populateUsingDataAdapter(string myQuery)
{
    SqlDataAdapter dap = new SqlDataAdapter(myQuery,cn);
    DataSet ds = new DataSet();
    dap.Fill(ds);
    return ds.Tables[0];
}

Hydrocarbon

why don't you just log out the start time loop over it X times then log out the end time – Factitive

possible duplicate of Is datareader quicker than dataset when populating a datatable? – Banquette

@Satinder Singh Not related to the question, but: in the DataReader version, don't forget to put the SqlCommand and DataReader variables into using blocks, since they are IDisposable. – Pythagoras

If you're only dealing with one table, perhaps a direct call to DataTable.Load( ) is sufficient. – Inmost

Answer 1

The difference will be negligible, so it's probably better to use the more concise version: SqlDataAdapter.Fill.

SqlDataReader.Fill creates an internal class LoadAdapter (derived from DataAdapter) internally, and calls its Fill method: performance will be very similar to SqlDataAdapter.Fill(DataTable).

There will be some small differences in initialization / validation of arguments, but as the number of rows increases, this will become less and less significant.

Note also that your second sample should be modified to be comparable with the first:

public DataTable populateUsingDataAdapter(string myQuery)
{
    using (SqlConnection con = new SqlConnection(constring))
    {
        SqlDataAdapter dap = new SqlDataAdapter(myQuery,con);
        DataTable dt = new DataTable();
        dap.Fill(dt);
        return dt;
    }
}

Predicament

+1 - because I just wrote up the same thing (for the record, didn't see your answer until after I finished). – Scipio

@joe:Thank you for reply, If am not wrong so here in this scenareo SqlDataAdapter will be a good choice – Hydrocarbon

@Predicament here the returned dt is null, isn't it? – Twain

Is the difference between sqlDataReader and sqlDataAdapter really neglible? Do you have any benchmarks that shows the difference? As pointed out below this reply fastest-way-to-read-data-from-a-dbdatareader may contain a benchmark. Also this msdn article on DataAdapter and DataReader might be usefull. – Astrobiology

I know this thread is old, just wanted to add my grain of sand to the solution: As everything, I recommend testing your use cases, I leave a linqpad script to test, for me DataAdapter is better because it lets you map the primary keys into the DataTable. Download LinqPad Script – Balthasar

Answer 2

This question, and more specifically, this answer suggests that your second example is faster. It is certainly not an exhaustive benchmark but it is an interesting test.

Reflecting the source code of DataTable shows that calling DataTable.Load() actually creates an internal DataAdapter subclass called LoadAdapter and calls the Fill() method of DataAdapter. SqlDataAdapter does the bulk of its loading work in the exact same place.

More importantly, I would tend to favor the second example for readability. Neither example compares to the fast access provided by direct use of the DataReader, so I would opt for the cleaner code.

Scipio

Answer 3

SqlDataReader has historically been significantly faster than SqlDataAdapter. Improvements may have been made in .NET 4.5, but I doubt it has improved enough to outpace the performance of the DataReader.

Tripitaka

Not true. Processing data directly from a SqlDataReader may be faster then loading the whole result set into a DataTable. But that's comparing apples and oranges. Both have similar performance for filling a DataTable. – Predicament

You are correct. I was assuming using a Reader to hydrate a custom class. Not to fill a DataTable. If the result has to be a DataTable then the performance will be equally poor regardless of whether an Adapter or Reader is used. – Tripitaka

Answer 4

SqlDataReader will be faster than SQlDataAdapter because it works in a connected state which means the first result is returned from query as soon as its available ..

Sande

I also know this theroy part. What i want to know which one is faster in the above code – Hydrocarbon

@Satindersingh sorry i thought u were asking the difference..SqlDatareader will be faster to use because it works in a connected state which means the first result is returned from query as soon as its available .. – Sande

"SqlDataReader will be faster than SQlDataAdapter because it works in a connected state" - that's not true: in both cases a DataTable is completely filled before the result is returned to the caller. – Predicament

Answer 5

In addition to the selected solution, I would like to add that:

Using the DataReader, you don´t need to know which type of DbConnection you have.

All you need is an instance which implements IDbConnection, with that you can use "connection.CreateCommand" and then "dbCommand.ExecuteReader" and then dataTable.Load.

But when you use DataAdapter you will need to know which connection is used (i.e. oracle, sqlserver, etc.)

(It´s not relevant for the thread starter, but I landed here using g**gle while looking for this topic.)

Watchman

This is a comment to the answer of Joe. – Fairtrade