Semi-Static Data Conservation Strategy

Question

I am working on a winform application that will be used by groups in the US and abroad, which replaces an existing application based on older technologies.

The performance of the old application abroad is rather slow due to excessive calls to the database server in the USA, so I would like to cache as much as possible on the client.

What is the best way to cache data for things that don't change very often, but can? How to save data due to fatigue without pulling it completely to each load?

Answer 1

A quick fix would be to create some kind of checksum for the data, and the client first requests it. If this is the same, then no more data is required, this part is updated. If the data changes, the amount changes, and the customer receives a new amount along with the new data.

The transfer of only what it seems every time that it should be less than all of your data.

Answer 2

If your data rarely changes, you can use the sql trigger to set the equivalent of the "updated" flag, and then simply map this flag to any other client-server message. If you add add / update / delete triggers to all semi-static tables and one watchdog table, you should be good to go.

Answer 3

You do not know what the data sets may look like, but how to calculate the hash as SHA256 for the data set and store this hash on the client and server. Then, before extracting from the server, we extract only the hash and compare it with the local hash on the client. If the hash match then uses a locally stored dataset.

Answer 4

You can save a function or view of an SQL table or something else light that can have a watermark or other indicator for a given set of objects that can report a revision identifier or 0 for unchanged, 1 for changed, and then only update objects which need updating.

Answer 5

I think the best approach would be to create a separate table for tracking data. The table will only have a list of table names and the last time this table was changed. Then you will create a trigger that will update the fields whenever an insert / update / delete occurs in this table. When the client wanted to check the data, you should get the data and get the date from this table and save it. The next time the client needs data, just find the table name in this table, get the date and compare it with the one you had. If the database has more, then the time to retrieve from the server.

The only drawback of this approach is that whenever you change the table, you will go to the database and get all the data again, even if this change does not affect your specific SQL query. This does not really matter if the data does not change often.

Answer 6

If the application itself can determine if there was a data update, scaling the delay of the request may be the easiest. When the application starts, request data, and then request again after a minimum update delay. If the data has not changed, double the update delay between requests. Continue to double the delay if no changes are detected (possibly with the maximum limit) and you are ready to go. This is a common template to reduce server load and it is very easy to implement the client part without changing your scheme or web service.

Answer 7

You must save the timestamp whenever the database or table you want to cache. If there are linked tables or those that change to a similar timeframe, you can group them into families that have the same timestamp.

Whenever you need data, you must first request a timestamp. If it has not changed, use cached data. If it has changed, ask for fresh data, update your cache and request the timestamp of all ancestor data recursively.

The checksum does not work, because they can collide, and you will have to ask for fresh data in any case to check for collisions. The old / changed flag will not work, because you won’t know when the data was changed, it could be before your previous timestamp. A version identifier that always increases after each change and never decreases will also work.

If you use the RESTful web service, the GET HEAD code will only return you an HTTP header, which may include a timestamp. If your web service does not include timestamps, you will need to identify new ones that will return them.