Plz kindly confirm my understand of client caching, thanks :)

edwinsn · 2016-12-11 10:05:34

@Ab,

Assume I want to cache all records for a specified table on the client side, pseudo code:

myRestClient.SetCache(TSQLMyTable);

Up to this point, there are not real cached records in the memory yet, until any calls to Retrieve or CreateAndFillPrepare, right?

Now assume the following code is executed:

  myRestClient.Retrieve('ID > 0', myRec);

At this point, all records for TSQLMyTable are cached now. Am I understanding it correct?

Thanks.

Last edited by edwinsn (2016-12-11 14:22:41)

ab · 2016-12-11 19:15:03

Only TSQLRecord retrieved BY ID are cached by SetCache().

But there are other levels of cache, please check the docs.

edwinsn · 2016-12-12 07:17:17

Thanks ab, I did read the doc before I'm asking the question here, it's just wasn't very clear to me.

From the doc:

Once enabled for a table and a set of IDs on a given table, any further call to TSQLRest.Retrieve(aClass,aID) or TSQLRecord.Create(aRest,aID) will first attempt to retrieve the TSQLRecord of the given aID from the internal TSQLRestCache instance's in-memory cache, if available.
Note that more complex requests, like queries on other fields than the ID primary key, or JOINed queries, won't be cached at REST level. But such requests may benefit of the JSON global cache, at SQLite3 level, on the server side.

So it's not possible to read all records from a table into the cache on the client side, in a single network round-trip (using 'ID > 0' as the retrieving conditions)? because when retrieving with a single ID, you read only a single record at a time.

My intention is to load an entire table into the RAM, to avoid a lot of "retrieve one record a time" operations, to speed things up.

Last edited by edwinsn (2016-12-12 07:34:48)

ab · 2016-12-12 08:28:44

Then, do it just like as said: load an entire table into RAM.
You may use either a TSQLRecord list, a TDocVariantArray, a JSON buffer, or a TSQLRestStorageInMemory.
The later storing internally a TSQLRecord list, and having TSQLRest basic commands available, including the Retrieve(ID) method which will be instant.

But a new cache-dedicated command, to actually add a set of the cached data in the cache, may be of some interest, as you propose.

edwinsn · 2016-12-12 09:16:25

@ab, Yes, I'm currently doing it with TDictionary<TSQLMyRecord>, it's already very handy and useful, I just was wondering if mORMot's built-in caching ability might be more handy - it'll make the cache completely transparent to my code, in other words, my code won't have to care about both the self-implemented in-memory TDictionary and TSQLRestClient - all it needs is interfacing with TSQLRestClient

Last edited by edwinsn (2016-12-12 09:17:46)

ab · 2016-12-12 10:42:40

Could you try the new TSQLRestCache.FillFromQuery() method as implemented in http://synopse.info/fossil/info/8a4e3b1dbf ?

Not fully tested yet, so your feedback is welcome!

edwinsn · 2016-12-13 10:29:39

Wow! Will do the test soon, thanks Arnaud!

edwinsn · 2016-12-15 16:18:56

@ab,

Tested TSQLRestCache.FillFromQuery inside a local network, the result is great - went from 6,373 MS to 40 MS.

Result:

Test loading records one by one from db - non-cached:
6,373 MS = 0:0:6

Destroyed server-side caching

Constructing client cache:
100 MS = 0:0:0

Test loading records one by one from db - cached:
40 MS = 0:0:0

Testing code:

procedure TfrmDebug.TestClientCaching;
var
  Elapsed: TTimeSpan;
  myStr: string;
  newProd: TSQLProduct;
  prodList: TSQLProduct;
  stopWatch: TStopwatch;

  procedure TestReadProdFromDbOneByOne;
  begin
    prodList.FillRewind;
    while prodList.FillOne do
    begin
      AppCtx.Db.Retrieve(prodList.ID, newProd);
    end;
  end;
