Pre-loading grid data

[Lynn]

I'm trying to fetch a lot of data, which is reaching 100k+ results. The main problem is that the API doesn't work with this many records at once due to simply being more data than allowed in memory by the webserver (for good reasons). What's the proper solution to continue here? I can easily page the data, but I want the filter/sorting client side over the complete result. It basically comes down to having to execute multiple requests to fetch all data and then use the grid with this data. 

I noticed several issues if I use a DataSource directly, such as paging will request a new page if the page size changes for example. If I have already fetched the first 20 records, and I change the page size from 10 to 15, it will fetch the 15 records again while it already has those records. On top of that it seems that I can't exactly pre-load the data easily either without manually writing some code to call the API endpoint X times.

I'm suspecting that the solution would be to create an observable array and fill this based on a data source that I query page by page? This way I could for example load the first 100 results and then batch the rest in sets of 10k or 20k?

Are there any examples available? I'm using the jQuery variant.

Edit: I'm still in need of at least a "destroy" call for this grid, could that even work together?

 

[Georgi Denchev]

Hi, Lynn,

Unfortunately there aren't any good solutions to the described problem. Generally speaking my recommendation would be to turn all of the server operations on(filtering, paging, sorting, grouping, aggregating) and fetch only the necessary data as you're dealing with a very large amount of it.

Mixing client and server operations is not possible. If you wish to filter the data on the client, you would have to fetch all of it and fill the dataSource with the entire dataset, otherwise the filtering will be applied only on the currently available records.

The DataSource shouldn't be making any additional requests when the pageSize is changed, assuming you have serverPaging set to false.

You can check the Destroying Widgets article in regards to the last question.

Best Regards,
Georgi Denchev
Progress Telerik

[Lynn]

> Mixing client and server operations is not possible. If you wish to filter the data on the client, you would have to fetch all of it and fill the dataSource with the entire dataset, otherwise the filtering will be applied only on the currently available records.

Yeah that's what I ran into. I managed to currently make it work by streaming the generated data through a chunked gzipped request, which turns 55mb into 2mb, and it roughly takes 1.5m to generate. I'm a bit worried about performance with bigger datasets though (this is ~140k records). Client side the grid is working fine though, so that's great!

Are there any guides on how to deal with bigger datasets? At some point I think one of the solutions will be to generate the dataset through a background job and have the grid poll for data until its' ready, but would require some caching solutions for when the page is reloaded. Most of the examples in the docs are for smaller datasets.

In regards to the "destroy" question, it was more about removing a row from the records and then reloading the data, but as I managed to load everything for now, it's no longer an issue.

For documentation purposes, I'm using PHP/Symfony and I'm using a streamed (symfony) response that generates chunks of json through the violet/streaming-json-encoder package. Doctrine (PDO) is set to not use buffered queries to prevent OOM errors.

[Georgi Denchev]

Hello, Lynn,

I am glad that you found a workaround and thank you for sharing it!

The best way to optimize the Grid(and any of the other data bound widgets) is to move as much operations as possible to the server. Sending the filter, sort, grouping, paging information back to the server and building a query which retrieves only a portion of the data from the database(or any other storage) is the best approach to deal with large datasets.

While "streaming" chunks of data is theoretically possible, there's no telling if this would work with all of the functionalities of the widget. I would still strongly recommend that you look into the serverOperations if you plan on expanding the data even further.

[Lynn]

Streaming content works fine in this scenario. Currently the query generating the data is still ~22 seconds and that's without sorting or paging. Adding this would be detrimental to the user experience, though we are in the process of fixing the backend to work better in this regard. I'm using the streaming response in both the read and a create call without issues, though this is done through a custom $.ajax() call in jquery. The response is gzipped and not processed until the full response is received.

read: function (options) {
    $.ajax({
    url: url,
    dataType: "json",
    contentType: 'application/json',
    type: "GET",
    data: options.data,
    success: options.success,
    error: options.error,
});

Are there any ready-made tools in PHP for parsing things like paging and filtering? Basically would need "convert this paging/filtering request to SQL".

[Georgi Denchev]

Hi, Lynn,

You can check the Kendo UI for PHP wrappers which showcase such functionality.

You can download the wrappers from your Profile's Download page and run the demos locally.

You can also check the Remote Binding section of the documentation which showcases how to configure the wrappers to work with server operations and the DataSource class. The class will automatically transform the filters/sorts/paging into an SQL query and execute it on the Database it is connected to.

In case you have issues with setting up the environment, please check the Introduction article:

https://docs.telerik.com/kendo-ui/php/introduction 

If you do not wish to use the wrappers, I am afraid that you'll have to manually build the SQL queries with the provided request parameters.