Improve multiple synchronized access to data

Question

At the beginning I would like to apologize for the excessive use of bold text. I wanted to simplify the understanding of the way my long questions.

I have a problem with multiple access to the data.

I have to implement following problem: I have a server application SERVER_A (based on TIdServer) with many (about 100) TCP connections. The goal of each of these connections is:

• Get status message (every 2-5s) from CLIENT. Status contains 3 double numbers stored as text.
• Send answer from SERVER_A to CLIENT- after getting status

These statuses (about 100) have to be saved in the Firebird database table - TABLE (I use to do this AnyDAC). Each CLIENT has its number and its record in TABLE.

Three another local network client applications - LOCAL_APPLICATIONS have to have acces to TABLE too. They have to visualize statistics data from TABLE.

LOCAL_APPLICATIONS sometimes have to change one of the record of the TABLE. Results this changes have to be delivery to the right CLIENT via TCP connection of SERVER_A as a respond.

The problem is in the efficiency of such a solution is reached.

So far, every TCP calls write data separetly to TABLE causing massive overload Firebird and any of the operations doing by LOCAL_APPLICATIONS are very slowly.

Therofore could I ask:Is the correct approach is to build a LOCAL_ARRAY in SERVER_A and collect the data from all TCP connections and save all of them together periodically at fixed time intervals (for example every 2 seconds) in the TABLE? If so, how to make efficient data synchronization: time to change the lock on the LOCAL_ARRAY and lock it during time reading the data from it to save the data in TABLE?

Maybe better is too use sigle variable for each connection status then use LOCAL_ARRAY to avoid locking every fields in LOCAL_ARRAY during inserting data from one CLIENT? But it is not flexible solution.

Is my idea is good to increase efficiency? Or rather not? What could be better solution?

Regards Artik

Answer 1

Take a look at the CQRS pattern.

CQRS stands for Command Query Responsibility Segregation. At its heart is a simple notion that you can use a different model to update information than the model you use to read information. This simple notion leads to some profound consequences for the design of information systems.

From my experiment, this is a very good pattern for implementing scaling and responsiveness in a Client-Server architecture.

You can use a separate database (in-memory if possible) for the queries, then have a command-driven bus for writing the data.

This is very close to what you want to implement. Perhaps some more high-level information like this CQRS pattern may help you.

For instance, it is common in CQRS to join multiple commands into one "batch" of SQL statements, within a single transaction, to increase speed. Some DB allow array binding, which make it even faster.

Without source code, it will be difficult to find out what is wrong. With the numbers you are showing, I suspect you have to find the real bottlenecks of your application. I guess you are in fact far away from reaching the FireBird limitations, but it is they way you use it which makes it limited. Adding some clever cache, and use a clear CQRS pattern may help a lot.

Answer 2

Client side: create a function/procedure which will tell to server/table "im online" or "im offline" or like this messages when user do an action. instead of sending status messages every 2-5 seconds.

Server side: create a function/procedure which will tell to clients only newest changes (not full of table) when new message arrive from any client.

Or you can create procedures in firebird for this. This will lower your network and server traffic/load.

EDIT:

Another idea is; you can create addational tables for IDing/listing your standart datas. How it is?

Let's say you are sending or recieving "HOT", "COLD", "COOL" datas to clients or from clients. long text means more network and database load. You can create a table which is listing as standart for all things. Lets do an example;

Table name HotColdCool

ID   Description
1    HOT
2    COLD
3    COOL

With this table you will send and recieve only ID (any kind of int) instead full text. This will lower your network and database load with to many clients.

if you explain more about your project, i can give more idea about it to you.