Lazy Load Multi Select Drop Downs (Performance)

[kevinburkett]

Hello Ghislain!

Thanks for a great grid component and maintaining it and improving it so often!!!

For every instance of Slickgrid that I use in my React app, I iterate through every column definition and create a unique multi select drop down including all cell formatters. I have been using async collections. However, the more columns I have the slower it takes for the grid to load. My largest grid is 168 columns with 11,000 rows of data.

I would like to lazy load the drown down to build only when they are clicked on. However, I have tried multiple events that are available for filters such as onOpen, onBlur and onFocus. onOpen is the closest to working however it only works on the 2nd time that I open the drop down. it takes a second for the function to run and does not update the view even though the collection has been updated and enableCollectionWatch is true. Is there a better way to handle this? I am including my columnDefinition loop and the function that gets called to calculate the new items in the drop down.

On a separate note, is there anything in the pipeline about implementing virtual column scrolling like there is for virtual row (infinite) scrolling? Rendering only the columns in the active viewport? I am pushing the edge of the grid with this much data (2.6MB) and it would mean a complete re-architecture to move to server side sorting, grouping, filtering along with aggregations at every group level.

Thanks!

        for (let colIndex = 0; colIndex < columns.length; colIndex++) {
            const column = columns[colIndex];
            if (column) {
                if (!column.sortComparer) column.sortComparer = sortComparer;
                if (!column.minWidth) column.minWidth = 150;
                if (column.sortable === undefined || column.sortable === null) column.sortable = true;
                if (column.filterable === undefined || column.filterable === null) column.filterable = true;
                if (column.exportWithFormatter === undefined || column.exportWithFormatter === null)
                    column.exportWithFormatter = true;
                if (column.columnGroup === undefined || column.columnGroup === null) column.columnGroup = '';
                if (column.filter === undefined || column.filter === null)
                    column.filter = {
                        model: Filters.multipleSelect,
                        enableRenderHtml: true,
                        collection: [{label: 'Loading...', value: '__LOADING__'}],
                        //  collectionAsync: getUniqueFilterValues(colIndex, column.field, column, dataset, slickgridRef),
                        operator:
                            Array.isArray(column.params?.formatters) &&
                            column.params.formatters.some(
                                (f: Formatter) => f === arrayFormatter && typeof f === 'function',
                            )
                                ? OperatorType.inContains
                                : OperatorType.in,
                        type: column.type,
                        filterOptions: {
                            onOpen: async () => {
                                if (column.filter && column.filter.collection) {
                                    column.filter.collection = await getUniqueFilterValues(
                                        colIndex,
                                        column.field,
                                        column,
                                        dataset,
                                        slickgridRef,
                                    );
                                }
                            },
                            filter: true,
                            autoAdjustDropWidthByTextSize: true,
                            maxWidth: 800,
                            minWidth: 140,
                        } as Partial<MultipleSelectOption>,
                        customStructure: {
                            label: 'label',
                            value: 'value',
                        },
                        collectionSortBy: {
                            property: 'value',
                            fieldType: column.type,
                            sortDesc: column.type === FieldType.dateIso ? true : false,
                        },
                        enableCollectionWatch: true,
                    };
            }
        }
        
export const getUniqueFilterValues = async (
    colIndex: number,
    fieldName: string,
    column: Column,
    dataset: any[],
    grid: any,
): Promise<any[]> => {
    const fieldParts = fieldName.split('.');
    const uniqueValues = new Map();
    const formatters: Formatter[] = [];
    let complexObject: boolean = false;
    if (column.formatter && column.formatter === Formatters.complexObject) {
        complexObject = true;
        formatters.push(column.formatter);
    } else if (
        column.formatter &&
        column.formatter === Formatters.multiple &&
        column.params &&
        column.params.formatters
    ) {
        for (const formatter of column.params.formatters) {
            if (formatter === Formatters.complexObject) {
                complexObject = true;
            }
            formatters.push(formatter);
        }
    } else if (column.formatter) {
        formatters.push(column.formatter);
    }
    if (fieldParts.length > 1 && !complexObject) {
        console.warn(`complexObject formatter must be assigned to the column (${column.name}) with nested fields`);
        return [];
    }
    dataset?.forEach((item, rowIndex) => {
        let nestedValue = item;
        for (const part of fieldParts) {
            nestedValue = nestedValue ? nestedValue[part] : null;
            if (nestedValue == null || nestedValue == undefined) break;
        }
        if (Array.isArray(nestedValue)) {
            for (const value of nestedValue) {
                let finalNestedValue = value;
                for (const formatter of formatters) {
                    finalNestedValue =
                        formatter.name === 'arrayFormatter'
                            ? value
                            : formatter.name === 'arrayObjectToCsvFormatter' &&
                              column.params &&
                              column.params.propertyNames.length > 0
                            ? value[column.params.propertyNames[0]]
                            : formatter(rowIndex, colIndex, finalNestedValue, column, item, grid.slickGrid);
                }
                uniqueValues.set(finalNestedValue, finalNestedValue);
            }
        } else {
            let finalNestedValue;
            for (const formatter of formatters) {
                finalNestedValue = formatter(rowIndex, colIndex, nestedValue, column, item, grid.slickGrid);
            }
            uniqueValues.set(nestedValue, finalNestedValue ?? nestedValue);
        }
    });
    const mapArray = Array.from(uniqueValues, ([value, label]) => ({
        label:
            value === null
                ? createDomElem('span', undefined, 'null', '&#8709;').outerHTML
                : typeof label === 'object'
                ? label.outerHTML
                : label,
        value: value,
    }));
    // console.log(column.name, mapArray);
    return mapArray;
};

