Telerik blogs

How to Implement Drill-down Functionality in Telerik’s PivotGrid for ASP.NET AJAX Part 2

by Radoslav Kirilov
May 08, 2013 Web, ASP.NET AJAX 0 Comments


This is the second post of mine, which elaborates on how to implement drill down functionality in Telerik’s ASP.NET PivotGrid control. In the previous post I demonstrated how to display our Popup Dialog control containing a Telerik Grid with detailed information about the PivotGrid’s data cells. Today I will show you how to optimize the performance of the example provided in my previous post.
The drill down demo from the first part is great, but it is not perfect. It has some weak parts and we can improve them. Here’s how.

Kill the LinkButtons

First, we render LinkButtons and if the PivotGrid has thousands of data cells, thousands of LinkButtons will be added. However, with so many controls on the page the ViewState will be gigantic. Also, the rendered HTML will be very large and the browsers can be slower when rendering it. So let’s kill the LinkButtons.
But wait a second, if there are no LinkButtons, where will the user click in order for the window to pop up? The answer is simple - on the data cells. We can attach a client-side onclick event to every cell and fire an AJAX request or command to the server.  Also, instead of setting the select query arguments as LinkButton CommandArguments, we can pass them as a parameter of the onclick event handler:
void RadPivotGrid1_CellDataBound(object sender, PivotGridCellDataBoundEventArgs e)
    {
        PivotGridDataCell cell = e.Cell as PivotGridDataCell;
 
        if (cell != null)
        {
            // We do not need to attach onclick event on cell which does not have values
            if (cell.DataItem != null)
            {
                string argument = GetCommandArguments(cell);
                string script = string.Format("OpenDetailsWindow('{0}')", argument);
                cell.Attributes.Add("onclick", script);
            }
            index++;
        }
    }
Then on the client side we can fire an AJAX request with the related to the data cell select arguments:
<script type="text/javascript">
     function OpenDetailsWindow(argument)
     {
          $find("<%= RadAjaxPanel1.ClientID %>").ajaxRequest(argument);
     }
</script>
If you inspect the ViewState of the page after this change, you will see that it is twice smaller than before the changes. I think this is our first victory over this example.

Eliminate the Millions of Checks for Each Data Cell

In the previous post I talked that we need to adjust the row and column indexes by calling AdjustRowAndColumnIndexes:
private void AdjustRowAndColumnIndexes(PivotGridDataCell cell, ref int rowIndexesCount, ref int columnIndexesCount)
{
     //if aggregates are more than one additional cells are rendered, so we need to exclude their values from the query
     if (aggregateFields.Count > 1)
     {
         if (RadPivotGrid1.AggregatesPosition == PivotGridAxis.Columns)
         {
             if (cell.CellType == PivotGridDataCellType.DataCell ||
                 cell.CellType == PivotGridDataCellType.RowTotalDataCell ||
                 cell.CellType == PivotGridDataCellType.RowAndColumnTotal ||
                 cell.CellType == PivotGridDataCellType.RowGrandTotalDataCell ||
                 cell.CellType == PivotGridDataCellType.ColumnGrandTotalRowTotal)
             {
                 columnIndexesCount--;
             }
         }
         else
         {
             if (cell.CellType != PivotGridDataCellType.RowTotalDataCell &&
                 cell.CellType != PivotGridDataCellType.ColumnGrandTotalRowTotal)
             {
                 rowIndexesCount--;
             }
         }
     }
      // if row total or grand total cell is hit we need to escape its values from query
     if (cell.CellType == PivotGridDataCellType.RowTotalDataCell ||
         cell.CellType == PivotGridDataCellType.ColumnGrandTotalRowTotal ||
         cell.CellType == PivotGridDataCellType.RowGrandTotalDataCell)
     {
         rowIndexesCount--;
     }
     // if column total or grand total cell is hit we need to escape its values from query
     if (cell.CellType == PivotGridDataCellType.ColumnTotalDataCell ||
         cell.CellType == PivotGridDataCellType.ColumnGrandTotalDataCell ||
         cell.CellType == PivotGridDataCellType.RowGrandTotalColumnTotal ||
         cell.CellType == PivotGridDataCellType.RowAndColumnTotal ||
         cell.CellType == PivotGridDataCellType.ColumnGrandTotalRowTotal)
     {
         columnIndexesCount--;
     }
 }
However, in the best case each data cell will pass through three checks, in the worst case - more than ten. This is a lot of “if” conditions. If there are 10,000 cells, we will have more than 50,000 checks in order to differentiate each cell. Can we decrease the number of checks? Yes we can, but how? If the cells belong to a matrix, we can cache the first row column indexes and the row indexes of each row’s first cell. The good news is that data cells belong to a matrix. So we can cache the indexes of the fake cells. First let’s cache the column data cell indexes:
int index = 0;
int[] allFakeColumnCells = null;
bool isFirstDataCell = true;
int countOfFakeRowCells = 0;
int cellsCount = 0;
string firstRowID = string.Empty;
string currentRowID = string.Empty;
int key = 0;
 
