How to deal with a very big datasets?

[estrify]

Dear UniGUI team,

I have a problem that I need your advice on how to tackle it.

I have a good set of queries that can generate a quite big rows of results… After doing all server-side filtering (in the SQL), depending on user profile but the “most powerful” profile can receive about 300.000 registers with 30 columns (from tables with millions of entries).

I think the normal sequence in a UniGUI project is the following:
1.    MySQL Server executes the query and returns it to UniGUI server. As I can only use client side cursors with UniGUI controls, this huge amount of data travels from MySQL to UniGUI servers, consuming network resources and time.
2.    UniGUI server sends much less amount of data to UniGUI client with a tipical paged DBGrid, so this is done quickly. Filters and sorters of a DBGrid manage temporarily stored data in UniGUI server, so those operations are very fast.

So, the problem is the point 1. Is there a way to use server cursors and avoid this traffic of data from MySQL to UniGUI server?. I think I can’t but I am not sure if this is right or not: with server cursors, UniGUI components can’t use tools like “Recordcount”, so a DBGrid can’t use, for example, its classic pagination system, neither client side filtering, sorting, etc.

Is there a way to configure a DBGrid to work properly with server cursors?. If so, this will save my life, temporarily, because it will give me some time to develop the following (if it is not done within UniGUI, but I think not):

• To create a custom system to replace a large handful of very useful features of UniGUI's DBGrid: pagination/infinite scrolling, grid filters and sorters. That is, using a DBGrid without paging and implement a totally new paging system doing "SELECT COUNT()" to retrieve the number of rows, use my own controls to do pagination and link it to SQL's "LIMIT OFFSET, ROWS" to retrieve only one desired page. And, obviously, disable all client side filtering and sorting and replacing it with server side filtering and sorting.

I am sure that I am not the first guy with this kind of problems, and may be UniGUI can be configured to work this way, but I don't know how. So if you have indications/sample projects/tips/best practice/etc. to do so, I will be very grateful.

Best regards,

 

[rhazell]

On 6/10/2021 at 12:57 PM, estrify said:

Dear UniGUI team,

I have a problem that I need your advice on how to tackle it.

I have a good set of queries that can generate a quite big rows of results… After doing all server-side filtering (in the SQL), depending on user profile but the “most powerful” profile can receive about 300.000 registers with 30 columns (from tables with millions of entries).

I think the normal sequence in a UniGUI project is the following:
1.    MySQL Server executes the query and returns it to UniGUI server. As I can only use client side cursors with UniGUI controls, this huge amount of data travels from MySQL to UniGUI servers, consuming network resources and time.
2.    UniGUI server sends much less amount of data to UniGUI client with a tipical paged DBGrid, so this is done quickly. Filters and sorters of a DBGrid manage temporarily stored data in UniGUI server, so those operations are very fast.

So, the problem is the point 1. Is there a way to use server cursors and avoid this traffic of data from MySQL to UniGUI server?. I think I can’t but I am not sure if this is right or not: with server cursors, UniGUI components can’t use tools like “Recordcount”, so a DBGrid can’t use, for example, its classic pagination system, neither client side filtering, sorting, etc.

Is there a way to configure a DBGrid to work properly with server cursors?. If so, this will save my life, temporarily, because it will give me some time to develop the following (if it is not done within UniGUI, but I think not):

• To create a custom system to replace a large handful of very useful features of UniGUI's DBGrid: pagination/infinite scrolling, grid filters and sorters. That is, using a DBGrid without paging and implement a totally new paging system doing "SELECT COUNT()" to retrieve the number of rows, use my own controls to do pagination and link it to SQL's "LIMIT OFFSET, ROWS" to retrieve only one desired page. And, obviously, disable all client side filtering and sorting and replacing it with server side filtering and sorting.

I am sure that I am not the first guy with this kind of problems, and may be UniGUI can be configured to work this way, but I don't know how. So if you have indications/sample projects/tips/best practice/etc. to do so, I will be very grateful.

Best regards,

 

Very good question... is what he suggests currently possible?  That is, can dbgrid pagination somehow be achieved via SQL? 

In short, what is the most effective way to deal with potentially large datasets returned via SQL?   

[风吹小机机]

The paging function can be realized with firedac

[rhazell]

Yes, that's sort of the point... however, If I am understanding correctly there's no ability to apply the paging managed by the uniDBGrid to the SQL of the server-side datasource.

1) The datasource is filled at on server-side (eg. using a SQL statement)

2) However, the paging implemented by uniDBGrid only control the subset of server-side records required for display on the client-side.

Am I misunderstanding the process - I'd like to hear other opinions, please? 

[estrify]

6 hours ago, rhazell said:

Yes, that's sort of the point... however, If I am understanding correctly there's no ability to apply the paging managed by the uniDBGrid to the SQL of the server-side datasource.

1) The datasource is filled at on server-side (eg. using a SQL statement)

2) However, the paging implemented by uniDBGrid only control the subset of server-side records required for display on the client-side.

