Interesting Performance Observation

Aspose.Cells Product Family
1

We are currently evaluating your product to determine if could help us speed up the importing data from Excel into our system.

Currently we use the Jet OLEDB provider and set its extended properties so that it references the Excel ISAM (Extended Properties="Excel 8.0;HDR=YES;IMEX").

Although this is really fast compared to using automation it's not fast enough for us considering the composition of the files we have to batch import (many worksheets with potentially thousands of rows).

Now for the interesting part: I expected that Aspose.Cells would be either comparable or faster because despite how fast ISAM may be in the end the individual data items would still have to be marshaled up from ISAM into OLEDB and then into the managed framework - here's what I found.

I found that if I took a workbook with only 1 spreadsheet that had 4 columns and 45,000 rows and loaded it into a DataTable using Aspose.Cells it was roughly 30% faster than doing the same with OLEDB (pretty good huh?).

"What's the problem?" you ask (this is the crux of the issue): All this time my configuration was set to "Debug" and I was running and testing it from within the IDE. I decided (for whatever reason) to compile a "Release" version and test it from outside the IDE and to my surprise the OLEDB version was 47% faster. Now the issue really isn’t Debug compile vs. Release – it’s whether or not I ran it from inside the IDE (I just gave you the history as to how I discovered it).

All things being equal I expected the reverse to have happen. Am I missing something here...? (I probably am...)

But at the end of the day all I am really concerned with is a having a quick way of Importing Excel data into a DataTable.

I have attached the code I was using in my tests.

Thanks in advance.

jc3

2

Please try this attached fix. I optimized the performance to load your file.

3

Worked beautifully.

OleDB: ~1.2 secs for 45K rows

Aspose.Cells: ~0.8 secs for 45K rows

It's about a 33.3% gain. Party!!! [<:o)]

I would ask you what you did to fix it but I know it's an IP thing so I wont. Wink [;)]

Thanks.