Uploaded image for project: 'ZK Spreadsheet'
  1. ZK Spreadsheet
  2. ZSS-1115

Encounter "Warning: Unresponsive script" in Firefox when setting massive cells

    Details

    • gh.sprint.customfield.default.name:
      Sprint 2

      Description

      Problem description

      A user encounter the "unresponsive script error" with Firefox:

      • they are seeing a problem when trying to render/update cells after we populate the data to a sheet.
      • they are using a process queue on the server side and then when the data is sent back to the client, the show busy spinner pauses, and then before all the cells can update, we get the browser's unresponsive script error. If you continue, you get a few more cells updated.
      • This particular book has 1 sheet tab with 600 cells that need to be updated when the server sends back the data.
      • This took about 5 secs to get the data and render it to the page when using ZSS 2.6.

      Steps to reproduce

      1. run attached files and visit it with Firefox
      2. Click "populate data" button. It's implemented by the echo event. The "unresponsive script warning" should appear.
      3. Click "populate data - event queue" button. The warning should not appear.

      Debug information

      • When unresponsive script warning shows, the response size is relative larger than normal e.g. 2000 cells, 9506k, response array size: 8000
      • call notifyChange() doesn't eliminate the issue
        @Listen("onPopulate= #ss")
        public void populate(Event e) throws InterruptedException{
            populateData();
            Clients.clearBusy();
            Ranges.range(ss.getSelectedSheet()).notifyChange(); // add this line doesn't eliminate the issue nor reduce the response size
        }
        

      Workaround

      Perform a heavy operation in an event queue

      queue.subscribe(new EventListener<Event>() {
      			
      			@Override
      			public void onEvent(Event e) throws Exception {
      					performHeavyOperation();
      			}
      		}, new EventListener<Event>() {
      			@Override
      			public void onEvent(Event e) throws Exception {
      				//access a spreadsheet component
      				Ranges.range(spreadsheet.getSelectedSheet()).notifyChange();
      				Clients.clearBusy();
      			}
      		});
      
              //an event listener to trigger the 
      	@Listen("onCellClick = #spreadsheet")
      	public void onCellClick(CellMouseEvent event){
      		queue.publish(new Event("onStart"));
      		Clients.showBusy("busy...");
      	}
      
      
      1. setMassiveCells.zul
        0.4 kB
        hawk
      2. SetMassiveCellsComposer.java
        2 kB
        hawk
      3. ZKInsertMerge.java
        4 kB
        hawk
      4. zkinsertmerge.xlsx
        366 kB
        hawk
      5. zkinsertmerge.zul
        0.2 kB
        hawk

        Activity

        Hide
        henrichen henrichen added a comment - - edited

        The reason of slowness:

        		for (int column  = 0 ; column < 20 ; column++){
        			for (int row = 0 ; row <800 ; row++ ){
        				Range range = Ranges.range(sheet, row, column);
        				range.getCellData().setEditText(row+", "+column);
        				CellOperationUtil.applyBackgroundColor(range, "#FF00FF");
        				CellOperationUtil.applyFontHeightPoints(range, 14);
        				CellOperationUtil.applyAlignment(range, Alignment.CENTER);
        			}
        		}
        

        The code update 1) text, 2). backcolor, 3). fontHeightPoints, 4). alignment for each cell. Unfortunately, each step would generate a small command for update. Especially, step 2, 3, 4 would do style update which will update the cell's surrounding 8 cells (totally 9 cells; yes, this is stupid. To handle border properly, we need to handle all 9 cells. And we did not separate style update from border update) and these cells actually overlap to each other. It is that several replicate update commands accumulated together which cause the "slowness" issue.

        Solution:

        		for (int column  = 0 ; column < 20 ; column++){
        			for (int row = 0 ; row <800 ; row++ ){
        				Range range = Ranges.range(sheet, row, column);
                                        range.setRefresh(false);  // <--------- tell ZSS not to fire notifyChange for each cell yet 
        				range.getCellData().setEditText(row+", "+column);
        				CellOperationUtil.applyBackgroundColor(range, "#FF00FF");
        				CellOperationUtil.applyFontHeightPoints(range, 14);
        				CellOperationUtil.applyAlignment(range, Alignment.CENTER);
        			}
        		}
                        Ranges.range(ss.getSelectedSheet()).notifyChange(); // <--- notifyChaneg the whole sheet as one command
        
        Show
        henrichen henrichen added a comment - - edited The reason of slowness: for ( int column = 0 ; column < 20 ; column++){ for ( int row = 0 ; row <800 ; row++ ){ Range range = Ranges.range(sheet, row, column); range.getCellData().setEditText(row+ ", " +column); CellOperationUtil.applyBackgroundColor(range, "#FF00FF" ); CellOperationUtil.applyFontHeightPoints(range, 14); CellOperationUtil.applyAlignment(range, Alignment.CENTER); } } The code update 1) text, 2). backcolor, 3). fontHeightPoints, 4). alignment for each cell. Unfortunately, each step would generate a small command for update. Especially, step 2, 3, 4 would do style update which will update the cell's surrounding 8 cells (totally 9 cells; yes, this is stupid. To handle border properly, we need to handle all 9 cells. And we did not separate style update from border update) and these cells actually overlap to each other. It is that several replicate update commands accumulated together which cause the "slowness" issue. Solution: for ( int column = 0 ; column < 20 ; column++){ for ( int row = 0 ; row <800 ; row++ ){ Range range = Ranges.range(sheet, row, column); range.setRefresh( false ); // <--------- tell ZSS not to fire notifyChange for each cell yet range.getCellData().setEditText(row+ ", " +column); CellOperationUtil.applyBackgroundColor(range, "#FF00FF" ); CellOperationUtil.applyFontHeightPoints(range, 14); CellOperationUtil.applyAlignment(range, Alignment.CENTER); } } Ranges.range(ss.getSelectedSheet()).notifyChange(); // <--- notifyChaneg the whole sheet as one command
        Hide
        henrichen henrichen added a comment - - edited

        This will need developer's help to disable refresh first. ZSS can do very little. Of course, the optimized solution would be that ZSS can "merge" update commands automatically but it will be a long journey if is not impossible.

        Show
        henrichen henrichen added a comment - - edited This will need developer's help to disable refresh first. ZSS can do very little. Of course, the optimized solution would be that ZSS can "merge" update commands automatically but it will be a long journey if is not impossible.
        Hide
        henrichen henrichen added a comment -

        Fixed.

        Show
        henrichen henrichen added a comment - Fixed.
        Hide
        henrichen henrichen added a comment - - edited

        Per the zkinsertmerge.*

        			if (cellRange.getCellValue() != null && cellRange.getCellValue().toString().startsWith(SYMBOL_COLLAPSED)){
        				Ranges.range(this.selectedSheet).unprotectSheet("test");
        				final Range insertRange = Ranges.range(selectedSheet, 0, event.getColumn() + 1, 0, event.getColumn() + 9).toColumnRange();
        				insertRange.setAutoRefresh(false);  //<-- disable auto refresh
        				CellOperationUtil.insert(insertRange, InsertShift.RIGHT, InsertCopyOrigin.FORMAT_LEFT_ABOVE);			
        				Ranges.range(this.selectedSheet).protectSheet("test", true, true, false, false, false, false, false, false, false, false, true, true, false, false, false);
        				
        				cellRange.setAutoRefresh(false); //<--  disable auto refresh
        				cellRange.setCellValue(SYMBOL_EXPANDED + " " + cellRange.getCellValue().toString().substring(3));
        				spreadsheet.setMaxVisibleColumns(selectedSheet.getLastColumn(13)+20);
        				spreadsheet.setMaxVisibleRows(selectedSheet.getLastRow());
        				
        				Ranges.range(this.selectedSheet).notifyChange(); // notify change in a whole
        			}
        

        When column is inserted, it will copy the format from its left column and for each cell with different cell style. Use the same trick to make better performance.

        Show
        henrichen henrichen added a comment - - edited Per the zkinsertmerge.* if (cellRange.getCellValue() != null && cellRange.getCellValue().toString().startsWith(SYMBOL_COLLAPSED)){ Ranges.range( this .selectedSheet).unprotectSheet( "test" ); final Range insertRange = Ranges.range(selectedSheet, 0, event.getColumn() + 1, 0, event.getColumn() + 9).toColumnRange(); insertRange.setAutoRefresh( false ); //<-- disable auto refresh CellOperationUtil.insert(insertRange, InsertShift.RIGHT, InsertCopyOrigin.FORMAT_LEFT_ABOVE); Ranges.range( this .selectedSheet).protectSheet( "test" , true , true , false , false , false , false , false , false , false , false , true , true , false , false , false ); cellRange.setAutoRefresh( false ); //<-- disable auto refresh cellRange.setCellValue(SYMBOL_EXPANDED + " " + cellRange.getCellValue().toString().substring(3)); spreadsheet.setMaxVisibleColumns(selectedSheet.getLastColumn(13)+20); spreadsheet.setMaxVisibleRows(selectedSheet.getLastRow()); Ranges.range( this .selectedSheet).notifyChange(); // notify change in a whole } When column is inserted, it will copy the format from its left column and for each cell with different cell style. Use the same trick to make better performance.

          People

          • Assignee:
            henrichen henrichen
            Reporter:
            hawk hawk
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Agile