Despite the COVID-19 outbreak, our team continues operating at full speed. We are always here to support and answer all your questions.

Feel free to reach out by filling this quick form.

Fill the form
Get Free Trial

Custom Datasource API: Lazy loading of Flat Table View

Answered
Parmod asked on March 26, 2021

Hi Flexmonster Team,
I am using the Custom Datasource API and everything is working fine with the Pivot. But when it comes we do flat view(select-request-for-flat-table) form the Pivot, we are facing problem of loading data into the flat table.
Record size : 1,000,000(approx rows)
Flat table page size: 10,000
Request to server : 100
Problem:
Until all the pages are downloaded from the server flat view doesn’t display anything on the UI.
We can’t wait for that long(approx 30-60 min depending on network speed and how may fields need to display).
For Flat Table View in Custom API Datasource, we want lazy loading mechanism on scrolling the Flat View to download more data based on the Total page number.
I don’t know about rest of the api. But for Flat View on Custom API Datasource It make sense to have lazy loading feature because all the computation we are doing on the database/server.
Regards,
Parmod

5 answers

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster March 26, 2021

Hello, Parmod,
 
Thank you for reaching out to us.
 
Currently, Flexmonster does not support lazy loading for the custom data source API.
 
Still, loading 1M records for 30-60 minutes seems to exceed expectations even when loading the whole dataset at once (without lazy loading or paging). Our team assumes this may be due to the server layer’s bottleneck (for example, slow database querying) or specifics of the data itself.
 
Do you think you could provide us with a sample dataset (use dummy data if needed) so that we could test it on our side?
 
Also, it would be useful for us to understand how the server operates with the data. For example, does it load the data in advance and store it in the RAM, or query the data storage every time Flexmonster performs a request.
 
We are looking forward to hearing from you.
 
Best regards,
Illia

Public
Parmod March 26, 2021

Hi Illia,
The maximum timing taken by the database to process the query.
Testing:
drillthroughMaxRows: 50000
Total Records: 1,199,760
Number of Fields: 2
Time taken around 20 minute.
Average time of execution: 29 seconds.
Q: it would be useful for us to understand how the server operates with the data?
A: We follow below steps
1. After receiving the request from UI  we create a database query from it and execute the query.
2.   After getting the response(result set) from the database, we pull row in sequential manner from the result set.
3.Convert the row to create Flexmonster Response and flush that data to ui.
4. Repeat step 2 and 3 until we get all the records from the db.
Q: does it load the data in advance and store it in the RAM
A: Its not feasible for us, because the data set size is in Tera Bytes. So everything is done with the query. Only Flexmonster Response generation is handled on the Server side.
One more thing, In general we scroll to 2-5 pages of Flat view data only. So if lazy loading support is provided it will help use to reduce the server calls and we can reduce the “drillthroughMaxRows”
to 1000 or 500 so that user will get fast response.
Please see the attachments.
Regards,
Parmod

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster March 29, 2021

Hello, Parmod,
 
Thank you for providing us with the details.
 
We agree that lazy loading is a nice feature to have. Still, it is a complex feature, and our roadmap is full at the moment. Therefore, we are not planning to add it in the nearest future.
 
Still, we suggest checking if anything can be improved on the server-side.
 
For example, make sure you receive unique data from the data storage only once.
As far as we understand, you query the database every time Flexmonster makes a request.
Does this query return the whole dataset, which is split into pages afterward?
Or, maybe, the query returns only the part needed for the specific page?
 
Please make sure you do not query the database for the whole dataset every time Flexmonster requests a page. It would mean that only the part of the data is used.
 
Instead, we suggest querying the database only once (after the first request) and pass it to Flexmonster page by page.
Another approach is to query the database only for a specific number of records needed for the requested page.
In both cases, we suggest preparing the next page in advance while transferring the current one to the client-side. According to the provided waterfall chart, it would minimize the TTFB and increase speed up to two times.
 
Also, we want to draw your attention to the network speed demonstrated on the chart.
The approximate speed seems to be approximately 1-1.5Mb per second. Please note that regardless of whether pagination is implemented, this speed significantly affects the loading process.
 
We hope it helps.
Please contact us in case other questions arise.
 
Regards,
Illia

Public
Parmod March 30, 2021

Hello Illia,
Question: For example, make sure you receive unique data from the data storage only once.
As far as we understand, you query the database every time Flexmonster makes a request.
Does this query return the whole dataset, which is split into pages afterward?
Or, maybe, the query returns only the part needed for the specific page?
Answer: For ‘Flat Table View’ if the db have duplicate value then we need to show same value on UI to support the underline pivot value. We are doing everything with the help of query i.e Pagination, Pivot(cube) query.
And on the server side we are just generating the Flexmonster response in the the streaming manner and flushing the data to UI on the basis of records.

Statement: Please make sure you do not query the database for the whole dataset every time Flexmonster requests a page. It would mean that only the part of the data is used.
Answer: We are using Paginated SQL query to fetch the record. So we don’t need to fetch unnecessary data.

Suggestion: Instead, we suggest querying the database only once (after the first request) and pass it to Flexmonster page by page.
Another approach is to query the database only for a specific number of records needed for the requested page.
In both cases, we suggest preparing the next page in advance while transferring the current one to the client-side. According to the provided waterfall chart, it would minimize the TTFB and increase speed up to two times.
Answer: The database size is in Tera Bytes so caching the database response to server is not an option for us and we are getting multiple request from same user(browser) as well as different user. If we do something you suggested it will eat up entire resources and make the server slow or unresponsive. To counter the above scenario we are using prepared statements.

Statement: Also, we want to draw your attention to the network speed demonstrated on the chart.
The approximate speed seems to be approximately 1-1.5Mb per second. Please note that regardless of whether pagination is implemented, this speed significantly affects the loading process.
Answer: If we have a lazy loading feature I know the loading time will not be less even it will be more but we have multiple benefits
1. User do not need to wait for 20-30 min to view data. User have at least some data to view.
2. User might not see the entire data. So we have less server\database calls.
Or can we show data while it is loading rest of the pages from the server.
Regards,
Parmod

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster March 31, 2021

Hello, Parmod,
 
Thank you for the detailed response.
 
Our team understands the benefits of the pagination functionality for your case.
Still, we are not ready to provide you with any estimates due to mentioned obstacles.
 
Concerning the following question from your recent email: “Or can we show data while it is loading rest of the pages from the server” we want to explain that Flexmonster requires the whole dataset in order to compose the internal structure and render the grid afterward.
 
However, we want to suggest the workaround you may find useful for your case.
In your previous email, you mentioned that usually, only 2-5 first pages of the data are used.
Do you think you could request only the first 2-5 pages from the database and pass them to Flexmonster as if it was the whole dataset? In case the user needs the rest of the data, he can request the entire dataset/its bigger part manually.
 
It is even possible to allow choosing the number of records before requesting the data.
The required number of pages/records can be inserted in the request made by Flexmonster with capabilities of the customizeAPIRequest method or sent as a preflight request.
 
Please let us know if you find this workaround useful.
Do not hesitate to contact us in case other questions arise.
 
Regards,
Illia

Please login or Register to Submit Answer