1

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

Artik
  • 803
  • 14
  • 36
  • This is perhaps difficult to answer without seeing your code. Have you done performance profiling? How do you know what needs to change? If you haven't profiled then optimization is a crap shoot - most of the time it will end up being something that's not what you think it is that needs to be fixed. So many times I have seen the algorithm blamed when the implementation was at fault... – J... Apr 28 '13 at 12:07
  • I have not done performance profiling. I'm amateur and I can't do this process:( I'm writing this application to help my main professional job. I just enthusiast informatic technology I'm trying do it by myself. After one year good working project I have to rebuild it. Besides of bottleneck I have correct some others parts of my code. Therefore i decided write it again - better, and I asked how to do this synchronization correctly. Thank you for your answer. – Artik Apr 28 '13 at 13:13
  • Before you make up your mind what is wrong, why don't you profile your application and find out where the real bottleneck is. Why are you happy to guess? Did you profile and find a certain synchronization primitive (wait for lock) is really your bottleneck? If it turns out readers are waiting for other readers, maybe you could use a MREW (Multi Read Exclusive Write) lock. – Warren P Apr 28 '13 at 13:37
  • Yes, thanks to your comment I've read about profiling. It have to help me find bottleneck. Could you recomend me tool which can I use with my Delphi XE Prof? A specially easy and free at start. – Artik Apr 28 '13 at 17:16
  • Do clients keep connections open or do they frequently disconnect? Frequent reconnects instead of keeping the connection alive can cause server overload as the sockets are still open for a while) – mjn Apr 29 '13 at 16:24
  • mjn - clients keep connections as long as it possible - I mean until disconnection error appear. After this reconnection is starting. – Artik Apr 29 '13 at 16:52

2 Answers2

3

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.

Arnaud Bouchez
  • 42,305
  • 3
  • 71
  • 159
  • Thank you. I will try think and rebuild my application again. It's difficult to present its code because it has to many (for me) business logic where can be bottlenecks. This application has worked ok until more then 30 clients were connected. The problem appeared after one year no problem work. And now I have to rebuild it. Thank you again. – Artik Apr 28 '13 at 13:10
  • So does the "Command" in the CQRS pattern equal the "Command" pattern that we all know and love? – Warren P Apr 28 '13 at 13:36
  • @WarrenP As soon as the queries are segregated from them. :-) CUD = commands R = queries – Arnaud Bouchez Apr 28 '13 at 20:14
0

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.

signon
  • 13
  • 1
  • 7
  • Status has to be send so frequently because it contains values of changing parameters like temperature etc. Every new message from clients can change meaning of collected data - priority of importance - so local clients aplication have to decide how to interpret these data. So, maybe it is good idea to remain and calculate data on local clients. Now, It Firebird does. Thank you. I'll be thinking about this and report about improvement. – Artik Apr 29 '13 at 17:02
  • I dont know realy what you are trying but also you can try play with indexes too. Organised and periodicaly fixed indexes improves database performance also. Saying that because i understand you are inserting/updating realy a lot of data. Doing this offen causing happen database/table fragmentation. So, reparing/re-cereating indexes fixs and improve database performance. – signon Apr 30 '13 at 15:30