Am I misunderstanding the process - I'd like to hear other opinions, please? 

Please take a look to the post where I tried to explain the need for a grid that works in a different way, to be able to do things similar to what other professional products do…

Regards,

 

[rhazell]

2 minutes ago, estrify said:

Please take a look to the post where I tried to explain the need for a grid that works in a different way, to be able to do things similar to what other professional products do…

Regards,

 

Thanks, I have and I agree with your assessment. I kind of re-asked the question mostly to see if there's been any recent changes to uniGUI to address this functionality. 

[Stemon63]

I seem to understand that the problem, which is the same as I raised for other situations, concerns the optimization of resources and especially of communications between client and server.
There are "enterprise" situations in which it is necessary to avoid that large masses of data pass through a dataset and then into the client grid, when a direct call from the client could be made to a service that would return the same data WITHOUT filling the server memory . Especially in situations where there are many users connected, with the result of "killing" the server resources.
I myself also asked for the possibility of being able to carry out a sort of "cached update" on the client side, so as to be able to send all the changes to the server in "one shot" (ApplyUpdate) so as not to have to chat with the server for each modification made to each record in the grid, especially in applications where user-machine interaction is very fast and intense.
Clearly we are talking about optional client features and complementary to the current "servers" already available that works wonderful.
And of course we are talking about situations in which there is a high number of users connected at the same time.
Basically we need a client-side dataset (with cached update) filled from an external  json. Exactly like what TMS Webcore owns with its client dataset.  I already now get it manually through store management, but something official is needed for better result.
We need to add a Pascal to JavaScript transpiler Pas2js to UNIGUI, so we can handle everything well on the client side too? 🙂

[JarekZ]

Hello

I solved it on the SQL server side. Procedures return only the data package that is to be displayed. DBGRID has been replaced with HTMLFrame + bootstrap-table. The size of the tables is about 30 million records. it works very well in my applications.

[rhazell]

16 hours ago, JarekZ said:

Hello

I solved it on the SQL server side. Procedures return only the data package that is to be displayed. DBGRID has been replaced with HTMLFrame + bootstrap-table. The size of the tables is about 30 million records. it works very well in my applications.

Would it be possible to share an example? Sounds intriguing! 

[JarekZ]

the basis is a procedure in the database simple example for MSSQL:

create procedure Orders.Listaproduktow (@userid int, @ startindex int, @ countindex int, @ filter varchar (50))

as

begin

Select * from Orders.products where name like @filter

offset @startindec rows fetch next @countindex rows only;

end

Support for the @startindex variable in the application - increases or decreases by @countindex

Bootstrap - added Ajax support which returns which page it wants to display.

the example of the procedure is trivial but it is the key to speed.

Well indexed table, proper SQL query, and it works very well.

Of course, if you set @countindex to 100000, it doesn't make sense. I have @countindex set from 50 to 200

[rhazell]

7 hours ago, JarekZ said:

the basis is a procedure in the database simple example for MSSQL:

create procedure Orders.Listaproduktow (@userid int, @ startindex int, @ countindex int, @ filter varchar (50))

as

begin

Select * from Orders.products where name like @filter

offset @startindec rows fetch next @countindex rows only;

end

Support for the @startindex variable in the application - increases or decreases by @countindex

Bootstrap - added Ajax support which returns which page it wants to display.

the example of the procedure is trivial but it is the key to speed.

Well indexed table, proper SQL query, and it works very well.

Of course, if you set @countindex to 100000, it doesn't make sense. I have @countindex set from 50 to 200

Hello and thanks for the reply.  I like & understand the concept you've presented (especially the SQL part), however, it's the bootstrap implementation I'm unfamiliar with... do you have a demo for that?  I'd be willing to pay for a small demo - if you're interested, please contact me at rhazell_at_microcalm_dot_com

[JarekZ]

I am attaching the project. I apologize for its quality but I wrote at lunch;) you need to set up a database in MSSQL, set up a table and create a procedure. There is table.sql for that Connecting to the database in the config.ini file I have a test database with about 10 million records, I can send it.

Mormot v1.18 is required for json support

 

DemoUni.zip

[rhazell]

Many thanks... I shall take a look. 

[Norm]

I agree with Jarekz. This is a situation where the uniGrid approach is the wrong solution and HTML + Javascript is the best approach. Not only does this provide the most flexible solution, given your special requirement, it also gives you access to a wealth of free jQuery based online tools (in addition to Bootstrap).  You obviously need Javascript knowledge to use this approach, which is relatively easy for a Delphi programmer to acquire and probably the best investment for any unigui programmer. There is also a huge community of developers on platforms like StackOverflow who are very keen to help and would provide more useful ideas to questions like "how to deal with large datasets".

I noted that Jarekz's example merges the json data into the html script before displaying the page. You can also use ajexRequest to retrieve the data from the server after displaying the page (while displaying some kind of "waiting for data..." widget).