Excutereader is very slow when taking data by oledbcommand object

I am fetching data开发者_StackOverflow社区 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.