29

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];
}
Breeze
  • 2,010
  • 2
  • 32
  • 43
Satinder singh
  • 10,100
  • 16
  • 60
  • 102
  • 3
    why don't you just log out the start time loop over it X times then log out the end time – Daveo Feb 21 '13 at 09:57
  • possible duplicate of [Is datareader quicker than dataset when populating a datatable?](http://stackoverflow.com/questions/334658/is-datareader-quicker-than-dataset-when-populating-a-datatable) – nawfal Mar 29 '13 at 10:28
  • @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. – Richardissimo Feb 23 '18 at 05:15
  • If you're only dealing with one table, perhaps a direct call to DataTable.Load( ) is sufficient. – Brain2000 Sep 04 '18 at 19:50

5 Answers5

28

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;
    }
}
Joe
  • 122,218
  • 32
  • 205
  • 338
  • +1 - because I just wrote up the same thing (for the record, didn't see your answer until after I finished). – Tim M. Feb 21 '13 at 10:39
  • @joe:Thank you for reply, If am not wrong so here in this scenareo `SqlDataAdapter` will be a **good** choice – Satinder singh Feb 21 '13 at 10:41
  • @Joe here the returned dt is null, isn't it? – Behzad Jul 13 '13 at 20:35
  • 1
    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](http://stackoverflow.com/questions/5758526/what-is-the-fastest-way-to-read-data-from-a-dbdatareader/14860927#14860927) may contain a benchmark. Also this msdn article on [DataAdapter and DataReader](https://msdn.microsoft.com/library/ms254931%28v=vs.110%29.aspx) might be usefull. – surfmuggle Nov 04 '16 at 17:01
  • 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](https://1drv.ms/u/s!AtQMh-0jjgb-krdoWHkGfX558gcNig) – Gabriel Espinoza Apr 24 '18 at 19:47
8

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.

Community
  • 1
  • 1
Tim M.
  • 53,671
  • 14
  • 120
  • 163
5

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.

Rob Epstein
  • 1,450
  • 9
  • 11
  • 3
    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`. – Joe Feb 21 '13 at 10:32
  • 3
    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. – Rob Epstein Feb 21 '13 at 10:58
0

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 ..

coder
  • 1,980
  • 3
  • 21
  • 32
  • I also know this theroy part. What i want to know which one is faster in the above code – Satinder singh Feb 21 '13 at 10:13
  • @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 .. – coder Feb 21 '13 at 10:17
  • 3
    "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. – Joe Feb 21 '13 at 10:30
0

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.)

just_a_guest
  • 1