Free Support Forum - aspose.com

Performance with Pivot Table in Aspose.Grid

We are creating a pivot table based on the result of a query (DataTable), this result has about 50,000 rows, but the pivot table row amount depends on the fields used for grouping on rows.

Currently we are generating an statistical information with pivot table rows grouped by state or state/city and columns grouped by years.

We can group this data in 32 rows and the pivot table is generated quickly, just a few seconds. (information grouped by state).

If we use the same input data to generate a more detailed pivot table, with about 1,100 rows, the table visualization takes a long time (about 20 minutes) or is never generated. (information grouped by state and city).

If we generate the table, not all cities at once, but breaking the report in for instance 3 parts of about 350 rows each and generating the pivot table separately for each part we can generate all the information in about 3 minutes (1 minute per pivot table).

We have made several tests to see the response time of aspose.grid and I have seen that the time taken to generate a pivot table depends more of the resulting rows. But if we double the number of resulting data time taken is about five times. So the time is not proportional to the size of the resulting pivot table.

The other thing we have seen by debugging our code is that the aspose.grid method called to buid the pivot table is executed in no more than a minute, but a long time taken is to produce the resulting html sent to the client browser.

Our code to generate the pivot table looks like this:

...

PivotCache pc = gridWeb.WebWorksheets.PivotCaches.Add(dataTable);
PivotTable pt = pc.CreatePivotTable(hojaTablaDinamica, hojaTablaDinamica.Cells[yTabla,xTabla] , "");

// report rows
IList filas = defRep.listarFilas();
foreach (String fila in filas)
{
pt.PivotFields[fila].Orientation = PivotFieldOrientation.RowField;
pt.PivotFields[fila].SortOrder = PivotFieldSort.Asc;
}

// report columns
IList columnas = defRep.listarColumnas();
foreach (String columna in columnas)
{
pt.PivotFields[columna].Orientation = PivotFieldOrientation.ColumnField;
pt.PivotFields[columna].SortOrder = PivotFieldSort.Asc;
}

// Report data definition
IList datos = defRep.listarDatosReporte();
foreach (IDefinicionDatoReporte dato in datos)
{
pt.PivotFields[dato.nombre].Orientation = PivotFieldOrientation.DataField;

switch (dato.operacion.id)
{
case 1:
pt.PivotFields[dato.nombre].Function = PivotFieldFunction.Count;
break;
case 2:
pt.PivotFields[dato.nombre].Function = PivotFieldFunction.Sum;
break;
case 3:
pt.PivotFields[dato.nombre].Function = PivotFieldFunction.Average;
break;
case 4:
pt.PivotFields[dato.nombre].Function = PivotFieldFunction.Max;
break;
case 5:
pt.PivotFields[dato.nombre].Function = PivotFieldFunction.Min;
break;
}
}

pt.DataBind();
...

I will apreciate any help to solve this isue.

Hi,

Thank you for considering Aspose.

We have added your issue to our issue tracking system with issue id CELLSNET-11772. We will look into it and get back to you soon.

Thank You & Best Regards,

<span style=“font-size: 10pt; font-family: “Segoe UI”;”><o:p></o:p>

Hi,

Thank you for considering Aspose.

Please provide the source data of the PivotTable or create a simple application and post it here that will help us to analyze why generating report is slow. We have tested for your issue. The source data is 1000 rows and all of the row’s data is different. This only takes 40 seconds. Please check the attached test application. Also, sending the result to client browser needs longer time, I think it maybe because of the data in the GridWeb.

Thank You & Best Regards,

Nausherwan, thanks for yuor answer, however I wasn’t able to run the example, due to it is built using aspose.cell, while we purchased aspose.grid.

Attached, I am sending an example application which create three different pivot table based on the same data source.

The first one generate data grouped by state, so the output table is small and is generated in just a few seconds.

The other two examples create a pivot table grouping by state and city, so the resulting pivot tables are larger and take a longer time to generate. However the second one just shows data from ten states and is generated in several minutes, but the third generate information for all states (about 30) and is never generated.

The example programs takes data from a SQL Database, a backup of it is included (testdata.bak). To run the application using the SQL data source is necessary to restore the backup into a MS SQL 2005 Server and then modify the connection string in the Web.Config.

Alternatively I am including the same data into an excel file that can be used to populate a database on any other SQl Engine if required. However that requires a little recoding in the application.

I am generating the pivot table without include the source data on a workseheet.

The excel file also includes two sample pivot tables, one grouped by state and other grouped by state and city.


Hi,

Thank you for sharing the sample application and database backup.

We will look into your issue and get back to you soon.

Thank You & Best Regards,

Hi,

Thank you for considering Aspose.

After farther analysis, we found generating PivotTable report causes serious performance issues. It will take around two weeks time to fix the issue.

Thank You & Best Regards,

Hi Nausherwan,

There is some new about this issue?

As an alternative. It is possible to create a pivot table and then export it to an excel file, all of this in the server side, without send the resulting sheet to the client browser for rendering, and then allow the user just to download the file?

Would this take significant less time ?

This would be an acceptable solution in this case.

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

We are looking into your issue and we will update you soon. As per the alternate process, you may use Aspose.Cells APIs to create a pivot table on the server side and share the file with the user. Please see the following documentation link with details regarding the creation of Pivot Table using Aspose.Cells:

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/how-to-create-a-pivottable.html

Thank You & Best Regards,

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Please try the attached latest fix. We have improved the performance of generating PivotTable. It is about 50 seconds to create a PivotTable report using the source data that you have provided us. Attached is the test project for your reference.

Also, GirdWeb can save a PivotTable report to an excel file in server side. But there are some problems in this process. We will continue to deal with this issue and let you know when the issue gets resolved.

Thank you & Best Regards,

Hi, Nausherwan,

This has been a nice work. We tested the test project and it takes 45 second to generate. When we replaced the excel data import for a SQL query, the pivot table generation time was reduced to 28 seconds.

However, happiness is not complete, because you send us a fixed version of Aspose.Cells, but we are using Aspose.Grid, so we are not able to implement the new version in the production environment because we need a fixed Aspose.Grid.dll and we received Aspose.Cells.Grid.dll.

When is going to be available this fix in aspose.Grid?

Hi,

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Thank you for considering Aspose.

Well, Aspose.Grid component has been merged into Aspose.Cells component and we don’t provide any further updates / fixes for Aspose.Grid Component. You may make minor changes in your code and convert it to Aspose.Cells Grid suite.

Thank You & Best Regards,

May I use my Aspose.Grid licence to use Aspose.Cells in this case?

Hi,

Please check:
http://www.aspose.com/corporate/purchase/policies/aspose-grid-merged-with-aspose-cells.aspx


Thank you.

The issues you have found earlier (filed as 11772) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.