Performance when using Smart Markers

Aspose.Cells Product Family
1

Hello,

I have a simple worksheet a header row (3 columns), with 3 smart markers in the second row for pulling the 3 columns of data out of the dataset. My data table has 12,000 rows and 3 columns of data. When I call the Process(sheetNumber, true) it takes like 49-50 seconds to process.

If I bypass Process and write the values directly it takes .25 seconds (using the code below). Can you explain what's up with difference, can anything in my designer sheet be causing this?

private void LoadAttributeDataByCell(Worksheet w, DataTable dt)
{
int rowCount = 0 ;
foreach (DataRow r in dt.Rows)
{
rowCount++ ;
w.Cells[rowCount, 0].PutValue( r["cat_key"].ToString() ) ;
w.Cells[rowCount, 1].PutValue( r["att_key"].ToString() ) ;
w.Cells[rowCount, 2].PutValue( r["value"].ToString() ) ;
}
}


Kris

2

Hi Kris,

Thanks for your report. I will check this issue right now.

3

Hi Kris,

I wrote a test case as you described. The execute time is only about 0.8s.

Could you send your designer file and test code to me? Thanks.

4

Laurence,

I emailed you a copy of the designer file, sample code, and data.

Kris

5

Hi Kris,

I found the performance is slowed down when pushing down the rows. I will find how to optimize it. In your template, you can change the smart markers to speed up the problem. Please change your smart marker to “&=attValues.cat_key”,"&=attValues.att_key" and “&=attValues.value”. Please have a try.

6

Hi Laurence!

I also find massive performance problems when using Smart Markers! Unfortunately in my case, I have to push the rows down, because in my designer-file there are different sections one below the other, which would be overwritten otherways.
The process method for the first insert of 13.250 rows is takes about 6-8 seconds. The second insert (again about 13.250 rows), which starts below the rows just inserted (at row 13.255) takes about 2 minutes and 10 seconds!!

Do you have any ideas how to improve performance??

wolfo

7

Yes. I find the reason for this performance problem and have an idea to solve it. It will take me about 2 weeks to optimize and test it.

8

Laurence-

Are these performance changes in v3.0.2.1?

We are having similar slow results while evaluating Aspose.Excel v3.0.2.1. When our datatable has 2300 rows, it gets created in less than 5 seconds. However when it has 8000 rows, the time to process leaps up to 30 seconds. I’ve tried various combinations of things in our designer sheet (noadd is not an option in this particular template) but appear to have hit a performance ceiling.

Thanks,
Todd

9

Hi Todd,

I did optimize this issue. Could you please upload your template and sample code here?

If you don’t want to make it public, please send it to excel@aspose.com.

10

Hello Laurence,



I am using version 3.6.1.0 and experiencing slow performance when using
a designer sheet to populate a spreadsheet. This is the scenario:



I have a DataTable with 5,000 rows and 35 columns. In the
designer sheet there are no formulas. I need to be able to push
down rows, so I can’t use “(noadd)”. This is taking about
18 seconds to populate. When I increase the number of rows in the
DataTable to 10,000 I would expect the time it takes to be around
double (36 seconds), but it actually takes 87 seconds. And when I
further increase the number of rows to 15,000 it takes 195 seconds.



Can you try a similar test case and let me know if you get similar performance results?



Thank you.

Kerry

11

If you want to populate DataTable data into a rectangle area, it’s better to use ImportDataTable method. It will be much faster.