MBR IT/.NET 247 : Fastest way to fill a DataSet from a SqlDataReader on ASPFriends.com 'aspngescalate' list

Fastest way to fill a DataSet from a SqlDataReader

Messages

Related Types

This message was discovered on ASPFriends.com 'aspngescalate' list.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Dan Green (VIP)

[I've made a couple of posts to the aspngdata mailing list and received no
response. In the meantime I've done some experimentation. This post
restates my question and provides details of my tests.]

I'm looking for the fastest way to build a DataSet schema and then populate
it with a SqlDataReader.

So far I've played with:
* SqlType Typed Accessors (eg. GetSqlInt32)
* .NET Typed Accessors (eg. GetInt32)
* GetValue()
* DataTable.LoadDataRow(object[])

Strangely, it appears that the SqlTyped accessors *slow down* the process of
loading a DataSet.

Here are some excerpts of code and the relative timings produced (from
fastest to slowest):

======

PortableAccessors (2.29 secs)
---
// setting up the schema
col = myTable.Columns.Add("Pkey", typeof(int));
col = myTable.Columns.Add("VarcharCol", typeof(string));
...
// filling the rows
row = myTable.NewRow();
row[0] = aRecord.GetValue(0);
row[1] = aRecord.GetString(1);
...

======

PortableLoadData (2.38 secs)
// setting up the schema
col = myTable.Columns.Add("Pkey", typeof(int));
col = myTable.Columns.Add("VarcharCol", typeof(string));
...
// filling the rows
object[] row = new object[4];
aRecord.GetValues(row);
myTable.LoadDataRow(row, false);

======

TypedPortableAccessors (2.75 secs)
---
// setting up the schema
col = myTable.Columns.Add("Pkey", typeof(int));
col = myTable.Columns.Add("VarcharCol", typeof(string));
...
// filling the rows
row[0] = aRecord.GetInt32(0);
row[1] = aRecord.GetString(1);

TypedSqlAccessors (3.56 secs)
---
// setting up the schema
col = myTable.Columns.Add("Pkey", typeof(SqlInt32));
col = myTable.Columns.Add("VarcharCol", typeof(SqlString));
...
// filling the rows
row[0] = reader.GetSqlInt32(0);
row[1] = reader.GetSqlString(1);

======

NOTES

* SqlConnection was the connection mechanism, SqlServer 2000 was the
database.
* Each example established a primary key as part of setting up the schema
(not shown). BTW, doing this adds a significant performance overhead. I
assume it must have something to do with validation logic that gets
triggered for every row that is added to the table. The overhead is
relatively equally across all the different mechanisms.
* BeginLoadData and EndLoadData were used as brackets around the row filling
process.

======

Again, I'm trying to find the fastest way to build a DataSet schema and
populate it from a SqlDataReader.
The docs suggest using SqlTypedAccessors is the fastest way. My experience
(as noted above) differs.
Is there likely a problem with my experiments or is using GetValue actually
the fastest approach?