begin
  //memCache.Clear;
  AppCtx.Db.ServerCacheFlush;
  AppCtx.Db.Cache.Clear;
  prodList := TSQLProduct.CreateAndFillPrepare(AppCtx.Db, 'ID > 0');
  GC(prodList);

  newProd := TSQLProduct.Create;
  GC(newProd);

  // test without caching;
  memCache.Lines.Add('Load one by one from db - non-cached');
  stopWatch := TStopwatch.StartNew;
  TestReadProdFromDbOneByOne;
  Elapsed := Stopwatch.Elapsed;
  memCache.Lines.Add(FormatUTF8('% MS = %:%:%', [HumansInt(Trunc(Elapsed.TotalMilliseconds)), Elapsed.Hours, Elapsed.Minutes, Elapsed.Seconds]));

  memCache.Lines.Add('Destroying server-side caching');
  AppCtx.Db.ServerCacheFlush;
  AppCtx.Db.Cache.Clear;

  //load the all products into client cache
  memCache.Lines.Add('Constructing client cache');
  stopWatch := TStopwatch.StartNew;

  AppCtx.Db.Cache.SetCache(TSQLProduct);
  AppCtx.Db.Cache.FillFromQuery(TSQLProduct, 'ID > 0', []);

  Elapsed := Stopwatch.Elapsed;
  memCache.Lines.Add(FormatUTF8('% MS = %:%:%', [HumansInt(Trunc(Elapsed.TotalMilliseconds)), Elapsed.Hours, Elapsed.Minutes, Elapsed.Seconds]));

  // test with caching;
  memCache.Lines.Add('Load one by one from db - cached');
  stopWatch := TStopwatch.StartNew;
  TestReadProdFromDbOneByOne;
  Elapsed := Stopwatch.Elapsed;
  memCache.Lines.Add(FormatUTF8('% MS = %:%:%', [HumansInt(Trunc(Elapsed.TotalMilliseconds)), Elapsed.Hours, Elapsed.Minutes, Elapsed.Seconds]));

  memCache.Lines.Add('=============================');
end;

edwinsn · 2016-12-15 16:23:51

PS, the call of AppCtx.Db.Cache.SetCache() shouldn't necessary before FillFromQuery() is called, right?

I wasn't aware it's necessary until I debugged into the source of FillFromQuery - open source is soooooooo great

ab · 2016-12-15 20:57:01

Yes you need to enable the cache to use the cache....

mine1961 · 2016-12-16 09:21:25

Hi Arnaud!

I'm just reading this conversation.
At this moment I'm setting up an application which should run on mobile devices using FMX.
Unfortunately at least now components like TSQLRecord or TSQLRestCache won't work when compiling for Android or iOS.
Are there any plans for cross platform units?

Thanks Michael

edwinsn · 2016-12-16 16:03:24

@mine1961, I don't know if caching is also enabled in the cross-platform units, but just in case you missed the related chapter: http://synopse.info/files/html/Synopse% … DE_TITL_86

@ab,
Is there a way to **partially** update the cache, for example, partially update the existing cache with records modified since a given time (the time the client cache was created previously). I'll illustrate the idea will some pseudo code:

var
  //global var
  gCacheTime : TDateTime;

procedure ConstructCache;
begin
  gCacheTime := Now;
  myDb.Cache.SetCache(TSQLMyTable);
  myDb.Cache.FillFromQuery(TSQLMyTable, 'ID > 0', []);
end;


// how to achieve the following?
procedure UpdateCache;
var
  onlyNewRecords: TSQLMyTable;
begin
  // retrieve records only those updated since the last time the client cache was updated.
  myTable := TSQLMyTable.CreateAndFillPrepare('LastUpdateTime > ?', [gCacheTime]);

  myDb.Cache.UpdateFromQuery(onlyNewRecords);
end;

I wish my propose makes sense

mORMot Open Source

#1 2016-12-11 10:05:34

Plz kindly confirm my understand of client caching, thanks :)

#2 2016-12-11 19:15:03

Re: Plz kindly confirm my understand of client caching, thanks :)

#3 2016-12-12 07:17:17

Re: Plz kindly confirm my understand of client caching, thanks :)

#4 2016-12-12 08:28:44

Re: Plz kindly confirm my understand of client caching, thanks :)

#5 2016-12-12 09:16:25

Re: Plz kindly confirm my understand of client caching, thanks :)

#6 2016-12-12 10:42:40

Re: Plz kindly confirm my understand of client caching, thanks :)

#7 2016-12-13 10:29:39

Re: Plz kindly confirm my understand of client caching, thanks :)

#8 2016-12-15 16:18:56

Re: Plz kindly confirm my understand of client caching, thanks :)

#9 2016-12-15 16:23:51

Re: Plz kindly confirm my understand of client caching, thanks :)

#10 2016-12-15 20:57:01

Re: Plz kindly confirm my understand of client caching, thanks :)

#11 2016-12-16 09:21:25

Re: Plz kindly confirm my understand of client caching, thanks :)

#12 2016-12-16 16:03:24

Re: Plz kindly confirm my understand of client caching, thanks :)

Board footer