asked on August 13, 2021
Hello there,
We are considering purchasing Flexmonster component for use on a customer.
The amount of records we need to query is very large, over 1,000,000 records.
In addition, the number of columns returned is also large, around 60 columns.
Today we have an C# API that queries a single SQL SERVER table and returns all that data in JSON format for displaying.
Is there any performance gain in Flexmonster when using the "Data Server" component to perform this same query on SQL SERVER?
⋅ August 16, 2021
Hello,
Thank you for contacting us.
When the Data Server is not used, Flexmonster loads the exported JSON data directly from the server. In this case, all the data (1M records, 60 columns) are loaded to the client-side and processed at once. It means Flexmonster waits for all the data to be passed over the network, then it processes the data using the client's machine capabilities. The mentioned data set size may turn out too big for an average client's machine. We recommend directly connecting to JSON when the data set size is less than 100-150 MB.
In its turn, Flexmonster Data Server serves as a middle layer between your data storage (Microsoft SQL Server) and client-side Flexmosnter component. It performs all the heavy operations (aggregating, filtering, sorting, etc.) on the server side. Next, the ready-to-show data is sent to the client and displayed instantly.
It is also important to note that only the data that needs to be rendered on the grid is sent over the network. It means Flexmonster does not need to wait for an entire data set and perform all the heavy calculations on the client side.
This approach allows operating with up to ten times bigger data sets than direct JSON connection.
Moreover, Flexmonster Data Server works with Microsoft SQL Server database out of the box and can fetch the data directly without the current C# tool mentioned in your question.
We suggest checking out our documentation dedicated to Connecting to a Microsoft SQL Server database using Flexmonster Data Server.
Please let us know if any additional questions arise.
Best regards,
Illia
⋅ August 16, 2021
Thanks for your response Illia!
I did the tests with de Data Server (as a windows service):
On the Query field i did the "select * from MY_VERY_BIG_TABLE". This takes a LOT of time to complete. When I save the index, Data Server fetches all records from my table to the memory (or whatever)!
When my users are using the pivot table, the data needs to be updated as the user presses F5.
Using the Data Server as a service this was not possible, even when i configured the refresh time to 0. Every time i did the F5 on the browser, the response from data server was fast and the data displayed was outdated.
I've tried to use the Data Server as a DLL as well, to use the Data Server filter, but the first time the user opens the pivot table, the query "select * from MY_VERY_BIG_TABLE" runs and takes REALLY long time to complete. The filter was not used (the method "_apiService.GetFieldsAsync(request);" inside "public async Task<IActionResult> PostFields([FromBody]FieldsRequest request)" does not accept filter.
After an F5 is pressed, the response was fast and my data was outdated.
Questions:
⋅ August 17, 2021
Hello,
Thank you for your feedback.
Please note that Flexmonster Data Server fetches the data from your database and stores it in the RAM of its host. The data will be reloaded according to the refresh time. If refresh time is 0 or undefined, the Data Server will not reload the data.
It takes some time to fetch the data from the data source and process it, especially when operating with large datasets. This is why the first launch of the Data Server or further reloads may take a long time to complete. However, after completion, this allows all the requests from the client to be performed fast.
This approach also exposes limitations on the updating process. Flexmosnter Data Server does not fetch the latest data from the database every time a request is made (F5 in your case).
Updating the data every minute (the smallest possible value for refresh) also may not be an option since reloading takes a long time to complete.
Therefore, we suggest considering one of the following approaches.
The first one is less complex. It implies using a direct JSON/CSV connection instead of the Data Server. You will need to implement a proxy that would fetch the latest data from your database and return it to Flexmonster every time user performs a request.
This approach exposes limitations on the dataset size mentioned earlier (100-150 MB). You can prefilter data on the server to match this limitation.
The second approach is more complex and implies developing custom implementation of the custom data source API. Flexmonster custom data source API is our custom communication protocol that allows you to retrieve already aggregated data from a server to Flexmonster Pivot. This protocol is used by Flexmonster Data Server as well.
You can use it to develop a custom application that would receive client's requests, fetch the latest data, process it, apply filters/aggregations/sorting/etc., and send the data back to Flexmonster.
You can find more information about this approach in our documentation: Introduction to the custom data source API. You will also find .NET and Node.js samples of the implementation we developed for reference.
Please let us know if additional questions arise.
Kind regards,
Illia
