Why is the OleDbDataAdapter.Fill() extremely slow?

Question

I am trying to build an application that will read through an excel files (.xlsx, .xls). Unfortunately, the OleDbDataAdapter.Fill() performance is surprisingly bad. I takes up to 2 minutes to read one record from the file.

More info about the file:

Size - 257MB
Columns - 104
Rows - 1 000 000

Code I am currently using to read the file:

    string conStr = string.Empty;
    string strQuery = string.Empty;
    switch (extension)
    {
        case ".xls": //Excel 97-03
            conStr = @"Provider=Microsoft.Jet.OleDb.12.0;Data Source=" + file_source + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1;';";
            strQuery = "SELECT " + col_no + " * FROM [" + workbook + "] ";
            break;

        case ".xlsx": //Excel 07
            //connection string to connect to the xlsx file
            conStr = @"Provider=Microsoft.Ace.OleDb.12.0;Data Source=" + file_source + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;';";
            strQuery = "SELECT " + col_no + " * FROM [" + workbook + "] ";
            break;
    }
    DataTable tbl = new DataTable();
    OleDbDataAdapter ada = new OleDbDataAdapter(strQuery, conStr);
    ada.Fill(tbl);
    ada.Dispose();

    return tbl;

Your help would be greatly appreciated!

Thanks!

Some alternatives available here: http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp As pointed by @semao using Excel as database for huge datasets is not very performant solution. e.g. in case of XLSX file the file must be unzipped, loaded to memory, indexed and only then you can query it. It is not file format suitable for fast random access — xmojmr, Jul 21 '14 at 13:25

score 3 · Answer 1 · answered Jul 21 '14 at 13:05

3

It is a bad idea to store large data sets in Excel files. In my opinion reading 250MB Excel file in 2 minutes is to be expected.

I would suggest switching to any database solution. But if you can't change you data store, you can try using Excel COM objects (it will require Excel to be installed on server machine though). Here is a walktrough by Teddy Garland.

answered Jul 21 '14 at 13:05

semao

1,757
12
12

Thanks semao. I will check it out. – user2090983 Jul 21 '14 at 13:09

Why is the OleDbDataAdapter.Fill() extremely slow?

1 Answers1