BuyLog In
Bug Report T304468
Visible to All Users

Serious performance issues occur when the Cell.CopyFrom method is used for many cells after an upgrade to version 15.1.7

created 9 years ago

Hi

We have made an "Excel Editor" based on the XtraSpreadsheet Control that populates records from a grid into a spreadsheet,
After we upgraded Devexpress from version 14.2 to 15.1 performance has decreased significantly.

Previously it took about 3-4 sec to load 3300 records. Now it takes approx 15 sec.
After a little performance measuring it seems that the "CopyFrom" that is the main time waster (see code below)

Any experience with this problem?

public void SetCellValueFromText(Cell cell, Type valueType, string value, Cell formatCell, bool readOnly)

C#
       {  
           int intValue;  
           double doubleValue;  

-->        cell.CopyFrom(formatCell, PasteSpecial.Formats);  
           if (valueType == typeof(System.Int32))  
           {  
               if (int.TryParse(value, out intValue))  
                   cell.Value = intValue;  
               else  
               {  
                   cell.Value = value;  
                   cell.FillColor = COLOR_ERROR;  
               }  
           }  
           else if (valueType == typeof(System.Decimal))  
           {  
               if (double.TryParse(value, out doubleValue)) {  
                   cell.Value = doubleValue;  
                   cell.NumberFormat = "#,#0.00";  
               }  
               else  
               {  
                   cell.Value = value;  
                   cell.FillColor = COLOR_ERROR;  
               }  
           }  
           else  
           {  
               cell.Value = value;  

           }  
           cell.Protection.Locked = readOnly;  

           if (readOnly)  
               cell.Fill.BackgroundColor = COLOR_READONLY;  

       }
Comments (2)
DevExpress Support Team 9 years ago

    Hello Christian,
    Try to disable the document history during the copy operation as shown below:

    C#
    spreadsheetControl1.Document.History.IsEnabled = false;  
spreadsheetControl1.BeginUpdate();  
// .................  
// copy data from grid to spreadsheet  
// ..................  
spreadsheetControl1.EndUpdate();  
spreadsheetControl1.Document.History.IsEnabled = true;

    If this solution does not help you avoid the performance issue, please provide me with a sample project I can use to reproduce the problematic behavior on my side.
    I will investigate it and do my best to assist you. I am looking forward to your response.

    BS BS
    Bo Sundahl 9 years ago

      Hi Oleg
      Thanks for fast response!
      I have tested your suggestions. It helped a little but still still very slow. Some measurements
      With version 15.1
      - Original code = 23.9 sec
      - With Begin / EndUpdate = 19.4 sec
      - With Begin / End & History disabled = 18.5 sec
      - Without the Cell.CopyFrom statement = 3.6 sec
      I have made a small sample project that demonstrate the problem (enclosed)
      Timedifference using the Cell.CopyFrom or not is from 0.4 sec => ~18 sec
      Looking forward to see your response.

      Sign in to comment on this post

      Answers approved by DevExpress Support

      created 9 years ago (modified 9 years ago)

      We have fixed the issue described in this ticket and will include the fix in our next maintenance update. To apply this solution before the official update, request a hotfix by clicking the corresponding link for product versions you require.

      Note: Hotfixes may be unavailable for beta versions and updates that are about to be released.

      Additional information:

      In the scope of this thread, we have improved the performance when the Cell.CopyFrom method is used for a large number of cells.
      The following image illustrates the time consumption before and after applying this fix:

        Comments (2)
        BS BS
        Bo Sundahl 9 years ago

          Fantastic.
          Our application now performs well again. Thanks for your fast support!

          DevExpress Support Team 9 years ago

            You are welcome, Christian!
            Should you have additional questions regarding our products or need our further assistance, do not hesitate to contact us.

            Sign in to comment on this post
            created 9 years ago

            Hello Christian,
            In your scenario, you copy format settings of a single cell to all other cells in a range. This is not a suitable solution for your scenario.
            It is more suitable to apply the mentioned formatting to an entire range rather than apply the mentioned formatting cell by cell.
            Please refer to the following code:

            C#
            spreadsheetControl1.Document.History.IsEnabled = false;  
spreadsheetControl1.BeginUpdate();  

//Insert data  
for (int excelRowCount = 0; excelRowCount < recordsToInsert; excelRowCount++)  
{  
    Cell cell = ws.Cells[cellDataStart.RowIndex + excelRowCount, cellDataStart.ColumnIndex];  
    SetCellValueFromText(cell, typeof(System.Int32), random.Next(0, 100000).ToString(), cellDataStart, true);  
    for (int index = 1; index < columnsToInsert + 1; index++)  
    {  
         cell = ws.Cells[cellDataStart.RowIndex + excelRowCount, cellDataStart.ColumnIndex + index];  
         SetCellValueFromText(cell, typeof(string), random.Next(1,100000).ToString(), cellDataStart, false);  
    }  
}  

int rigthColumnIndex = cellDataStart.LeftColumnIndex + columnsToInsert;  
int bottomRowIndex = cellDataStart.TopRowIndex + recordsToInsert - 1;  

Range rangeWithData = ws.Range.FromLTRB(cellDataStart.LeftColumnIndex, cellDataStart.TopRowIndex, rigthColumnIndex, bottomRowIndex);  
rangeWithData.CopyFrom(cellDataStart, PasteSpecial.Formats);  

spreadsheetControl1.EndUpdate();  
spreadsheetControl1.Document.History.IsEnabled = true;

            I also attached a sample project to demonstrate this approach in action.
            With this approach, it is required about 1.2 seconds to fill a worksheet with data in your scenario.
            Please try this and let me know your results.

              Comments (2)
              BS BS
              Bo Sundahl 9 years ago

                Yes, your are right IF all cells should have the same formatting.
                But in my application the cells are formatted individually depending on the values and "ReadOnly" flag.
                I wonder what have changed from 14.2 to 15.1. There were no problems at all before we upgraded to 15.1 last Friday.
                I have just made the excact same example using the old environment / Devexpress 14.2. The measurements are:
                Devexpress 14.2 with "CopyFrom" = 1,4 sec
                Devexpress 14.2 without "CopyFrom" = 0,3 sec
                Devexpress 15.1 with "CopyFrom" = 26 sec
                Devexpress 15.1 without "CopyFrom" = 0,5 sec
                Seems that something significantly has changed and I'm a bit worried if other features have similar problems as we plan to use the Spreadsheet for other things as well.

                DevExpress Support Team 9 years ago

                  You are right, Christian.
                  In version 14.2, your sample operated more quickly. We need additional time to investigate what we can do here.
                  We will get back to you as soon as we have any results.

                  Sign in to comment on this post

                  Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

                  Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.