Devart Forums

Posted: **Thu 07 Oct 2010 14:52**

I'd like to use RefreshQuick to minimize traffic with the server. But I would like to add another layer of optimization, a cache of data.

My idea is that for every TMSQuery that can potentially return many records I use a cache mechanism.

I found a way to savetofile MSQuery1 using TVirtualTable and TCRBatchMove in this way:

Code: Select all

CRBatchMove1.Source := MSQuery1;
CRBatchMove1.Destination := VirtualTable1;
// I need to Copy FieldDefs
VirtualTable1.FieldDefs := MSQuery1.FieldDefs;
CRBatchMove1.Execute;
VirtualTable1.SaveToFile('Test.dat');

To Retrieve from File I do LoadFromFile and use CRBatchMove1 in the "opposite direction".

This works but it is very slow, I tried with 1000 records and it is much slower than retrieving data from a DB in LAN.

So QUESTION1: is this the best that can be done or is there a more efficient way to cache data locally?

Moreover To use RefreshQuick TimeStamp somehow is used, so I should SaveToFile also the TimeStamp info to allow to use RefreshQuick without calling Open.

TO summarize what I want to achieve is this (is it possible?):

1) RUN the application
2) as a TMSQuery needs to be opened I check if it has been cached (so if a file has been saved):
2a) if yes I load the data to the TMSQuery including timestamp info and then I call RefrshQuick
2b) if not I simply call TMSQuery.Open

Of course there are many issues, but those are not related to this question (like: MSQuery can have different Params, one MSQuery can have different SQL code accodrding to application logic, there can be security issues by caching critical data, ...).

NOTE: THe need of caching comes for the scenarios in which the connection to the server is very slow (users accessing with VPN from remote connection for example).

Posted: **Fri 08 Oct 2010 12:35**

Hello,

You can try creating a local copy of the database on a client machine and synchronize these databases with the help of the TCRBatchMove component like this:

Code: Select all

  CRBatchMove.Source := MSQueryServer; // query to the database on the server
  CRBatchMove.Destination := MSQuery2Local; // query to the local copy of the database
  CRBatchMove.Execute;

You can create a local copy of the database with the help of SQL Server Compact Edition and then use the TMSCompactConnection component to access that database.

Also you can try reducing the size of data that is passed from server to client. You can set the TMSQuery.FetchAll property to False and try to select the TMSQuery.FetchRows property value that gives you the best data receiving performance taking into account the speed of your connection to the server.

Devart Forums

Using RefreshQuick and TCRBatchMove to minimize traffic

Using RefreshQuick and TCRBatchMove to minimize traffic