[ghiscoding]

I would like to lazy load the drown down to build only when they are clicked on.

@kevinburkett I don't have that kind of features since I never dealt with such large grid and never with that many columns, I see that you already use filterOptions and you are indeed talking about methods from the external library. The external lib onOpen seems like a good solution, however it seems that we're missing functions to set the data afterward, I could add a setData() in that external lib but I already published a new major version to go with upcoming Slickgrid-Universal release (see below) and even then you would need a ref to the ms-select instance which I'm not sure how to get it at the moment (I might need to add a function to get external lib instances, maybe onExternalLibReady(instance) or something similar). I'm not sure if it watches for collection reassignment, you might need to use slice() for it to detect the change, not sure, below is the collection watcher code

slickgrid-universal/packages/common/src/filters/selectFilter.ts

Lines 311 to 320 in d4a1625

	protected watchCollectionChanges(): void {
	if (this.columnFilter?.collection) {
	// subscribe to the "collection" changes (array `push`, `unshift`, `splice`, ...)
	this._collectionObservers.push(collectionObserver(this.columnFilter.collection, this.watchCallback.bind(this)));
	// observe for any "collection" changes (array replace)
	// then simply recreate/re-render the Select (dropdown) DOM Element
	propertyObserver(this.columnFilter, 'collection', this.propertyObserverCallback.bind(this));
	}
	}

You could also create your own Custom Filter/Editor even though that means a bit more work (or you could also extend the SelectFilter with TypeScript).

I'm not sure if I've put some await on the collectionAsync, if so it might be better to move to .then() which could probably help a bit in these kind of large data. You should probably do the same in your case, so if we had the .then() and the .slice() for the change detection, does that do anything?

onOpen: () => {
      if (column.filter && column.filter.collection) {
         getUniqueFilterValues(
              colIndex,
              column.field,
              column,
              dataset,
              slickgridRef,
          ).then(newCollection => column.filter.collection = newCollection.slice());
      }
  },

On a separate note, is there anything in the pipeline about implementing virtual column scrolling like there is for virtual row (infinite) scrolling? Rendering only the columns in the active viewport?

No plan for this kind of thing, since virtual scroll was really implemented by the original SlickGrid (and multiple-select) authors and I wouldn't even know how to properly implement that kind of thing myself. So, I'm sorry but there's no plan do virtual scrolling like this. I would rather focus on making the dropdown lazy loading work since that seems more doable and will probably fix your grid loading slowness. 🤞🏻 I mean even loading 168 columns shouldn't be that slow if none of the dropdown collection are loaded until they are opened

I'm currently focusing on releasing the next upcoming major version (roadmap to 9.0) which for React users will require React 19 and will arrive pretty soon (in a week or two). Would you be able to upgrade? Meaning, are you on React 19? Because I'm not sure if I want to spend time on the current release, I would rather push code on the new release (currently in the next branch). But if you really can't upgrade yet, I might see what I can do in current release.

I could certainly help if you could provide a basic repro on GitHub or Stackblitz. but anyway I will look into it for sure on the lazy loading part and see what can be done but I'm unsure if it will be available in current release or on next major release.

By the way, if you completely remove the select filter, does the grid load as quickly as expected? or is that not as fast as you wish?

[kevinburkett]

I am currently on React 18.2.0. I could look into how difficult it will be to move to React 19. I don't know how backwards compatible it is.

I tried your suggestions "then()" and "slice()" but it did not fix the issue. I also tried some variations.

I would have to look into extending the selectFilter/multi select code. I had to do something similar with some of the aggregator classes like sum and max so they would support complexObjects.

i did remove the multi select filter on every field, and it is still slow and jittery when trying to scroll that many columns. I use a formatter on almost every one of the 168 fields. There are all complex objects because it is an array of json. For a table this large, it needs to be faster for it to be usable. I am running the latest Brave browser (Chromium).

I will look into it more and get back to you.

Thanks!

[ghiscoding]

