.NET 2 DataTable SLOW

[Guest]

I recently attempted to convert an application from .NET 1.1 to .NET 2.0. I
discovered that the performance of filling the DataTable objects was
ridiculously slower than in the .NET 1.1 incarnation. So I wrote a little
test app to run against both runtimes to see what the real performance
difference was:

class Program
{
const string SQLCommand =
"select LEXPR.LCID as LangLCID, LEXPR.RESID as RESID, LEXPR.EXPR as
Expression, LEXPR.UPDATE_TS as UpdateTS from LEXPR order by RESID, LCID";
const string connString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\Development\\LanguageStuff\\TCSLang.mdb;Persist Security
Info=True";
[STAThread]
static void Main(string[] args)
{
OleDbConnection connection = new OleDbConnection(connString);
connection.Open();
DataTable table = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(SQLCommand, connection);
DateTime start = DateTime.Now;
adapter.Fill(table);
DateTime stop = DateTime.Now;
TimeSpan span = new TimeSpan(stop.Ticks - start.Ticks);
Console.WriteLine(span.ToString());
Console.WriteLine(table.Rows.Count);
Console.Read();
}
}

The output from the program when running against the 1.1 framework is:
2.0468619 seconds for 63,211 records.

The output from the program when running against the 2.0 framework is:
29.8591839 seconds for 63,211 records.

That means it takes 14.587786259541984732824427480916 times longer to load a
DataTable in the 2.0 framework (for this example). What's going on here?

 

[WenYuan Wang]

Hi James

Thanks for you posting.
I have reproduced this issue.
Below is my test code,
--------------------------- reproduce--------------------
public void initDB()
{
string SQLCommand = "select * from table1";
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\test.mdb;Persist Security Info=True";
OleDbConnection connection = new OleDbConnection(connString);
connection.Open();
DataTable table = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(SQLCommand, connection);
DateTime start = DateTime.Now;
adapter.Fill(table);
DateTime stop = DateTime.Now;
TimeSpan span = new TimeSpan(stop.Ticks - start.Ticks);
MessageBox.Show(table.Rows.Count.ToString());
MessageBox.Show(span.ToString());
for (int i = 0; i < 65000; i++)
{
DataRow dr = table.NewRow();
dr[0] = i;
table.Rows.Add(dr);
}
}

public void loadDB()
{
OleDbCommandBuilder dbcb = new OleDbCommandBuilder(adapter);
adapter.Update(table.GetChanges());
string SQLCommand = "select * from table1";
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\test.mdb;Persist Security Info=True";
OleDbConnection connection = new OleDbConnection(connString);
connection.Open();
DataTable table = new DataTable();
OleDbDataAdapter adapter = new OleDbDataAdapter(SQLCommand, connection);
DateTime start = DateTime.Now;
adapter.Fill(table);
DateTime stop = DateTime.Now;
TimeSpan span = new TimeSpan(stop.Ticks - start.Ticks);
MessageBox.Show(table.Rows.Count.ToString());
MessageBox.Show(span.ToString());
}
--------------------------------------------
It takes me 1 second to fill data from .mdb when running against the .Net
framework 1.1,
But it takes me 6 second to fill data from .mdb when running against the
Net framework 2.0.

For this reason, I need some time to do more research. As soon as I get any
information or conclusion on this, I will update here to let you know.

Thanks for your patience.
Wen Yuan
Microsoft Online Community Support
===============================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
===============================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[WenYuan Wang]

Hi James

According to my research, there are some known issues for ADO.NET 2.0 in
which we deal with large numbers of rows.

In my opinion, though, part of the problem is reading 63,211 rows. Any
time we look at that many rows, we should consider either using a
DataReader or changing architecture to return fewer rows.

I will do more research to find the cause of this issue and update here if
there is additional information.

Wen Yuan
Microsoft Online Community Support
===============================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
===============================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[WenYuan Wang]

Hi James

According to my research, I found that NET v1.x DataSet takes a
super-shortcut for handling filling of Empty DataTable, it differs creation
of the primary-key index till the end of Fill and then creates the index
resulting in very fast initial load times (no index maintenance while Fill
is in progress)¨C in this case, it could be under 4 seconds (basically time
taken by adapter to retrieve rows + 1 second to load).

This super-shortcut does not get kicked in when 1 or more rows are already
present ¨C not saying that this matters in your case, am just passing the
information. Now if the incoming rows have duplicates ¨C say the column is
not a primary-key column at the database-end, then the Fill with 0 initial
rows and 1 initial row will behave differently. In the first case the table
will end up having duplicate rows (as index building is differed) while in
the second case the duplicates would get merged into single row. .NET v2.0
does not have this anomaly, it always uses the index and maintains it
during the load phase.

If there is anything I can help with, please feel free to post in the
newsgroup and we will follow up.
Wen Yuan
Microsoft Online Community Support
===============================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
===============================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)