Datareader vs Dataset when populating grid in unbound mode RRS feed

  • Question

  • Hi guys,

    I currently am writing a project which uses largish amounts of data in SQL Server 2000. I am populating a grid with on average 5000 rows in it (this could rise to double or maybe even treble that). The way i am currently doing this is to load the rows into a data reader using stored procedures. The data reader data is then read into a class (record by record). These class objects are then added one by one to an arraylist.

    Once i have this arraylist i then go to the grid (componentone grid) and add the rows one by one and perform functions on them (setting styles, indenting nodes etc).

    I don't want the grid to be bound to the database ideally.

    Question is; does anyone have an opinion on the method i am using to get the data back and poke it in to the grid? i.e. using a data reader to populate an arraylist full of row objects rather than through datasets?

    I have always used data readers so don't have very much knowledge over the comparitive gains of one over the other.

    Speed is a major issue so maybe another question is whether databinding a datatable and then performing operations on it is quicker than my current method.

    Any comments etc. would be great.

    Many thanks


    Monday, October 8, 2007 1:16 PM

All replies

  • From my knowledge. Datasets are faster than datareaders for larger volumes, datareaders are faster for small amounts of data.


    Monday, October 8, 2007 2:11 PM
  • Hi,


    I think your best to use a DataTable rather than an ArrayList in your approach, I'd stick to a DataReader as these are the fastest way to get information from the database (DataReaders optimal performance, DataSet for added functionality & flexibility). The good thing is you can populate a DataTable from a DataReader really simply, the DataTable.Read() method I think fills the table from a reader.


    Have a look at the .NET Data Access Architecture Guide and the Performance Guide on the .NET patterns and practises site. These contain excellent information on what and when to use DataSets vs DataReaders.


    I think your solution is to introduce paging into your application. For example filling a grid with 5000 rows, thats a lot of data for the user to scroll through, perhaps a better solution would be to reduce that to pages containing a set of 1000 records. With the user having the ability to move to the next 1000 rows, or back again.


    Also make sure your only getting the data you need, for example do not used SELECT * statements, maybe only select a small collection of columns that allows the user to make a decision on what additional information they need and then go back to the database for that additional information. for example get the ID and Description (still using paging) then when the user decides that they want to view the record you can go back and get it.


    Caching might help also, perhaps you cache the records at the application, take the hit early and then update the cahce every week, for example, or allow the user to refresh the cache. This will be depend on how often the data in the database changes and how important out of data information is, for example if you got the ID and Description then these might not change as often as other data in the same table, so you can cache this information for longer.


    There is also a Caching guide on patterns and practises also.

    Monday, October 8, 2007 3:28 PM