Jump to content


Photo

UniDbGrid - fetching considerations

UniDbgrid Fetch Paging

  • Please log in to reply
4 replies to this topic

#1 arilotta

arilotta

    Active Member

  • uniGUI Subscriber
  • PipPipPip
  • 77 posts
  • LocationTrieste, Italy

Posted 10 January 2018 - 04:58 PM

Hi all,

as far as I have read from the forum the only way to deal with big datasets and uniDbGrids is paging.

This works quite well, but I would prefer another option, let me explain better.

 

Without paging, the web server fetches the whole dataset and it gives back it to the client:

 

DB server --> Web server (Unigui) : whole dataset

Web Server (UniGUI) --> Browser: whole dataset

 

This is the worst situation, as it implies a lot of overhead for all the actors (DB Server, Web Server, 

Browser, network, etc.). It should not be used for large datasets.

 

 

With paging, the web server fetches the whole dataset (this is why the option FetchAll shall be set),

and it gives back to the client (browser) just the current page. Therefore:

 

DB server --> Web server (Unigui) : whole dataset

Web Server (UniGUI) --> Browser: partial dataset/ curent page

 

This is better, as it minimizes the overhead on the client side (browser), but the Web Server (UniGUI) still

has to deal with a large dataset that has to be fetched entirely from the DB server and to be kept in memory.

 

 

The last option, that I really miss with UniGUI, is the way that VCL DBGrid works, but it is common to find it

on many websites. This is the reason why I'm asking about it, I'm confident there should be a way to work it out

with UniGUI.I think that it could be called "incremental fetch".

 

Practically, UniGUI should fetch just the first N rows and show them on the UniDbGrid component, letting the user know

that there are more rows than the ones actually visible. It could be a button on the paging bar called "Load more rows".

If the user clicks on that button, UniGUI should fetch N additional rows from the dataset, and then display N*2 total

rows. This approach can be iterated till the whole dataset is fetched.

Alternatively, without using the "Load more rows" button, it could be possible to do it in the VCL way:

when the user scrolls down the grid and he reaches the last line, the "give me more lines" could be automatically

fired.

The "incremental fetch" solution would be the best in my opinion, as the user does not really care to know the number of pages in the grid,

nor he needs to skip randonly on different pages. Usually he does want quickly the first N rows, and then the possibility

to look up another N rows for a fe times. Then, if the data is not found, he would change the actual filter/selection.

This would bring the following situation:

 

DB server --> Web server (Unigui) : partial dataset

Web Server (UniGUI) --> Browser: partial dataset

 
 
 
Sorry for the long post, hoping that I was clear, thank you all.
Andrea

  • 0

#2 Volk65

Volk65

    Member

  • uniGUI Subscriber
  • PipPip
  • 32 posts
  • LocationMSK, Russia

Posted 10 January 2018 - 06:35 PM

Hi!
Nice to see people who think about optimization.

Unfortunately (and I should know) it is not possible.
Data extraction deals with TDataSet. But this component does not have Paging.
FireDac, when properly configured, can extract data page by page, but there's still a Library (ADO), TDBF, and other data access components that have no idea about paged data retrieval.
And there are methods of Tdataset, which negates the whole optimization data, for example: 1) Locate the method that should go through the whole dataset (millions of records), 2) the method SetFilter, which should select only 100 records out of a million.
That is, to produce the final dataset component, the server must make a selection of all data.
It has nothing to do with UniGUI. If you can do paging sample in VCL, then it will turn out in UniGUI. But Grid has nothing to do with it. DbGrid does not have to know anything about the components, which provide him data.
Everyone can organize such, but it's almost impossible to make universal.

Sorry for my YandexTranslator-English, but I hope that main idea is clear.


  • 0

#3 arilotta

arilotta

    Active Member

  • uniGUI Subscriber
  • PipPipPip
  • 77 posts
  • LocationTrieste, Italy

Posted 11 January 2018 - 08:44 AM

Hi Volk65, I think you are right regarding the fact that not all TDataSet descendents support incremental fetching.

For example, the TClientDataSet or TVirtualTable, which are not bound to a DB server, obviously do not support it.

Even the general TDataSet does not support it.

But I think there are many other specialized descendent that support it instead.

I am using DevArt components to connect to Oracle DB, and I can finely tune the following fetching options:

 

- FetchAll (boolean: fetch all rows or not )

- FetchRows (number or rows to fetch in a bunch)

 

FireDac, as you say, can do it.

Implementing the "incremental fetch" with UniDbGrids, and using a TDataSet that does not support it, would give the same result

as today.

But if used with TOraQuery (or FireDac), would give the same user experience as VCL in a web application: amazing !

 

Of course using the Locate (or Last command) would break the rule, but it is something that the developer should be aware of,

as the behaviour is well known. It is sufficient to avoid local filtering options with big datasets and this kind of "incremental fetching grids"

(and reopen the queryinstead with changed conditions).

 

To make a comparison with VCL, it is the same as using the vertical scrollbar on a DBGrid filled with 1 million rows, and dragging it

to the bottom: the system hangs.

It is up to the developer to block such a bad behaviour.

 

Thank you for sharing your opinion.

Anyone else ?

Andrea


  • 0

#4 Stefano Monterisi

Stefano Monterisi

    Advanced Member

  • uniGUI Subscriber
  • PipPipPipPip
  • 121 posts

Posted 11 January 2018 - 09:43 AM

HI,

Farshad already known my ancient "partial fetching" request for Dbgrid; this one is the same but not only at Dbgrid visual level; Andrea want to optimize record's number present in memory.

The problem is that Tdataset  partial fetchrow add records fetched to others already fetched scrolling the dbgrid; so, If I go to last records, all records are fetched :-)

The best idea is (as devexpress servermode) that records in memory are always the twice of row visible in grid. So I have always 100 records in memory (dbgrid with 50 rows), even if I go to the last records....
But this involve a quering mechanism at sql level for paging...

;-)

 
 


  • 0

#5 arilotta

arilotta

    Active Member

  • uniGUI Subscriber
  • PipPipPip
  • 77 posts
  • LocationTrieste, Italy

Posted 11 January 2018 - 11:03 AM

Hi Stefano, I see your concerns about scrolling (down) the grid and the consequent fetching of additional rows, but this should be limited in one of the following ways:

 

- the users scrolls down the grid to the last row, and they can fetch and additional page (N rows) using a dedicated button "Load more rows" that could be 

  placed on the navigation bar, instead of the current previous/next/first/last page buttons

- the user scrolls down the grid to the last row, and automatically (ajax event ?) and additional page (N rows) is fetched from the opened query and added to the UniDbGrid

 

So no way for the end user to move to the last row and fetch all the rows from the dataset as a whole.

After a few page fetches without finding what they are looking for, the users would tipically refine the filter to get the data he really wants.

Memory occupation, CPU, network usage minimized on the DB side and web server (UniGUI).

 

Andrea


  • 0





Also tagged with one or more of these keywords: UniDbgrid, Fetch, Paging

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users