TCachedDatasource

[Ian Branch 131]

Hi Team,
Background:  
My Pacific Island Customer is still in the 'Dark Ages' when it comes to his Network/Workstation environment.  Old HP Server with Win Server 2012 r2 64bit.  Two HDD in Raid 1 but one is 3Gb/s the other is 6Gb/s. 😞 
His LAN workstations are old as well, running Win 7.  
Users can also access my Delphi database apps on his Server via RDP and/or via the Web using Cybele's ThinFinity.

The Database engine is ElevateSoft's ElevateDB 64bit.
His philosophy is, 'If it ain't broke, don't fix it.'.  To which I respond, 'If it ain't as good as it can be, then it's broke'.   
He just doesn't want to spend any money on it unless he absolutely has to.
Anyway, with 26+ Users and multiple multi-User Apps, things tend to bog down.  Surprise!!

 

Idea:
As he won't spend money at his end, what can I do application wise to improve the apparent database performance and therefore the User experience?
Right now you have many ideas going through your mind... 😉
What I decided, as much for the perceived need as for an exercise, was the concept of a Cacheing TDatasource.  TCachedDatasource.

 

Development:
With the assistance of two Associates, Claude & ChatGPT 4o, I developed and put the design concept/spec to them.
They each came back with a proposal which I vetted and selected one as the most promising.  Also the one I could understand. 😉
So, between the three of us, playing one against the other and back, we developed a basic component that worked.
We then refined it further, eliminating some errors, optimising some areas, until I was generally satisfied and it appeared to work.
Replaced the existing TDatasource components in one of my apps with the new TCachedDatasource and tested general functionality.  All good at this stage.
So, keeping this 'original' code aside, I put it to them and raised questions about various scenarios.  i.e. Autoinc fields, searching, filtering, etc.
The component was slowly refined.  Playing each of the AIs against each other for issues/refinements.
The overall priority was Data integrity.  No loss or corruption of data.  This to the detriment of any other functionality.

 

Result:
After a day of interaction we have what is attached here.
Note some additional properties:
1.    Active:-    True (default) - the caching is turned on.  False - The component acts like a normal TDatasource.
2.    CacheSize:-    This is the number of records to cache.  This is associated with VisibleRecords.
3.    EnableLogging:-    False (default) - No logging.  True - Logging enabled.  A text Log will be sent to what is defined in the LogFileName property.
4.    LogFileName:-    Full path/name of the Log file for logging.  If only a Name is entered it will be written to the Apps directory.  If there is no entry here then EnableLogging is ignored.
5.    RefreshInterval:-    The interval, in seconds, that the cache will be refreshed. Default is 60 seconds.
6.    VisibleRecords:-    The number of visible records that a DBGrid may be showing.  This feeds back to the CacheSize and allows dynamic adjustment if the DBGrid height is adjusted and therefore shows more, or less, records.

 

Conclusion:
As I said, this was as much a desire to improve things for the User, as it was an exercise for myself using AI.
I'm sure it isn't perfect, and could do with some tweaks here and there.  Feel free.  If you find anything notable and fix it, please publish it here, I'm sure others will appreciate your submissions.
I haven't done any performance testing.

This was developed in Delphi 12.2.  I think it should be good back to XE2.

 

Learnings:
There is a lot that goes into something like this when multiple factors need to be considered.
Claude tends to forget existing code when proposing/integrating new code suggestions.
Claude was/is limited in the 'length' of Chats.
ChatGPT was reasonable but not as code particular as Claude.
Don't do this again. 🙂

 

I hope somebody finds this of some use, even if it is as an exercise in how not to do it.

Feel free to can the whole idea.  🙂

 

P.S.  As this was a Human/AI collaborative effort, who owns the IP?? 😉

 

Regards,

Ian

uCachedDataSource.pas

Edited February 10 by Ian Branch

[Anders Melander 1894]

1 hour ago, Ian Branch said:

even if it is as an exercise in how not to do it.

https://docwiki.embarcadero.com/Libraries/Athens/en/Data.DB.TDataSource

Quote

TDataSource provides an interface between a dataset component and data-aware controls on a form.

 

TDataSource is the wrong layer for something like caching. You need to sit between TDataSource and TDataSet.

What happens if you have two datasources connected to a single dataset and one of them modifies the data? Now you have two different views of what the data looks like...

 

What is the point of spawning a thread to update the cache if all the thread does is call Synchronize to update the cache from the main thread?

 

I know ElevateDB has had resistance against FireDAC in the past but if they have gotten wiser, and support FireDAC now, then you could have used one of the FireDAC datasets. They all support caching and briefcase mode. TClientDataSet could also have been used.

[Ian Branch 131]

Hi Anders,

See, I knew alternatives would be suggested.  🙂

16 minutes ago, Anders Melander said:

What happens if you have two datasources connected to a single dataset

Until refresh.

 

20 minutes ago, Anders Melander said:

support FireDAC now

I don't believe so.

20 minutes ago, Anders Melander said:

What is the point of spawning a thread to update the cache if all the thread does is call Synchronize to update the cache from the main thread?

I did say it wasn't perfect... 😉

 

[Anders Melander 1894]

21 minutes ago, Ian Branch said:

See, I knew alternatives would be suggested. 

https://meta.wikimedia.org/wiki/Cunningham's_Law

[Die Holländer 67]

In my experience, outdated hardware can cause databases or executables to run slowly,

regardless of how well the software is programmed or optimized. No matter how much

you try to increase speed through software changes, upgrading the hardware can

significantly enhance performance.

[dummzeuch 1559]

14 hours ago, Ian Branch said:

P.S.  As this was a Human/AI collaborative effort, who owns the IP?? 😉

Just assume it's your IP, the company behind the AI will steal it anyway (actually it probably already has, because you pasted it into the chat).

[Tommi Prami 138]

Yellow,

I was pondering have you measured in some way will this actually speed things up.

-Tee-