CODEWITHSUNDEEP
asp.netperformancedatasetsqldatareader
Chris Burgess
Chris Burgess

Reputation: 5835

DataReader or DataSet when pulling multiple recordsets in ASP.NET

I've got an ASP.NET page that has a bunch of controls that need to be populated (e.g. dropdown lists).

I'd like to make a single trip to the db and bring back multiple recordsets instead of making a round-trip for each control.

I could bring back multiple tables in a DataSet, or I could bring back a DataReader and use '.NextResult' to put each result set into a custom business class.

Will I likely see a big enough performance advantage using the DataReader approach, or should I just use the DataSet approach?

Any examples of how you usually handle this would be appreciated.

Upvotes: 11

Views: 15755

Answers (10)

Shiv Kumar
Shiv Kumar

Reputation: 9799

Seeing that no answer has been marked yet even though there are plently of good answers already, I thought I'd add by two bits as well.

I'd use DataReaders, as they are quite a bit faster (if performance is your thing or you need as much as you can get). Most projects I've worked on have millions of records in each of the tables and performance is a concern.

Some people have said it's not a good idea to send DataReader across layers. I personally don't see this as a problem since a "DbDataReader" is not technically tied (or doesn't have to be) to a database. That is you can create an instance of a DbDataReader without the need for a database.

Why I do it is for the following reasons: Frequently (in a Web Application) you are generating either Html, or Xml or JSON or some other transformation of your data. So why go from a DaraReader to some POCO object only to transform it back to XML or JSON and send it down the wire. This kind of process typically requires 3 transformations and a boot load of object instantiations only to throw them away almost instantly.

In some situations that's fine or can't be helped. My Data layer typically surfaces two methods for each stored procedure I have in the system. One returns a DbDataReader and the other returns a DataSet/DataTable. The methods that return a DataSet/DataTable call the method returning a DbDataReader and then uses either the "Load" method of the DataTable or an adapter to fill the dataset. Sometimes you need DataSets because you probably have to repurpose the data in some way or you need to fire another query and unnless you have MARS enabled you can't have a DbDataReader open and fire another query.

Now there are some issues with using DbDataReader or DataSet/DataTable and that is typically code clarity, compile time checking etc. You can use wrapper classes for your datareader and in fact you can use your DataReaders a IEnumerable with them. Really cool capability. So not only do you get strong typing and code readability you also get IEnumerable!

So a class might look like this.

public sealed class BlogItemDrw : BaseDbDataReaderWrapper
{
    public Int64 ItemId { get { return (Int64)DbDataReader[0]; } }
    public Int64 MemberId { get { return (Int64)DbDataReader[1]; } }
    public String ItemTitle { get { return (String)DbDataReader[2]; } }
    public String ItemDesc { get { if (DbDataReader[3] != DBNull.Value) return (String)DbDataReader[3]; else return default(String); } }
    public DateTime ItemPubdate { get { return (DateTime)DbDataReader[4]; } }
    public Int32 ItemCommentCnt { get { return (Int32)DbDataReader[5]; } }
    public Boolean ItemAllowComment { get { return (Boolean)DbDataReader[6]; } }
    public BlogItemDrw()
      :base()
    {
    }

    public BlogItemDrw(DbDataReader dbDataReader)
      :base(dbDataReader)
    {
    }
}

DataReader Wrapper

I have a blog post (link above) that goes into a lot more detail and I'll be making a source code generator for these and other DataAccess layer code.

You can use the same technique for DataTables (the code generator produces the code) so you can treat them as strongly typed DataTable without the overhead of what VS.NET provides out of the box.

Keep in mind that there is only one instance of the wrapper class. So you're not creating hundreds of instances of a class only to throw it away.

Upvotes: 4

Kenny Evitt
Kenny Evitt

Reputation: 9801

Irrespective of whether you're fetching a single result-set or multiple result-sets, the consensus seems to be to use a DataReader instead of a DataSet.

In regards to whether you should ever bother with multiple result-sets, the wisdom is that you shouldn't, but I can conceive of a reasonable class of exceptions to that rule: (tightly) related result-sets. You certainly don't want to add a query to return the same set of choices for a drop-down list that's repeated on hundreds or even dozens of pages in your app, but several sets narrowly-used may be sensibly combined. As an example, I'm currently creating a page to display several sets of 'discrepancies' for a batch of ETL data. None of those queries are likely to be used elsewhere, so it would be convenient to encapsulate them as a single 'get discrepancies' sproc. On the other hand, the better performance of doing so may be insignificant compared to working-around the natural one-sproc-one-result-set architecture of your ORM or hand-rolled data-access code.

Upvotes: 2

Wahid Bitar
Wahid Bitar

Reputation: 14084

  1. If you have more than 1000 record to bring from your DataBase.
  2. If you are not very interested with custom storing and custom paging "For GridView"
  3. If your server have a memory stress.
  4. If there is no problem to connect to your DataBase every time that page called.

Then i think the better is to use DataReader.

else

  1. If you have less than 1000 record to bring from your DataBase.
  2. If you are interested with storing and paging "For GridView"
  3. If your server haven't a memory stress.
  4. If you want to connect to your DataBase just one time and get the benefits of Caching.

Then i think the better is to use DataSet.

I hop that i'm right.

Upvotes: 7

Daniel Auger
Daniel Auger

Reputation: 12611

Map the DataReader to intermediate objects and then bind your controls using those objects. It can be ok to use DataSets in certain circumstances, but those are few and far between when you have strong reasons for "just getting data". Whatever you do, don't pass a DataReader to your controls to bind off of (not that you said that you were considering that).

My personal preference would be to use an ORM, but if you are going to hand roll your data access, by all means I think you should prefer mapping DataReaders to objects over using DataSets. Using the .NextResult as a way to limit yourself from hitting the database multiple times is a double edged sword however so choose wisely. You will find yourself repeating yourself if you try to create procs that always grab exactly what you need using only one call to the database. If your application is only a few pages, it is probably OK, but things can get out of control quickly. Personally I'd rather have one proc per object type and then hit the database multiple times (once for each object type) in order to maximize maintainability. This is where an ORM shines because a good one will generate Sql that will get you exactly what you want with one call in most cases.

Upvotes: 3

David Robbins
David Robbins

Reputation: 10046

If your stored proc returns multiple sets, use the DataReader.NextResult to advance to the next chunk of data. This way you can get all your data, load it to your objects, and close the reader as soon as possible. This will be the fastest method to get your data.

Upvotes: 4

Dillie-O
Dillie-O

Reputation: 29725

Take a look into the TableAdapters that are available with .NET 2.0 and up. What they do is give you the strength of a strongly-typed DataTable and allow you to map a Fill method to it that will use a DataReader to load it up. Your fill method can be existing stored procedures, your own AdHoc SQL, or even let the wizard generate the AdHod or Stored Procedure for you.

You can find this by starting up a new XSD DataSet object within your project. For tables that are used for more than just lookup, you can also map insert/update/delete methods to the TableAdapter.

Upvotes: 0

wprl
wprl

Reputation: 25407

In almost every situation DataReaders are the best solution for reading from a database. DataReaders are faster and require less memory than DataTables or DataSets.

Also, DataSets can often lead to situations in which the OO model is broken. It's not very object oriented to be passing around relational data/schemata instead of objects that know how to manipulate that data.

So, for extensibility, scalability, modularity, and performance reasons, always use DataReaders if you consider yourself a Real Programmer™ ;)

Check the links for facts and discussion about the two in practice and theory.

Upvotes: 2

Vijesh VP
Vijesh VP

Reputation: 4540

If you are not interested in updating or deleting the records you fetched from database, I would suggest using DataReader. Basically DataSet internally uses multiple Datareaders, so DataReader should give you good performance advantage.

Upvotes: 2

Carlton Jenke
Carlton Jenke

Reputation: 3025

Always put your data into classes defined for the specific usage. Don't pass DataSets or DataReaders around.

Upvotes: 4

Mitchel Sellers
Mitchel Sellers

Reputation: 63126

I have gone to a method that uses DataReaders for all calls, I have noticed a marked performance impovement, especially in cases when I am loading drop down lists, and other simple items like that.

Personally with multiple drop downs, I typically go to pullling individual chunks of data to get it, rather than say a stored procedure that returns 5 result sets.

Upvotes: 1

Related Questions