Import large result set into xlsx using importResultSet

Aspose.Cells Product Family
1

Hi,


I’m trying to import a large 300K row resultset into a excel worksheet using importResultSet api and it took 45 minutes to do that. Can you please let me know some option to optimize this.

I’m using a temporary license to use the latest Aspose Cells jar and I have attached the code I’m using.

Thank you,
Priya
2

Hi,


Thanks for providing us some details.

For this issue, we do not know what’s the size of your dataset, so we are not sure whether we can improve the performance for your case or not. Could you send us the generated file by Cells.importResultSet() method and we
will make further investigation.

Thank you.
3

Hi,


Thank you for taking a look at my issue…

The size of the result I trying to import is 300K records. But it could get as big as a few million records…

I see the mention of Aspose Light Cells but not able to find a sample of how to use it on resultset in Java.

Thank you,

Priya
4

Hi,


Well, we are not sure how many columns you are rendering, so as requested, could you give us your template file with 300K rows and specified columns, so we could evaluate your scenario/case on our end a bit. Regarding LightCells APIs, we are not sure if it helps. The LightCells APIs would be useful to insert data row by row in light weight mode although one way can be you may input data record by record (e.g using Cell.putValue() method) from your result set in the even driven mode using your own code. See the document on LightCells APIs for your reference:
https://docs.aspose.com/display/cellsjava/Using+LightCells+API

Thank you.
5

The number of columns maybe 100 or more, with a maximum of 990.

But that one I tried had only 12 columns… But still importing took 45 minutes.
6

Hi,


Thanks for providing us further details.

Could you provide us output Excel file (300K rows and 12 columns) which took 45 minutes on import, so we could evaluate your issue properly.

Thank you.
7

Hi,


I have attached the 300K sample excel file that takes a long time to import results.

Thank you
8

Hi,


Thanks for the sample file.

I have logged an investigation ticket with an id “CELLSJAVA-42207” for your issue. We need to thoroughly investigate your scenario/ case to evaluate if Cells.impotResultSet() is taking more time to import data into the worksheet. If we find the issue, we will try to figure it out and optimize the method.

Once we have an update on it, we will let you know here.

Thank you.
9

Hi Amjad,


Can you please let us know how long this may take. We have a deadline and we need this feature asap. It will be great if we can a fix soon. :slight_smile:

Thanks
10

Hi,


Please spare us a little time (e.g 3-5 days or so), so our concerned developer could evaluate/analyse your issue and we will be in a better position to update on it.

Thank you.
11

Thank you for the update, Amjad.


I look forward to trying the new code soon. :slight_smile:
12

Hi,


We did test your case. Firstly, we insert 300k records into the database, the records in the database look like the screenshot “dbrecrod.png” (attached). Then we use the following same code as yours to import data:
Workbook wb = new Workbook();
Cells cells = wb.getWorksheets().get(0).getCells();
cells.setMemorySetting(MemorySetting.MEMORY_PREFERENCE);
long t = System.currentTimeMillis();
cells.importResultSet(rs, “A1”, true);
System.out.println(System.currentTimeMillis() - t);
rs.close();
The output time is 6378, that is, importing only takes about 6 seconds.
We are afraid we could not reproduce the performance issue. If you still think it is an issue of our component, please provide us your console application (runnable) and sample db data (such as, one sqlite database file) to reproduce the issue so we can make further investigation.

Thank you.