void RadPivotGrid1_CellDataBound(object sender, PivotGridCellDataBoundEventArgs e)
{
    PivotGridDataCell cell = e.Cell as PivotGridDataCell;
    if (cell != null)
    {
        // If this is the first data cell we need to populate the collection whith fake columns cells count
        if (isFirstDataCell)
        {
            PopulateFakeColumnsCellCollection(cell);
            isFirstDataCell = false;
        }
       // We do not need to attach onclick event on cell which does not have values
       if (cell.DataItem != null)
       {
           string argument = GetCommandArguments(cell);
           string script = string.Format("OpenDetailsWindow('{0}')", argument);
           cell.Attributes.Add("onclick", script);
       }
       index++;
   }
}
// This methos is executed only for the first cell from the first row
private void PopulateFakeColumnsCellCollection(PivotGridDataCell cell)
{
    PivotGridDataItem item = cell.NamingContainer as PivotGridDataItem;
    cellsCount = item.Cells.Count;
    allFakeColumnCells = new int[cellsCount];
    firstRowID = item.UniqueID;
    for (int i = 0; i < cellsCount; i++)
    {
        int countOfFakeCells = GetCountOfFakeColumnCells(item.Cells[i] as PivotGridDataCell);
        allFakeColumnCells[i] = countOfFakeCells;
    }
}
private int GetCountOfFakeRowCells(PivotGridDataCell cell)
{
   int count = 0;
   //if aggregates are more than one additional cells are rendered, so we need to exclude their values from the query
   if (aggregateFields.Count > 1)
   {
       if (RadPivotGrid1.AggregatesPosition == PivotGridAxis.Rows)
       {
           if (cell.CellType != PivotGridDataCellType.RowTotalDataCell &&
               cell.CellType != PivotGridDataCellType.ColumnGrandTotalRowTotal)
           {
                count++;
           }
       }
   }
   // if row total or grand total cell is hit we need to escape its values from query
   if (cell.CellType == PivotGridDataCellType.RowTotalDataCell ||
       cell.CellType == PivotGridDataCellType.ColumnGrandTotalRowTotal ||
       cell.CellType == PivotGridDataCellType.RowGrandTotalDataCell)
       {
           count++;
       }
 
       return count;
}
Then we can do the same for the first cell from each row and apply the result to each cell from that row:
public string GetCommandArguments(PivotGridDataCell cell)
{
    // True when first cell of each row is hit
    if (currentRowID != cell.NamingContainer.UniqueID)
    {
        index = 0;
        currentRowID = cell.NamingContainer.UniqueID;
        countOfFakeRowCells = GetCountOfFakeRowCells(cell);
    }
 
    object[] rowIndexes = cell.ParentRowIndexes;
    object[] columnIndexes = cell.ParentColumnIndexes;
    int rowIndexesCount = rowIndexes.Count();
    int columnIndexesCount = columnIndexes.Count();
    int countOfFakeColumnCells = allFakeColumnCells[index];
    rowIndexesCount -= countOfFakeRowCells;
    columnIndexesCount -= countOfFakeColumnCells;
 
    StringBuilder buider = BuildArguments(rowIndexes, columnIndexes, rowIndexesCount, columnIndexesCount);
    return buider.ToString();
}
private int GetCountOfFakeColumnCells(PivotGridDataCell cell)
{
    int count = 0;
    if (aggregateFields.Count > 1)
    {
       if (RadPivotGrid1.AggregatesPosition == PivotGridAxis.Columns)
       {
           if (cell.CellType == PivotGridDataCellType.DataCell ||
               cell.CellType == PivotGridDataCellType.RowTotalDataCell ||
               cell.CellType == PivotGridDataCellType.RowAndColumnTotal ||
               cell.CellType == PivotGridDataCellType.RowGrandTotalDataCell ||
               cell.CellType == PivotGridDataCellType.ColumnGrandTotalRowTotal)
               {
                   count++;
               }
      }
 }
 
  // if column total or grand total cell is hit we need to escape its values from query
   if (cell.CellType == PivotGridDataCellType.ColumnTotalDataCell ||
       cell.CellType == PivotGridDataCellType.RowAndColumnTotal ||
       cell.CellType == PivotGridDataCellType.ColumnGrandTotalRowTotal ||
       cell.CellType == PivotGridDataCellType.ColumnGrandTotalDataCell ||
       cell.CellType == PivotGridDataCellType.RowGrandTotalColumnTotal)
       {
          count++;
       }
 
   return count;
}
With the described changes we decreased the number of data cells checks considerably. But still there is a weak place left - the arguments passed to the client. 
I described in the previous post that for each cell we pass an argument with the following signature: “columnName~value;columnName~value”. For example: 
TransportType~Microvan;Company~Advanced Services Ace;Country~Bulgaria;City~Sofia
however this is a big string and for almost  every data cell there is a repeating part:
TransportType~Microvan;Company~Advanced Services Ace;Country~Bulgaria;City~Sofia
TransportType~Microvan;Company~Continential World Corp;Country~Bulgaria;City~Sofia

