通过OleDbCommand对象采集数据时Excutereader很慢 [英] Excutereader is very slow when taking data by oledbcommand object

问题描述

我用的OleDbCommand的对象，并将其加载到数据表中获取从DBASE4数据库中的数据。但它花费过多时间来获取大约5-10分钟，160条记录。 请帮我。

code：

 使用（OleDbConnection的CN =新的OleDbConnection（@供应商= Microsoft.Jet.OLEDB.4.0;+ @数据源=+ TrendFilePath +\\+路径名+ ;+ @扩展属性=的dBASE III;））

 使用（OleDbCommand的厘米= cn.CreateCommand（））
 {
    cn.Open（）;

    为（中间体L = 0; L＆其中; =月; l ++）
     {
         数据表dt_Dbf =新的DataTable（）;
         FROM_DATE = DateTime.ParseExact（frmdate，dateFormat2，供应商）;
         FROM_DATE = From_Date.AddMonths（L）;

         INT月= From_Date.Month;
         。串年=1+ From_Date.Year.ToString（）子串（2，2）;

         如果（L == 0）
         {

              cm.CommandText = @SELECT * FROM 128.DBF，其中DATE_Y =
                               +一年+和DATE_M =+月+和DATE_D＆GT; =+ From_Day +;
              dt_Dbf.Load（cm.ExecuteReader（CommandBehavior.CloseConnection））;
         }
    }
}
 

解决方案

我怀疑你执行查询的问题。除非查询被过滤在大规模（百万/十亿）的数据集，我看不出它有问题 - 即使你执行12（我假设每个月？）这些查询。此外，当我在写，是您计划使用的数据表呢？每个月，你填充表，然后把它扔掉。 （既然你提到的数据库是唯一〜300K，这是不可能的查询本身占用了任何显著时间）。

它可能有一些做的网络，或者文件本身。就像我说的，你执行12查询，但拆除连接，每一个成功。这意味着，你需要建立12个接口，以及运行12查询。你应该调查你是否可以保持你需要执行组查询的一个连接。

另一种可能性是锁定。是数据库文件能够保持多个连接并执行多个查询用户的不同？我不知道该质数据库产品在所有的，但如果是单用户/单线程的，也可能是其他人所使用的数据库，并锁定它，你出来的时候的情况。

试用数据库复制到本地计算机，更改连接字符串来引用本地副本，并再次运行此code。如果执行时间显著减少，我相信你就可以自信地说，有一些类型的网络问题。从这一点来说，你会（可能）需要从有人在你的组织寻求帮助。 5-10分钟，12看似简单的查询，是一个笑话。

如果网络是问题的原因，有几个选项，你可以采取。

• 询问网络/基础设施的人进行调查。这可能是硬盘上的份额，或超载的网络IO。
• 复制数据库的机器在程序的开头。这只会工作，如果你只是做阅读，而你并不需要更新的数据。或者，复制到本地上在每个查询的开始，和只写到网络。这将保持（相对）新的数据，并确保所有的写操作都是正确的。但是，如果你做了很多的查询，复制整个文件的网络成本比将可能有更糟糕的表现。
• 使用更好的数据库产品（我的建议）。建立一个数据库服务器，使用的是Postgres或MySQL（我劝Postgres的），迁移dBASE数据到服务器，并把所有的应用程序引用dBASE文件指向数据库服务器。你真的应该这样做，如果有多个连接需要访问数据库。如果只有一个连接需要访问数据库，则数据库应该是机器做所述访问上。

I am fetching data from dBase4 database by using object of oledbcommand and load it into datatable. but it is taking too much time to fetch 160 records around 5-10 minutes. Please Help me Out.

Code:

 using (OleDbConnection cn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source=" + TrendFilePath + "\\" + Pathname + ";" + @"Extended Properties=dBASE III;"))

 using (OleDbCommand cm = cn.CreateCommand())
 {
    cn.Open();

    for (int L = 0; L <= months; L++)
     {
         DataTable dt_Dbf = new DataTable();
         From_Date = DateTime.ParseExact(frmdate, dateFormat2, provider);
         From_Date = From_Date.AddMonths(L);

         int month = From_Date.Month;
         string year = "1" + From_Date.Year.ToString().Substring(2, 2);

         if (L == 0)
         {

              cm.CommandText = @"SELECT * FROM  128.DBF where DATE_Y =" 
                               + year + " and DATE_M = " + month + " and DATE_D>=" + From_Day + ""; 
              dt_Dbf.Load(cm.ExecuteReader(CommandBehavior.CloseConnection));
         }
    }
}

解决方案

I doubt that the query you're executing is the problem. Unless the query is filtering on a massive (millions/billions) data set, I don't see a problem with it - even though you're executing 12 (I'm assuming for each month?) of these queries. Also, while I'm writing, were you planning on using the DataTable at all? For each month, you populate the table, then throw it away. (Since you mention the database is only ~300k, it's impossible that the queries themselves are taking up any significant time).

It probably has something to do with the network, or the file itself. As I said, you're executing 12 queries, but tearing down the connection as each one succeeds. This means that you need to establish 12 connections, along with running 12 queries. You should investigate whether or not you can maintain a single connection for the set of queries you need to execute.

Another possibility is locking. Is the database file capable of maintaining multiple connections and executing multiple queries for different users? I don't know the dbase product at all, but if it is single user / single threaded, it could be a case that other people are using the database and locking you out of it for a time.

Try copying the database to your local machine, changing the connection string to reference the local copy, and run this code again. If the execution time is significantly reduced, I believe you'll be able to confidently say that there is some kind of network issue. From that point, you will (probably) need to seek help from someone in your organisation. 5-10 minutes for 12 seemingly simple queries is a joke.

If the network is the cause of the issue, there are several options that you can take.

• Ask the networking/infrastructure people to investigate. It may be disk on the share, or overloaded network IO.
• Copy the database to the machine at the beginning of the program. This will only work if you are only doing reads, and you don't need updated data. Alternatively, copy to local on at the beginning of every query, and only write to the network. This will maintain (relatively) fresh data, and will ensure all writes are correct. However, if you do a lot of queries, the network cost of copying the entire file over will probably have worse performance.
• Use a better database product (my recommendation). Set up a database server, using Postgres or MySql (I advise Postgres), migrate the dbase data to the server, and have all your applications that reference the dbase file point to the database server. You really should be doing this if more than one connection needs to access a database. If only one connection needs to access the database, then the database should be on the machine doing the accessing.

这篇关于通过OleDbCommand对象采集数据时Excutereader很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！