hmm I'm just curious, is that is also slow if you disable the filters entirely (enableFiltering: false)? Again I have never added that many columns, the most I went was around 20, so 168 columns is just a crazy lot of them😮 I'm not sure if virtual scroll is doable at all on columns, but even if it is, it would take a long time before adding such feature (if ever).

I had to do something similar with some of the aggregator classes like sum and max so they would support complexObjects.

it might be beneficial to get a contribution for that use case.

On the other hand, I'm looking into adding a 3rd and new collectionLazy which could load the Promise with the onOpen with code like below

onOpen: () => {
  if (this.columnFilter.collectionLazy instanceof Promise) {
    this.columnFilter.collectionLazy.then((collectionData) => {
      if (!this._isDataLoaded) {
        const colResult = this.parseCollectionList(collectionData);
        this.msInstance?.refreshOptions({ data: colResult.dataCollection });
        this._isDataLoaded = true;
      }
    });
  }
}

I also think that providing the external lib is also something that I should do, so I should work on that as well (ie: onInstanceReady).

So with code like this with 10sec load

filter: {
  collection: [], // initialize as empty
  collectionLazy: new Promise((resolve) => {
    window.setTimeout(() => {
      resolve(Array.from(Array((this.dataset || []).length).keys()).map((k) => ({ value: k, label: `Task ${k}` })));
    }, 5000);
  }),
  model: Filters.multipleSelect,
},

Also I think for the future, I should add this feature directly in the ms-select library so that I could display a loading spinner or something with this kind of feature. However, again that extra feature with spinner would only be available in next major... but now it would work, just without the spinner which is probably fine for now. Is there a big difference with/without rendering the ms-select collections like you did? I'd like to know if the collectionLazy would help a little or barely.

... oh and I will add this collectionLazy in current release so that you could take advantage of it now, however spinner support will be added in next major

[kevinburkett]

Ghislain,

I tried different combinations of with and without filters. It is about the same. I think it is the number of columns and formatters on all the columns could be contributing. I also pin the first 5 columns to the left so that is like having 2 grids. I also use grouping and aggregations. I wonder if maybe it is also the data view calculating the aggregations on the 11,000 records even though it is only rendering the number in the view. I appreciate supporting the latest version. I will look into upgrading to your latest new version soon. I am also rewriting your backend graphql service in Go and with Postgres and adding the ability to do backend aggregations and grouping. Basically I want to implement all the grid stuff in the backend so that it can handle any number of rows with virtual scrolling. That would include creating realtime multi select filter options for every column with the backend.

Thanks!
Kevin

[ghiscoding]

Hello Kevin,

So I'm making progress on the collectionLazy that I mentioned earlier and will probably release something next week (as a minor, before the next major, see below) but it looks like you have more perf issues.

From what I see, you're using a lot of the features, with a lot of columns, rows and everything. If I was in your shoes, I would probably try to troubleshoot this by disabling everything to go raw, even disabling Filters entirely and even removing Formatters, then start enabling features 1 at a time. It's hard to help without a repro and also even harder when so many features are used in the same grid. Going 1-by-1 would help identify the culprit (which feature exactly causes the big perf decrease).

I will look into upgrading to your latest new version soon.

My next major version is not out yet, probably in a week or two. But upgrading to React 19 might be a good idea if you can. I'm not a React developer at all, but upgrading the demo to React 19 was pretty straightforward because I'm not using any forms or anything complex.

I am also rewriting your backend graphql service in Go and with Postgres and adding the ability to do backend aggregations and grouping.

I wouldn't mind receiving this as a new package since I built Slickgrid-Universal has a monorepo so that we could add any new packages at any time and make them optional. However, I never coded in Go but I know it's extremely fast compared to JavaScript, though I'm not sure how that would work for publishing a Go package...but anyway, any new contributions would be welcome if you want to go that route :)

I am also rewriting your backend graphql service in Go and with Postgres and adding the ability to do backend aggregations and grouping.

Yeah that's one thing that I don't currently have, Grouping and Backend Service together is quite complex and I wouldn't even know how to approach this kind of problem and for that reason I have always told everyone that it's just not supported at all. So again, I would certainly welcome any new PR contributions.

Below is how my lazy loading is improving (I'm showing a blue notification in the background when loading)... and my PR #1923 is now merged (to be released soon)

[ghiscoding]

@kevinburkett I pushed a new release v5.14.0 that includes collectionLazy and works the same as the animated gif above (you can also see it in this Example 16 by clicking on Prerequisite after loading the page). I plan to improve the select dropdown by adding a loading spinner in the ms-select external lib in the future, but that will only be available in the next major release of any SlickGrid repos

[ghiscoding]

I made the changes as well in the ms-select lib, adding a spinner built into the ms-select. It will be available only in the next major version though, current version will show as above