Shorten the Strings

Unfortunately transferring very long strings to the client is not a good idea, so we need to perform some optimization, but how? One of the answers here is to replace each string with a number. In this case we can create a key-value pairs of a number and a real string and these numbers will be sent to the client. We can keep key-value pairs in a dictionary stored into the Session:
public Dictionary<int, string> Arguments
{
    get
    {
        if (Session["Arguments"] == null)
        {
            Session["Arguments"] = new Dictionary<int, string>();
        }
        return Session["Arguments"] as Dictionary<int, string>;
    }
    set
    {
        Session["Arguments"] = value;
    }
}
Then into the GetCommandArguments method we can build the number arguments which will be sent to the client:
public string GetCommandArguments(PivotGridDataCell cell)
    {
        …
 
        StringBuilder buider = BuildArguments(rowIndexes, columnIndexes, rowIndexesCount, columnIndexesCount);
 
        return buider.ToString();
    }
 
private StringBuilder BuildArguments(object[] rowIndexes, object[] columnIndexes, int rowIndexesCount, int columnIndexesCount)
    {
        StringBuilder buider = new StringBuilder();
 
        ReplaceArgumentsWithNumbers(rowIndexes, rowFields, rowIndexesCount, buider);
        ReplaceArgumentsWithNumbers(columnIndexes, columnFields, columnIndexesCount, buider);
 
        // Remove the semicolon in the end
        if (buider.Length > 1)
        {
            buider.Remove(buider.Length - 1, 1);
        }
 
        return buider;
    }
  private void ReplaceArgumentsWithNumbers(object[] cellIndexes, List<PivotGridField> fields, int indexesCount, StringBuilder buider)
    {
        for (int i = 0; i < indexesCount; i++)
        {
            string firstPart = fields[i].DataField;
            string secondPart = cellIndexes[i].ToString();
            if (Arguments.ContainsValue(firstPart))
            {
                buider.Append(Arguments.FirstOrDefault(a => a.Value == firstPart).Key);
                AppendSecondParts(buider, secondPart);
            }
            else
            {
                Arguments.Add(key, firstPart);
                buider.Append(string.Format("{0}", key.ToString()));
                key++;
                AppendSecondParts(buider, secondPart);
            }
        }
    }
 
    private void AppendSecondParts(StringBuilder buider, string secondPart)
    {
        if (Arguments.ContainsValue(secondPart))
        {
            buider.Append(string.Format("~{0};", Arguments.FirstOrDefault(a => a.Value == secondPart).Key));
        }
        else
        {
            Arguments.Add(key, secondPart);
            buider.Append(string.Format("~{0};", key.ToString()));
            key++;
        }
    }
Now instead of sending 
TransportType~Microvan;Company~Advanced Services Ace;Country~Bulgaria;City~Sofia 
to the client we just sent something like this:
'0~1;2~3;4~5;6~7'

The only thing that remains is to convert the number to the original string into the RadAjaxPanel1_AjaxRequest event:
void RadAjaxPanel1_AjaxRequest(object sender, AjaxRequestEventArgs e)
    {
        StringBuilder whereClause = new StringBuilder();
        if (!string.IsNullOrEmpty(e.Argument.ToString()))
        {
            string[] elements = e.Argument.ToString().Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
            foreach (var element in elements)
            {
                var group = element.Split(new char[] { '~' }, StringSplitOptions.RemoveEmptyEntries);
                int firstPart = Convert.ToInt32(group[0]);
                int secondPart = Convert.ToInt32(group[1]);
                whereClause.Append(string.Format("{0} = '{1}' AND ", Arguments[firstPart], Arguments[secondPart]));
            }
 
            …
   }

Conclusion

You can see the example of the demonstrated drill down functionality into our SDK. You can download it and think about other optimization techniques which can improve the performance of the Drill Down functionality. I will be glad to hear what ideas for optimization you come up with.
ASP.NET, ASP.NET AJAX, Grid, Web Forms
About the Author

Radoslav Kirilov

is a software developer at one of Telerik’s ASP.NET AJAX teams. Ever since he joined the company in 2009, he has been working on the data-bound and date-picker controls. His interests are primarily concentrated on ASP.NET, AJAX, MVC, SQL and best practices.

Related Posts

Web React

Getting Started with the KendoReact PivotGrid

Web Blazor ASP.NET MVC ASP.NET Core ASP.NET AJAX Release

What’s New in Telerik Web UI Component Libraries with R2 2023

Release Web Mobile Desktop Productivity

The Telerik and Kendo UI R2 2023 Release Is Here—See What’s New!

Comments

Comments are disabled in preview mode.