Performance issue writing DateTimes to DataTable

Jan 14, 2013 at 9:47 PM

Hi there,

We are using EPPlus to read and write data between Excel and a DataTable.

The tests below were performed using 2500 rows (we need to import 30.000 rows in practice).

1. Baseline, read Excel sheet into DataTable as Strings takes < 1 second. 

2. Change the DataTable second column to the correct DataType Double. The import takes again < 1 second.

3. Change the DataTable first column to the correct DataType DateTime. The import takes 27 seconds! Imaging if we are going to read 30.000 rows.

Currently we loop through the rows to read them. Is there a beter way to do this because 27 seconds for 2500 rows is to long.


Jan 15, 2013 at 9:42 PM
Edited Jan 16, 2013 at 2:20 AM

Code novice checking in.  If I could look at the spreadsheet in question and know exactly what you want to read from the spreadsheet into a DataTable I could try and do some speedy code.

/EDIT:  If the spreadsheet has private data then I can understand if you can't provide that.  So instead if you could detail:  How many columns.  Per spreadsheet column what is the cell formatting type - Numeric\Date\Currency, etc.  Does Date column also include an hours\minutes\second.  What regional date format is used..  What is the typical\max length in characters of string column, etc, etc.   So enough detail for me to create a test spreadsheet with some random data that fills 30,000 rows that is a good analogy of the data structure of your spreadsheet.  Also what are the Datatable columns and .Net DataTypes.

Jan 16, 2013 at 9:26 PM

xav2075, Thanks for responding. I have no problem sharing the Excel sheet, it contains test data. I don't see a way to attach it though.

As I wrote above, everything goes quickly until I want to convert the first colum in Excel (formatted as Date with time part) into a DataTable column with datatype DateTime.

Jan 18, 2013 at 11:49 AM

I have sent you a forum message about sending me the spreadsheet.

Feb 3, 2013 at 7:28 PM
xav2075, excuses for responding so late. I did not get the forum message, however the problem went away by itself this week.

My previous tests were done in a virtual machine, doing the tests on the host itself yielded the following results: 29.994 rows in 4.748 seconds.

This is acceptable to me.