Dan Green
[ http://dotnetdan.com -- putting the dan in .net ]

Reply to this message...

Susan Warren

Dan, have you tried just using the Fill method of a SqlDataAdapter? It
uses a SqlDataReader under the covers, and will create the dataset for
you. I'm not much of a dataset whiz, but I know there are some table
mapping options that allows you to remap the data as it is inserted into
the DataSet. Perhaps setting those will let you query the database and
fill the dataset in a single step.

-----Original Message-----
From: Dan Green [mailto:Click here to reveal e-mail address]
Sent: Friday, August 17, 2001 1:06 AM
To: aspngescalate
Subject: [aspngescalate] Fastest way to fill a DataSet from a
SqlDataReader

[I've made a couple of posts to the aspngdata mailing list and received
no
response. In the meantime I've done some experimentation. This post
restates my question and provides details of my tests.]

I'm looking for the fastest way to build a DataSet schema and then
populate
it with a SqlDataReader.

So far I've played with:
* SqlType Typed Accessors (eg. GetSqlInt32)
* .NET Typed Accessors (eg. GetInt32)
* GetValue()
* DataTable.LoadDataRow(object[])

Strangely, it appears that the SqlTyped accessors *slow down* the
process of
loading a DataSet.

Here are some excerpts of code and the relative timings produced (from
fastest to slowest):

=3D=3D=3D=3D=3D=3D

PortableAccessors (2.29 secs)
---
// setting up the schema
col =3D myTable.Columns.Add("Pkey", typeof(int));
col =3D myTable.Columns.Add("VarcharCol", typeof(string));
...
// filling the rows
row =3D myTable.NewRow();
row[0] =3D aRecord.GetValue(0);
row[1] =3D aRecord.GetString(1);
...

=3D=3D=3D=3D=3D=3D

PortableLoadData (2.38 secs)
// setting up the schema
col =3D myTable.Columns.Add("Pkey", typeof(int));
col =3D myTable.Columns.Add("VarcharCol", typeof(string));
...
// filling the rows
object[] row =3D new object[4];
aRecord.GetValues(row);
myTable.LoadDataRow(row, false);

=3D=3D=3D=3D=3D=3D

TypedPortableAccessors (2.75 secs)
---
// setting up the schema
col =3D myTable.Columns.Add("Pkey", typeof(int));
col =3D myTable.Columns.Add("VarcharCol", typeof(string));
...
// filling the rows
row[0] =3D aRecord.GetInt32(0);
row[1] =3D aRecord.GetString(1);

TypedSqlAccessors (3.56 secs)
---
// setting up the schema
col =3D myTable.Columns.Add("Pkey", typeof(SqlInt32));
col =3D myTable.Columns.Add("VarcharCol", typeof(SqlString));
...
// filling the rows
row[0] =3D reader.GetSqlInt32(0);
row[1] =3D reader.GetSqlString(1);

=3D=3D=3D=3D=3D=3D

NOTES

* SqlConnection was the connection mechanism, SqlServer 2000 was the
database.
* Each example established a primary key as part of setting up the
schema
(not shown). BTW, doing this adds a significant performance overhead.
I
assume it must have something to do with validation logic that gets
triggered for every row that is added to the table. The overhead is
relatively equally across all the different mechanisms.
* BeginLoadData and EndLoadData were used as brackets around the row
filling
process.

=3D=3D=3D=3D=3D=3D

Again, I'm trying to find the fastest way to build a DataSet schema and
populate it from a SqlDataReader.
The docs suggest using SqlTypedAccessors is the fastest way. My
experience
(as noted above) differs.
Is there likely a problem with my experiments or is using GetValue
actually
the fastest approach?

Dan Green
[ http://dotnetdan.com -- putting the dan in .net ]

| [aspngescalate] member Click here to reveal e-mail address =3D YOUR ID
| http://www.asplists.com/asplists/aspngescalate.asp =3D JOIN/QUIT

Reply to this message...

Dan Green (VIP)

Hi Susan,

Thanks for the reply.
SqlDataAdapter does indeed work a treat for what it does.
But please trust that I have a good reason to use a SqlDataReader (I have
cause, your honour:-). And I want to make it fly like the wind!
It's perplexing to me that my experiments have shown the SqlServer-specific
technique to be slower than the portable OleDb version.
So I'd kinda really, really, really love a response. :)

Dan Green
[ http://dotnetdan.com -- putting the dan in .net ]

----- Original Message -----
From: "Susan Warren" <Click here to reveal e-mail address>
To: "aspngescalate" <Click here to reveal e-mail address>
Sent: Friday, August 17, 2001 11:55 PM
Subject: [aspngescalate] RE: Fastest way to fill a DataSet from a
SqlDataReader

Dan, have you tried just using the Fill method of a SqlDataAdapter? It
uses a SqlDataReader under the covers, and will create the dataset for
you. I'm not much of a dataset whiz, but I know there are some table
mapping options that allows you to remap the data as it is inserted into
the DataSet. Perhaps setting those will let you query the database and
fill the dataset in a single step.

Reply to this message...

	System.Data.DataSet
	System.Data.DataTable
	System.Data.SqlClient.SqlConnection
	System.Data.SqlClient.SqlDataAdapter
	System.Data.SqlClient.SqlDataReader
	System.Data.SqlTypes.SqlInt32
	System.Data.SqlTypes.SqlString

MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT

	Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
	Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com