Is a datareader faster ?
There are several ways to get to your data in a .net application. You can use a data-adapter to fill an XML-dataset or you can directly read your data using a datareader. A lot of code sample use datareaders, the main reason presented is the (assumed) performance. The main advantage of an XML-dataset is that it can be strongly typed to describe the data you are dealing with. So there seems to be a payoff between performance and ease (and quality) of coding. In the Apress database forum I found a very interesting thread on making datareader code easier to use. Working hard on an Apress title myself, this lead me to some investigations. My main question was, preferring typed datasets, if using a datareader is really that much faster.
As a first test I built a Winform app. On creation the form opens a sqlconnection. On the form is a data-adapter, in its select statement it selects the title field from the titles table in SQl-server's pubs database. From the data-adapter I generated a DataSetTitles. This dataset is not in the designer, I have to create an instance to use it. Two different methods read the data to fill a listbox on the form.
Using a typed dataset :
private void fillFromDataSet()
{
DataSetTitles ds = new DataSetTitles();
sqlDataAdapter1.Fill(ds.titles);
for (int i = 0; i < ds.titles.Count; i++)
listBox1.Items.Add(ds.titles[i].title);
}
And using a datareader :
private void fillFromDataReader()
{
System.Data.SqlClient.SqlDataReader dr;
dr = sqlDataAdapter1.SelectCommand.ExecuteReader();
while (dr.Read())
listBox1.Items.Add(dr.GetString(dr.GetOrdinal("title")));
dr.Close();
}
The datareader uses the selectcommand of the data-adapter. I learned the construction dr.GetString(dr.GetOrdinal("title")) from Pete Wright in the Apress forum. (Read these, there are a lot of interesting thing happening at Apress) The statement is nice but the fact that you have to identify the fieldname in a string always gives me an itch. My mantra is "Allways used strong typed objects". I prefer ds.titles[i].title, but that cannot be done using a reader.
Next I wrote some code to compare the speed of the two different ways to get to the data :
private void button1_Click(object sender, System.EventArgs e)
{
listBox1.Items.Clear();
DateTime start = DateTime.Now;
for (int i= 0; i < numericUpDown1.Value; ++i)
{
if (checkBox1.Checked)
fillFromDataReader();
else
fillFromDataSet();
}
DateTime stop = DateTime.Now;
int elapsed = (int) (stop.Ticks - start.Ticks);
label1.Text = elapsed.ToString();
}
In the actual code there are some more things to do statistics, but this will give you an idea.
The results :
- The time needed to get the data varied quite a lot in both approaches
- The first fill is always slower
- Using a data-reader in this situation is on the average only 10% faster
As a second test I built a web application. This time I read over 300 rows of 8 columns. Quite different data. The data read in is used to populate a datagrid.
Using a datareader:
System.Data.SqlClient.SqlDataReader dr;
dr = sqlDataAdapter1.SelectCommand.ExecuteReader();
DataGrid1.DataSource = dr;
DataGrid1.DataBind();
dr.Close();
And using a dataset
DataSetMOC ds =
new DataSetMOC();
sqlDataAdapter1.Fill(ds.ProjektChecklistItem);
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
The result were quite comparable to the winform situation. Again a lot of variation and again the datareader performing somewhere about 10% better.
What good are datareaders ?
Based on this sloppy test (please correct me if I did something wrong) my conclusion would be that it does not really pay off to use datareaders. Some fragments of your code will run a little faster. Browsing on the net you will find resources claiming a greater speed gain than I describe here, but (some of) these resources compare an oleDBdatAdapater with a sqlDataReader. Here I compared a sqlDataReader with a sqlDataAdapter.
In fact the dataAdapter is using a datareader internally when it is executing the Fill method. Using a data-adapter to fill typed datasets in your own code instead of a datareader gives you the possibility to write clearer and better code. GetOrdinal("tittle") is a bug you will not see until your app is running, ds.titles[i].tittle is something the compiler will see immediately. This is a gain in speed and quality of development I am quite willing to pay the (apparently) small performance overhead for.
Note : There are two reasons why this story does not apply to the compact framework. Besides resources being more expensive there, the compact framework has no knowledge of typed datasets at all.