Dataset vs DataReader - has got me thinking

Gambit_NI

Active member
Joined
Jun 16, 2005
Messages
42
Location
Belfast
Programming Experience
3-5
[Resolved] Dataset vs DataReader - has got me thinking

ive just been reading the following thread http://www.vbdotnetforums.com/showthread.php?t=3855 in the Articles of Interest about the performance difference between Datasets & datareaders

this has got me thinking on how i would go about changing the way we currently access our data as alot of our system do have to return large amounts of records and can seem slow sometime

all our systems are mainly just retrieving data and displaying in Grids, Lists etc. so no need to move back n forth thru the dataset

the way we work at the minute is we pass an empty dataset from the form/webpage to the class, the class then passes the empty dataset into the webservice where a stored proc is called, and the dataset filled. the dataset is then passed back to the form.

my question is, is there a better way of doing this??
 
Last edited:
EEEEP!

A better way? Probably.... the question is, why pass in the empty dataset? Serves no purpose. What you should be doing is invoking the call, which returns a datatable (not a dataset...)... then you can add the passed back datatable to your dataset like this:

myDataSet.Tables.Add (MyPassedBackDataTable)

I've taken to using this process in my own app, and this way I get all my datatables in one dataset. One thing to note, before you can add a datatable, you need to first remove any existing datatable of the same name (myDataSet.Tables.Remove("TableName") ) -- I usualy wrap it in a Try Catch Finaly... with the try part removing the table, the catch clears the error (if the table isn't already there) and the finally adds the table to the dataset.

Also, unless the data needs to reside in memory for lookups and whot not, there's no reason to use a DS/DT.... if all you are going to do is loop through the results and display it, the Reader would be better.... but I don't know if that will work with a webservice.

Tg
 
sorry i didnt mean we pass the empty dataset to the webservice, the webservice method returns a dataset to the class

i cant use datatables either as a webservice wont return these, it will only return datasets.(does anyone know if this will change in vs2005??)

so would it be better performance wise to fill a datable within the webservice, add this to a dataset then return the dataset. or just fill the dataset and return it?

da.Fill(ds_Dataset, "str_Dataset")
return ds_Dataset

or

da.Fill(dt_Datatable)
ds_Dataset.tables.add(dt_DataTable)
return ds_Dataset


Cheers,
Craig
 
Last edited:
The two sections of code you've shown do exactly the same thing. It's just that in the first case the adding of the DataTable to the DataSet is handled within the overloaded Fill function rather than you having to do it yourself. In fact, I bet if you could see the implementation of the first Fill overload it would actually call the second overload something like this:
VB.NET:
Public Function Fill(ByVal ds As DataSet, ByVal tableName As String)
	Dim table As New DataTable(tableName)

	Fill = Me.Fill(table)
	ds.Tables.Add(table)
End Function
The best thing about using a data adapater is that it retrieves the data AND puts it all neatly into a DataTable for you. That's the main reason that most people use one even when they don't have to. Basically, they're too lazy to write the code to deal with the data that a data reader returns. I'm guilty of this myself. I know that a data reader is often much quicker at retrieving data than a data adapter, but I don't know how much time it would then take to put that data into a table yourself, if that's whay you need. If you don't require data in a DataTable, definitely go for the data reader. If you do require a DataTable, the data adapter is definitely easier, and only testing would tell you exactly what the time difference would be in real world usage.
 
well i had a play around with the way i filled the dataset in the webservice and here's what i found

originally i filled the dataset using a dataadapter:-

code..

daResultSet.SelectCommand = cmdSelect
daResultSet.Fill(dsResultSet, "Results")

but decided to test replace this code with that of a datareader to fill the dataset, bit more code involved

code..

dr_DataReader = cmdSelect.ExecuteReader
Dim dt_DataTable AsNew DataTable
Dim dr_DataRow As DataRow
Dim i AsInteger
Dim bln_RunOnce AsBoolean
While dr_DataReader.Read

If bln_RunOnce = FalseThen
'create the table once
For i = 0 To (dr_DataReader.FieldCount - 1)
dt_DataTable.Columns.Add(dr_DataReader.GetName(i))
Next
bln_RunOnce = True
EndIf

i = 0
'create the rows
dr_DataRow = dt_DataTable.NewRow
For i = 0 To (dr_DataReader.FieldCount - 1)
dr_DataRow(dr_DataReader.GetName(i)) = dr_DataReader.GetValue(i)
Next i
'add row
dt_DataTable.Rows.Add(dr_DataRow)

End
While
'close reader
dr_DataReader.Close()
'add table to dataset
dsResultSet.Tables.Add(dt_DataTable)

to test it my command was a stored proc which ran a wildcard search on surname in our T_CLIENT table which has 129900 records in it, returning 39 fields per record

i tested against

S% - which returns 9319 records
C% - which returns 11346 records
M% - which returns 27554 records

and heres the avg times taken by each method to return the records

Wildcard test - DataAdapter Method - DataReader Method

S% - 8s - 6.33s
C% - 10s - 8s
M% - 22s - 16.67s

so it looks like filling the dataset in the webservice via a datareader is a fair bit faster!! and seems to get faster the more records it has to return (which agrees with the graph in Scott Mitchells original article)

HTH

Cheers,
Craig
 
Thanks for the data Craig. That proves that, for those who care about their app's performance, the data reader is worth the little extra time it takes to code the data handling. Your professionalism is appreciated.
 
People may be also interested in the Microsoft Data Access Application Block.... it can return DataSets or DataReaders, with out having to do any additional coding (it's the defference between calling .ExecuteDataSet or .ExecuteDataReader.... or .ExecuteNoQuery for queries that don't return data of any kind.)

Tg
 
yep, its quite mad when your on a global forum and you end up speaking to someone who came from a town 20mile up the road from you lol!

although when you where 4 i was still just a twinkle in my mums eye!!
 
Back
Top