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.