Performance improvement of client server system

Question

I have a legacy client server system where the server maintains a record of some data stored in a sqlite database. The data is related to monitoring access patterns of files stored on the server. The client application is basically a remote viewer of the data. When the client is launched, it connects to the server and gets the data from the server to display in a grid view. The data gets updated in real time on the server and the view in the client automatically gets refreshed.

There are two problems with the current implementation:

1. When the database gets too big, it takes a lot of time to load the client. What are the best ways to deal with this. One option is to maintain a cache at the client side. How to best implement a cache ?

2. How can the server maintain a diff so that it only sends the diff during the refresh cycle. There can be multiple clients and each client needs to display the latest data available on the server.

The server is a windows service daemon. Both the client and the server are implemented in C#

Answer 1

• Could put a date/timestamp (indexed) on the data and then load the data > last successful timestamp.

• Load data in pages so you get a quicker startup and then load the rest in the background.

Answer 2

If you go for the "work offline" (cached) solution then you should take a look at the MS ADO.NET Sync Framework. It supports providers and solves most of the hard problems with synchronizing data.

Another option is to retrieve only selected columns, like primary key and a single descriptive column. The remaining data could be lazy-loaded on demand, such as when it scrolls into view or is being accessed.