none
ACE.OLEDB - strange slowdown when file is opened in Excel RRS feed

  • Question

  •  

    It looks like  ACE.OLEDB provider gets much slower if spreadsheet being read is at the same time opened in Excel.

    Steps to reproduce:

     

    1. Create a new workbook in Excel
    2. Enter something into cells A1 and A1000000
    3. Save workbook, close Excel
    4. Compile and run the code below. Note how much time it took to iterate through rows.
    5. Open workbook in Excel and repeat step 4. Note that iteration through rows got much slower (10 times or something).

    Does anyone have an idea why it is so and how to get rid of slowdown?

     

          
    
     string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Mode=Share Deny Write;Data Source=C:\\Path\\Book1.xlsx;Extended Properties=\"Excel 12.0;IMEX=1;HDR=Yes\"";
    
              OleDbConnection connection = new OleDbConnection(connectionString);
    
              connection.Open(); 
    
    
              OleDbCommand _command = new OleDbCommand(cmdText, connection);
    
              _command.CommandTimeout = 60;
    
    
              OleDbDataReader _reader = _command.ExecuteReader(CommandBehavior.SingleResult);
    
    
              while (_reader.Read())
              {
                if (!_reader.IsDBNull(0))
                {
                  object p = _reader.GetValue(0);
                }
              }
    

     

    Tuesday, May 3, 2011 1:57 PM

Answers