Loading data from Access table to datatable very slow!

[VMI]

For some reason, the process of retrieving data (about 20 records) from an
Access table that has 400K records to a dataTable is taking over 3 mins. to
complete. Below is my code to connect to the DB and query the table. The
table "audit" primary key is "Line".
Another weird thing (but I guess that's another post) is that, while it's
doing the dataset Fill, my PC is slowed done substantially. But I don't
know why that would happen since the process is not consuming much memory or
CPU. This must be an Access flaw because it also happens if I'm running
intensive queries from within Access.
/**** Code ****/
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFileName;
//sFileName is mdb file
_connection = new OleDbConnection(strDSN);
_oleCommand = new OleDbCommand();
_oleCommand.Connection = _connection;
_oleCommand.Connection.Open();

string sQuery = "select top 20 * from audit where Line > 0 order by Line
asc";
OleDbDataAdapter adapter = new OleDbDataAdapter(sQuery, _connection);
DataSet DS_Audit = new DataSet();
adapter.Fill(DS_Audit, "audit"); //this is taking 3 minutes
return DS_Audit.Tables["audit"];

The reason we're using Access is because every client must have their own
personal DB to store the data. If we use server DBs such as MSDE or MySQL,
we will need to install the DB in every PC (we can't install it in 1 PC
because we can't assume that the clients are in a network). With Access we
can create the mdb from our application. Someone suggested the embedded
version of MySQL but every client may have several different files (MDBs),
so I'm not sure if that would complicate things.

Thanks for any help.

 

[Mel Weaver]

Are you sure there is an index on Line when you created the database in
Access?

 

[VM]

Yes. It wasn't there when I created it, but I added it afterwards. It's of
type "Number".
Do you have any idea what it could be?

Thanks.

Are you sure there is an index on Line when you created the database in
Access?

For some reason, the process of retrieving data (about 20 records) from
an Access table that has 400K records to a dataTable is taking over 3
mins. to complete. Below is my code to connect to the DB and query the
table. The table "audit" primary key is "Line".
Another weird thing (but I guess that's another post) is that, while it's
doing the dataset Fill, my PC is slowed done substantially. But I don't
know why that would happen since the process is not consuming much memory
or CPU. This must be an Access flaw because it also happens if I'm
running intensive queries from within Access.
/**** Code ****/
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
sFileName; //sFileName is mdb file
_connection = new OleDbConnection(strDSN);
_oleCommand = new OleDbCommand();
_oleCommand.Connection = _connection;
_oleCommand.Connection.Open();

string sQuery = "select top 20 * from audit where Line > 0 order by Line
asc";
OleDbDataAdapter adapter = new OleDbDataAdapter(sQuery, _connection);
DataSet DS_Audit = new DataSet();
adapter.Fill(DS_Audit, "audit"); //this is taking 3 minutes
return DS_Audit.Tables["audit"];

The reason we're using Access is because every client must have their own
personal DB to store the data. If we use server DBs such as MSDE or
MySQL, we will need to install the DB in every PC (we can't install it in
1 PC because we can't assume that the clients are in a network). With
Access we can create the mdb from our application. Someone suggested the
embedded version of MySQL but every client may have several different
files (MDBs), so I'm not sure if that would complicate things.

Thanks for any help.

 

[Miha Markic [MVP C#]]

Did you try the same query from within access itself - just to rule out the
engine bottleneck...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Yes. It wasn't there when I created it, but I added it afterwards. It's of
type "Number".
Do you have any idea what it could be?

Thanks.

Are you sure there is an index on Line when you created the database in
Access?

For some reason, the process of retrieving data (about 20 records)
from
an Access table that has 400K records to a dataTable is taking over 3
mins. to complete. Below is my code to connect to the DB and query the
table. The table "audit" primary key is "Line".
Another weird thing (but I guess that's another post) is that, while it's
doing the dataset Fill, my PC is slowed done substantially. But I
don't
know why that would happen since the process is not consuming much memory
or CPU. This must be an Access flaw because it also happens if I'm
running intensive queries from within Access.
/**** Code ****/
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
sFileName; //sFileName is mdb file
_connection = new OleDbConnection(strDSN);
_oleCommand = new OleDbCommand();
_oleCommand.Connection = _connection;
_oleCommand.Connection.Open();

string sQuery = "select top 20 * from audit where Line > 0 order by
Line
asc";
OleDbDataAdapter adapter = new OleDbDataAdapter(sQuery, _connection);
DataSet DS_Audit = new DataSet();
adapter.Fill(DS_Audit, "audit"); //this is taking 3 minutes
return DS_Audit.Tables["audit"];

The reason we're using Access is because every client must have their own
personal DB to store the data. If we use server DBs such as MSDE or
MySQL, we will need to install the DB in every PC (we can't install it in
1 PC because we can't assume that the clients are in a network). With
Access we can create the mdb from our application. Someone suggested the
embedded version of MySQL but every client may have several different
files (MDBs), so I'm not sure if that would complicate things.

Thanks for any help.

 

[VMI]

From Access, the query takes 19 seconds to execute (see results in table).
This same query, through the adapter.Fill() takes 1 min 54 secs and the PC
gets really slow to the point that I can't switch between Windows programs
with Alt-Tab.

Did you try the same query from within access itself - just to rule out
the engine bottleneck...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Yes. It wasn't there when I created it, but I added it afterwards. It's
of
type "Number".
Do you have any idea what it could be?

Thanks.

Are you sure there is an index on Line when you created the database in
Access?




For some reason, the process of retrieving data (about 20 records)
from
an Access table that has 400K records to a dataTable is taking over 3
mins. to complete. Below is my code to connect to the DB and query the
table. The table "audit" primary key is "Line".
Another weird thing (but I guess that's another post) is that, while it's
doing the dataset Fill, my PC is slowed done substantially. But I
don't
know why that would happen since the process is not consuming much memory
or CPU. This must be an Access flaw because it also happens if I'm
running intensive queries from within Access.
/**** Code ****/
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
sFileName; //sFileName is mdb file
_connection = new OleDbConnection(strDSN);
_oleCommand = new OleDbCommand();
_oleCommand.Connection = _connection;
_oleCommand.Connection.Open();

string sQuery = "select top 20 * from audit where Line > 0 order by
Line
asc";
OleDbDataAdapter adapter = new OleDbDataAdapter(sQuery, _connection);
DataSet DS_Audit = new DataSet();
adapter.Fill(DS_Audit, "audit"); //this is taking 3 minutes
return DS_Audit.Tables["audit"];

The reason we're using Access is because every client must have their own
personal DB to store the data. If we use server DBs such as MSDE or
MySQL, we will need to install the DB in every PC (we can't install it in
1 PC because we can't assume that the clients are in a network). With
Access we can create the mdb from our application. Someone suggested the
embedded version of MySQL but every client may have several different
files (MDBs), so I'm not sure if that would complicate things.

Thanks for any help.

 

[VMI]

Also, even after the dataset's filled up, Windows remains very slow, as if
the dataset's using up tons of memory. But when I check the task manager,
my application is only using up 20MB of memory (more or less what iexplore
uses).

Did you try the same query from within access itself - just to rule out
the engine bottleneck...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Yes. It wasn't there when I created it, but I added it afterwards. It's
of
type "Number".
Do you have any idea what it could be?

Thanks.

Are you sure there is an index on Line when you created the database in
Access?




For some reason, the process of retrieving data (about 20 records)
from
an Access table that has 400K records to a dataTable is taking over 3
mins. to complete. Below is my code to connect to the DB and query the
table. The table "audit" primary key is "Line".
Another weird thing (but I guess that's another post) is that, while it's
doing the dataset Fill, my PC is slowed done substantially. But I
don't
know why that would happen since the process is not consuming much memory
or CPU. This must be an Access flaw because it also happens if I'm
running intensive queries from within Access.
/**** Code ****/
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
sFileName; //sFileName is mdb file
_connection = new OleDbConnection(strDSN);
_oleCommand = new OleDbCommand();
_oleCommand.Connection = _connection;
_oleCommand.Connection.Open();

string sQuery = "select top 20 * from audit where Line > 0 order by
Line
asc";
OleDbDataAdapter adapter = new OleDbDataAdapter(sQuery, _connection);
DataSet DS_Audit = new DataSet();
adapter.Fill(DS_Audit, "audit"); //this is taking 3 minutes
return DS_Audit.Tables["audit"];

The reason we're using Access is because every client must have their own
personal DB to store the data. If we use server DBs such as MSDE or
MySQL, we will need to install the DB in every PC (we can't install it in
1 PC because we can't assume that the clients are in a network). With
Access we can create the mdb from our application. Someone suggested the
embedded version of MySQL but every client may have several different
files (MDBs), so I'm not sure if that would complicate things.

Thanks for any help.

 

[Miha Markic [MVP C#]]

Access "cheats" on results.
Try scrolling to the last record.
Did you look at task manager on how much memory is available?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

From Access, the query takes 19 seconds to execute (see results in table).
This same query, through the adapter.Fill() takes 1 min 54 secs and the
PC gets really slow to the point that I can't switch between Windows
programs with Alt-Tab.

Did you try the same query from within access itself - just to rule out
the engine bottleneck...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Yes. It wasn't there when I created it, but I added it afterwards. It's
of
type "Number".
Do you have any idea what it could be?

Thanks.

Are you sure there is an index on Line when you created the database in
Access?




For some reason, the process of retrieving data (about 20 records)
from
an Access table that has 400K records to a dataTable is taking over 3
mins. to complete. Below is my code to connect to the DB and query
the
table. The table "audit" primary key is "Line".
Another weird thing (but I guess that's another post) is that, while
it's
doing the dataset Fill, my PC is slowed done substantially. But I
don't
know why that would happen since the process is not consuming much
memory
or CPU. This must be an Access flaw because it also happens if I'm
running intensive queries from within Access.
/**** Code ****/
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
sFileName; //sFileName is mdb file
_connection = new OleDbConnection(strDSN);
_oleCommand = new OleDbCommand();
_oleCommand.Connection = _connection;
_oleCommand.Connection.Open();

string sQuery = "select top 20 * from audit where Line > 0 order by
Line
asc";
OleDbDataAdapter adapter = new OleDbDataAdapter(sQuery, _connection);
DataSet DS_Audit = new DataSet();
adapter.Fill(DS_Audit, "audit"); //this is taking 3 minutes
return DS_Audit.Tables["audit"];

The reason we're using Access is because every client must have their
own
personal DB to store the data. If we use server DBs such as MSDE or
MySQL, we will need to install the DB in every PC (we can't install
it
in
1 PC because we can't assume that the clients are in a network).
With
Access we can create the mdb from our application. Someone suggested
the
embedded version of MySQL but every client may have several different
files (MDBs), so I'm not sure if that would complicate things.

Thanks for any help.

 

[VMI]

in Access, I don't need to scroll down too much to the last record because
most of the records are visible ; the query asks for the top 20 records
(from 300k+ records). The query is : "select top 20 * from audit where Line

0 order by Line asc". When I do, I don't see a change in speed.

Before the dataset.Fill(), the task manager displays the commit charge iof
383000K/755985K and CPU usage is 4%. During the fill, the CPU usage
fluctuates from 15% to 80%, but it usually stays at 20-25%. The commit
charge increases, but it's not significant (399000K/755985K).
The problem occurs when I query an Access table that has many records (in my
case, 300K records). And I don't know why the PC slows down so dramatically
(in addition to the query taking 2 mins. to complete) because all its
resources are there. Could the problem be with the data adapter?

You have any suggestions?

Thanks.

Access "cheats" on results.
Try scrolling to the last record.
Did you look at task manager on how much memory is available?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

From Access, the query takes 19 seconds to execute (see results in
table). This same query, through the adapter.Fill() takes 1 min 54 secs
and the PC gets really slow to the point that I can't switch between
Windows programs with Alt-Tab.

Did you try the same query from within access itself - just to rule out
the engine bottleneck...

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Yes. It wasn't there when I created it, but I added it afterwards. It's
of
type "Number".
Do you have any idea what it could be?

Thanks.

Are you sure there is an index on Line when you created the database
in
Access?




For some reason, the process of retrieving data (about 20 records)
from
an Access table that has 400K records to a dataTable is taking over
3
mins. to complete. Below is my code to connect to the DB and query
the
table. The table "audit" primary key is "Line".
Another weird thing (but I guess that's another post) is that, while
it's
doing the dataset Fill, my PC is slowed done substantially. But I
don't
know why that would happen since the process is not consuming much
memory
or CPU. This must be an Access flaw because it also happens if I'm
running intensive queries from within Access.
/**** Code ****/
string strDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
sFileName; //sFileName is mdb file
_connection = new OleDbConnection(strDSN);
_oleCommand = new OleDbCommand();
_oleCommand.Connection = _connection;
_oleCommand.Connection.Open();

string sQuery = "select top 20 * from audit where Line > 0 order by
Line
asc";
OleDbDataAdapter adapter = new OleDbDataAdapter(sQuery,
_connection);
DataSet DS_Audit = new DataSet();
adapter.Fill(DS_Audit, "audit"); //this is taking 3 minutes
return DS_Audit.Tables["audit"];

The reason we're using Access is because every client must have
their
own
personal DB to store the data. If we use server DBs such as MSDE or
MySQL, we will need to install the DB in every PC (we can't install
it
in
1 PC because we can't assume that the clients are in a network).
With
Access we can create the mdb from our application. Someone
suggested
the
embedded version of MySQL but every client may have several
different
files (MDBs), so I'm not sure if that would complicate things.

Thanks for any help.

 

[Miha Markic [MVP C#]]

in Access, I don't need to scroll down too much to the last record because
most of the records are visible ; the query asks for the top 20 records
(from 300k+ records). The query is : "select top 20 * from audit where
Line
Before the dataset.Fill(), the task manager displays the commit charge iof
383000K/755985K and CPU usage is 4%. During the fill, the CPU usage
fluctuates from 15% to 80%, but it usually stays at 20-25%. The commit
charge increases, but it's not significant (399000K/755985K).
The problem occurs when I query an Access table that has many records (in
my case, 300K records). And I don't know why the PC slows down so
dramatically (in addition to the query taking 2 mins. to complete) because
all its resources are there. Could the problem be with the data adapter?

No, dataadapter is fine - it is using OleDb to fetch the data.
It could be something wrong with OleDb perhaps.
Can you try same situation on another computer?

 

[VMI]

Yes. i ran it in two client PCs (with only the framework installed) and
we're having the same problem.

Thanks.

 

[Miha Markic [MVP C#]]

If you want you can send me sample that reproduces the problem.
How big is the zipped database?

 

[VMI]

216 megabytes, and I have dial-up.
The problem should happen with any table that contains many records. What do
you mean when you say there's a problem with the OleDB?

If you want you can send me sample that reproduces the problem.
How big is the zipped database?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

Yes. i ran it in two client PCs (with only the framework installed) and
we're having the same problem.

Thanks.

 

[Miha Markic [MVP C#]]

216 megabytes, and I have dial-up.
The problem should happen with any table that contains many records. What
do you mean when you say there's a problem with the OleDB?

OleDbAdapter just invokes OleDb calls...