1

A client wants to build a worksheet-like application to show data from a database (presumably on a TDbGrid or similar), allowing free search and edition of all cells, as you would do in a worksheet. Underlying table will have more than 100k rows.

The problem with using TClientDataset, is that it tends to load all data into memory, violating user requisites, that are these 3:

  1. User will be able navigate from first to last record at any moment using scroll bar, keyboard, or a search filter (note that TClientDataset will load all records if you go to last record, AFAIK...).

  2. Connection will be through external VPN / internet (possibly slow), so only the actual visible records on screen should be loaded. Never all.

  3. Editions must be kept inside a transaction, so they can be committed or rollbacked at the end, and reconciling if necessary.

Is it possible to accomplish this 3 points using TClientDataset? If not, what are the alternatives?

oscar
  • 355
  • 1
  • 2
  • 13
  • 1
    Nope. The nature of a TClientDataSet is that it is an **in-memory table**, which means that you're never going to satisfy conditions #1 or #2. – Ken White Jul 03 '19 at 20:09
  • 1
    A CDS tends to hit a performance brick wall when loading a few 10k's wortth of records. You may do better with FireDAC or even ADO, but be aware of this Q&A: https://stackoverflow.com/questions/33964886/why-does-scrolling-through-adotable-get-slower-and-slower – MartynA Jul 03 '19 at 20:13
  • 1
    A large number of records from a remote server is likely to be retrieved a lot faster as a JSON document (with gzip compression in use) containing all of them. Loading the records into something local to do the searching/filtering etc depending on what grid control your using any local databases available will be a lot more responsive than doing requests over a possible slow connection. – Brian Jul 03 '19 at 20:21
  • 2
    @Brian: How is that possible? If the remote data is in a DB already, you add the overhead of extracting the data as JSON, compressing it, transmitting it to the client, decompressing it, and importing it into a CDS. And then reversing the entire process to send changes back doubles that overhead. – Ken White Jul 04 '19 at 02:07
  • 1
    in order to fulfil #1 and 2 one will need to implement some kind of paging and/or incremental refresh, possibly using intermediate cds etc. it's not that it is impossible but there's no all required built-in machinery in cds – Vladimir Ulchenko Jul 04 '19 at 06:45
  • As @vavan comments, I hoped there would be a kind of CDS capable of dividing big results in smaller chunks, keep only the changes in delta, forget older records as the user scrolls, and finally reconcile. It amazes me that this need hasn't been raised and solved before. I will test with old style transactions, combined with subqueries to get smaller chunks, and may be a CDS to keep accumulated delta. – oscar Jul 04 '19 at 09:52
  • @oscar Firedac solved this by MaxRec and offset on Select queries, After all it is the future Emba trying to promote CDS are going to be forgot about in the near future. – Nasreddine Galfout Jul 04 '19 at 11:45
  • @KenWhite Over a slow connection with higher latency it often works out to being faster. High latency kills client/server style messaging used to access most databases directly and compression is usually better on large blocks vs smaller blocks (besides most databases don't compresses communications with clients.) Often you can get all the data in 1/10th the size with compressed (BSON/JSON or proprietary) vs a regular database client connection. – Brian Jul 04 '19 at 11:58
  • @oscar: some people solved it (at least partially) in their cds descendants but original cds/midas didn't get much love/attention from its maintainers so no ready to use solution I'm afraid – Vladimir Ulchenko Jul 04 '19 at 15:30

1 Answers1

1

I'm answering just by your last line regarding alternatives, I can add some suggestions:

1- You can use some creativity, provide pagination and fetch let's say 100 rows per page using a thread which is equipped with a nice progress bar in the UI. in this method you must manage search and filters by some smart queries, reloading data sometimes, etc...

2- Use third party components that optimized for this purpose like SDAC + EhLib Dbgrid. SDAC is a dataset that can be useful for cache updates and EhDBGrid has a MemTable component inside it which is very powerful, free search, fuzzy match or approximate search work nicely, possible to revert, undo and redo, etc...

Ali Dehban
  • 88
  • 1
  • 8