[GH #1120] Takes a lot of time to load data

Tags: #<Tag:0x00007efc645881c8> #<Tag:0x00007efc64588088>
#1

Hello
I am using Hands on Table for my project. This is how I am initializing it:
const hot = new Handsontable(container, {
data: [
],
height: ‘auto’,
autoColumnSize: true,
width: ‘100%’,
manualColumnResize: true,
rowHeaders: true,
colHeaders: [‘Employee’].concat(headers),
licenseKey: ‘non-commercial-and-evaluation’,
columns: columns
});

I am trying to load data in the table. There are about 4000 records. It takes about 20 seconds to load it. How should I minimize the time it takes?

This is what i am using to load data:

hot.loadData(returnData);

#2

Hi @beccab904

Thank you for contacting us. Do you load the data from the server or from other file in your project? If you are retrieving it from the serve, with such dataset and configuration it shouldn’t take longer than a few seconds, so the issue might lie on the server side.

#3

I get the data from this:
$.ajax({
async: false,
url: ‘url’,
processData: false,
contentType: false,
type: “post”,
dataType: “json”,
data: formdatafilter,
success: function (data) {
returnData = data;
},
error: function (xhr, status, error) {
//console.log(“error”);
}
});

Then I load it using this:
hot.loadData(returnData);

This is what returnData looks like. It has about 4000 records:
[{PkId: ‘1’, Employee: ‘abcd’}]

There are 4000 row and 31 columns. But I am filling only first 2 columns which is PkId and Employee. I get the data from API pretty fast. It freezes at loadData for about 20 seconds.

#4

Hi @beccab904

I prepared an example, where we are loading 4000 rows from the server to the table. Loading time is again around 3-5s: https://jsfiddle.net/handsoncode/cn9rf2b5/

Can you please check how long it loads on your side?

#5

I checked and found out that the delay occurs when I set height as auto here:
hot = new Handsontable(container, {
height: ‘auto’,
colHeaders: true,
rowHeaders: true,
autoColumnSize: true,
manualColumnResize: true,
width: ‘100%’,
licenseKey: ‘non-commercial-and-evaluation’,
data: [],
colHeaders: [‘Employee’].concat(headers),
columns: columns
})

#6

So we’ve got the reason now. Indeed, height set up to auto is causing the delay. This setting is actually causing other problems within the table as well, and in fact, we don’t recommend using it like this.

It should be set either to the fixed value, or not set at all. We have plans to fix the implementation of calculating the measurements of the table, but the issue isn’t or our roadmap yet.

I’ll update you once it’s fixed.

#7

Thank you for your reply.

I had another query.

$(document).on(‘changeDate’, ‘[id^=“ToDate”]’, function (event) {
var id = $(this).attr(‘id’).replace(‘ToDate’, ‘’); // Extract the numeric part of the ID
var shiftValue = $(’[id^=“Shiftnew’ + id + '”]’).val();
var fromDateValue = $(’[id^=“FromDate’ + id + '”]’).val();

    updateSelectedRange1(shiftValue, fromDateValue, event.target.value);
});

function updateSelectedRange(value, startDate, endDate) {
var start = new Date(startDate);
var end = new Date(endDate);

// Prepare data object for bulk update
var dataChanges = [];

var currentStart = new Date(start);

while (currentStart <= end) {
    var dateKey = (currentStart.getDate()) + currentStart.toLocaleDateString('en-US', { weekday: 'short' }).charAt(0);
    var columnIndex = headers.indexOf(dateKey) + 1;

    if (columnIndex !== -1 && headers[columnIndex] !== 'Employee') {
        for (var rowIndex = 0; rowIndex < hot.countRows(); rowIndex++) {
            if (!dataChanges[rowIndex]) {
                dataChanges[rowIndex] = {};
            }

            // Preserve the "Employee" column value
            dataChanges[rowIndex]['Employee'] = hot.getDataAtCell(rowIndex, 0);

            // Update the other columns
            dataChanges[rowIndex][dateKey] = value;
        }
    }

    currentStart.setDate(currentStart.getDate() + 1); // Move to the next day
}

// Use loadData to update the data in bulk
hot.loadData(dataChanges);

}

On change of date I am adding values in the grid. I have multiple dynamic date fields. I am using loadData to add values in the grid. Is there any way i can retain values in the grid. Because on change of date, it removes the old data completely. I only need to update some data on change

I tried using setdataatcell as well but it takes a lot of time to write data in cells. here is the code:
function updateSelectedRange(value, startDate, endDate) {
var start = new Date(startDate);
var end = new Date(endDate);

// Get the index of the first column to skip
var firstColumnIndex = 1;

// Iterate through the date range and update corresponding cells, starting from the second column
for (var rowIndex = 0; rowIndex < hot.countRows(); rowIndex++) {
    // Iterate through the date range and update corresponding cells, starting from the second column
    var currentStart = new Date(start); // Reset currentStart for each row
    while (currentStart <= end) {
        var dateKey = (currentStart.getDate()) + currentStart.toLocaleDateString('en-US', { weekday: 'short' }).charAt(0);
        var columnIndex = headers.indexOf(dateKey) + 1;

        if (columnIndex !== -1 && columnIndex >= firstColumnIndex) {
            hot.setDataAtCell(rowIndex, columnIndex, value, 'loadData');
        }

        currentStart.setDate(currentStart.getDate() + 1); // Move to the next day
    }
}

}

#8

Hi @beccab904

I’m not sure if I understood your requirement correctly. You are changing the data, using loadData method, but you want to keep the data before the change?