将Access表中的数据加载到datatable非常慢！ [英] Loading data from Access table to datatable very slow!
问题描述
出于某种原因，从具有400K记录到数据表的
访问表中检索数据（大约20条记录）的过程需要3分钟。
完成。下面是我连接数据库和查询表的代码。
表审计主键是Line。
另一个奇怪的事情（但我猜这是另一篇文章）是，虽然它是
做数据集填充，我的电脑做得很慢。但是我不知道为什么会发生这种情况，因为这个过程没有消耗太多内存或者说是
CPU。这必须是Access缺陷，因为如果我在Access中运行
密集查询，也会发生这种情况。
/ ****代码**** /
string strDSN =" Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + sFileName;
// sFileName是mdb文件
_connection =新的OleDbConnection（strDSN）;
_oleCommand =新的OleDbCommand（）; <无线电通信/>
_oleCommand.Connection = _connection;
_oleCommand.Connection.Open（）;
string sQuery =" select top 20 * from审计Line> 0级按行
ASC英寸;
OleDbDataAdapter的适配器=新OleDbDataAdapter的（sQuery，_connection）;
数据集DS_Audit =新的DataSet（）;
adapter.Fill（DS_Audit，" audit"）; //这需要3分钟
返回DS_Audit.Tables [" audit"];
我们使用Access的原因是因为每个客户端必须有自己的
个人数据库才能存储数据。如果我们使用服务器数据库，如MSDE或MySQL，
我们将需要在每台PC上安装数据库（我们不能将其安装在1台PC中，因为我们不能假设客户在网络中）。使用Access我们
可以从我们的应用程序创建mdb。有人建议使用嵌入式版本的MySQL，但每个客户端可能有几个不同的文件（MDB），
所以我不确定这是否会使事情复杂化。 />
感谢您的帮助。
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.
推荐答案
您确定创建数据库时Line上有索引吗？在
访问？
" VMI" < VO ****** @ yahoo.com>在消息中写道
news：eu ************** @ TK2MSFTNGP15.phx.gbl ...
Are you sure there is an index on Line when you created the database in
Access?
"VMI" <vo******@yahoo.com> wrote in message
news:eu**************@TK2MSFTNGP15.phx.gbl...
出于某种原因，从具有400K记录到数据表的Access表中检索数据（大约20条记录）的过程正在超过3分钟。去完成。下面是我的代码连接到数据库并查询
表。表审计主键是Line。
另一个奇怪的事情（但我想这是另一篇文章）是，虽然它正在做数据集Fill，但是我的PC运行速度很慢。但是我不知道为什么会发生这种情况，因为这个过程并没有消耗太多的内存或CPU。这必须是一个Access缺陷，因为如果我从Access中运行密集查询也会发生这种情况。
/ ****代码**** /
字符串strDSN =" Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" +
sFileName; // sFileName是mdb文件
_connection = new OleDbConnection（strDSN）;
_oleCommand = new OleDbCommand（）;
_oleCommand.Connection = _connection;
_oleCommand.Connection.Open（） ;
string sQuery =" select top 20 * from audit where Line>按行排序
asc" ;;
OleDbDataAdapter adapter = new OleDbDataAdapter（sQuery，_connection）;
DataSet DS_Audit = new DataSet（）;
adapter.Fill（DS_Audit，"审计"）; //这需要3分钟
返回DS_Audit.Tables [" audit"];
我们使用Access的原因是因为每个客户都必须拥有自己的
用于存储数据的个人DB。如果我们使用服务器数据库，如MSDE或MySQL，我们需要在每台PC上安装数据库（我们不能将它安装在1台PC上，因为我们不能假设客户端在网络中）。使用
Access，我们可以从我们的应用程序创建mdb。有人建议使用嵌入式版本的MySQL，但每个客户端可能有几个不同的文件（MDB），所以我不确定这是否会使事情变得复杂。
感谢任何帮助。
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" < Mel @ [删除垃圾邮件] insdirect.com>在消息中写道
news：ui ************* @ TK2MSFTNGP12.phx.gbl ...
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.
"Mel Weaver" <Mel@[remove spam]insdirect.com> wrote in message
news:ui*************@TK2MSFTNGP12.phx.gbl...
你确定有一个在
Access中创建数据库时在线上的索引？
VMI < VO ****** @ yahoo.com>在消息中写道
新闻：eu ************** @ TK2MSFTNGP15.phx.gbl ...
Are you sure there is an index on Line when you created the database in
Access?
"VMI" <vo******@yahoo.com> wrote in message
news:eu**************@TK2MSFTNGP15.phx.gbl...
由于某种原因，检索数据的过程（约20条记录）来自具有400K记录到数据表的Access表占用超过3分钟。去完成。下面是我的代码连接到数据库并查询
表。表审计主键是Line。
另一个奇怪的事情（但我猜这是另一个帖子）是，虽然
它正在做数据集Fill，但是我的PC运行速度很慢。但我不知道为什么会发生这种情况，因为这个过程并没有消耗太多的
内存或CPU。这必须是一个Access缺陷，因为如果我从Access中运行密集查询也会发生这种情况。
/ ****代码**** /
字符串strDSN =" Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" +
sFileName; // sFileName是mdb文件
_connection = new OleDbConnection（strDSN）;
_oleCommand = new OleDbCommand（）;
_oleCommand.Connection = _connection;
_oleCommand.Connection.Open（） ;
string sQuery =" select top 20 * from audit where Line>按行排序
asc" ;;
OleDbDataAdapter adapter = new OleDbDataAdapter（sQuery，_connection）;
DataSet DS_Audit = new DataSet（）;
adapter.Fill（DS_Audit，"审计"）; //这需要3分钟
返回DS_Audit.Tables [" audit"];
我们使用Access的原因是因为每个客户都必须拥有他们的
拥有用于存储数据的个人DB。如果我们使用服务器数据库，如MSDE或MySQL，我们将需要在每台PC上安装数据库（我们不能在一台PC上安装它
，因为我们不能假设客户端在网络中）。使用
Access，我们可以从我们的应用程序创建mdb。有人建议将
嵌入MySQL版本，但每个客户端可能有几个不同的文件（MDB），所以我不确定这是否会使事情变得复杂。
感谢任何帮助。
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＃] - RightHand .NET咨询&开发
SLODUG - 斯洛文尼亚开发者用户组
www.rthand.com
" VM" < VO ****** @ yahoo.com>在消息中写道
新闻：uW ************** @ TK2MSFTNGP14.phx.gbl ...
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
"VM" <vo******@yahoo.com> wrote in message
news:uW**************@TK2MSFTNGP14.phx.gbl...
是的。当我创建它时它不存在，但之后又添加了它。这是
类型数字。
你知道它可能是什么吗？
谢谢。
梅尔韦弗" < Mel @ [删除垃圾邮件] insdirect.com>在消息中写道
新闻：ui ************* @ TK2MSFTNGP12.phx.gbl ...
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.
"Mel Weaver" <Mel@[remove spam]insdirect.com> wrote in message
news:ui*************@TK2MSFTNGP12.phx.gbl...
你确定在线上有索引吗？你在
Access中创建了数据库吗？
VMI < VO ****** @ yahoo.com>在消息中写道
新闻：eu ************** @ TK2MSFTNGP15.phx.gbl ...
Are you sure there is an index on Line when you created the database in
Access?
"VMI" <vo******@yahoo.com> wrote in message
news:eu**************@TK2MSFTNGP15.phx.gbl...
>由于某种原因，检索数据的过程（约20条记录）
>来自
>对dataTable有400K记录的Access表正在接管3
>分钟。去完成。下面是我的代码连接到数据库并查询
>表。表审计主键是Line。
>另一个奇怪的事情（但我猜这是另一个帖子）是，虽然它是>做数据集Fill，我的PC运行速度很慢。但是我
>不要
>知道为什么会发生这种情况，因为这个过程没有消耗太多内存>或CPU。这必须是一个Access缺陷，因为它也会发生，如果我是
>从Access中运行密集查询。
> / ****代码**** /
> string strDSN =" Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" +
> sFileName; // sFileName是mdb文件
> _connection = new OleDbConnection（strDSN）;
> _oleCommand = new OleDbCommand（）;
> _oleCommand.Connection = _connection;
> _oleCommand.Connection.Open（）;
>
> string sQuery =" select top 20 * from audit where Line> 0订单
>线
> asc" ;;
> OleDbDataAdapter adapter = new OleDbDataAdapter（sQuery，_connection）;
> DataSet DS_Audit = new DataSet（）;
> adapter.Fill（DS_Audit，" audit"）; //这需要3分钟
>返回DS_Audit.Tables [" audit"];
>
>我们使用Access的原因是因为每个客户都必须拥有自己的>用于存储数据的个人DB。如果我们使用服务器数据库，如MSDE或
> MySQL，我们需要在每台PC上安装数据库（我们不能将它安装在> 1 PC中，因为我们不能假设客户端在网络中）。用
>访问我们可以从我们的应用程序创建mdb。有人建议>嵌入式版本的MySQL，但每个客户端可能有几个不同的
>文件（MDB），所以我不确定这是否会使事情变得复杂。
>
>感谢您的帮助。
>
> 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.
>
这篇关于将Access表中的数据加载到datatable非常慢！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
