What I have
This is a prototype. I have a pool of 100 clients connected to the server via websockets reporting things and awaiting for commands. The server polls the commands database table of type MEMORY in a loop using a query with WHERE client_id=?. I can insert a combination of client_id+command to that table, and once I do that, the corresponding loop will match and SELECT it and pass it back to the client.
What's the problem
The approach sounds like it would work, but as far as I understand I'm talking about n simultaneous database connections and queries in an endless loop (n being the number of clients), which doesn't sound effective. It'd be much better to do one query in one loop and then somehow check the client_id, if any, and distribute the results to the corresponding clients.
This reminds me of the approach where you're selecting articles first and then for () {} the resultset and do separate queries to get the details foe each of the items, which results in n+1 queries being made. The solution to that is doing a big query with JOINs and also preloading the other data that doesn't fit into the main JOINed query. There should be the similarly more effective way to do the database polling too.
UPDATE: I found this answer in the related section, and it says pretty much the same thing:
Hammering your database isn't really a good idea. While I'm pretty sure you've realized this, others might not have. I remember a friend of mine tried to use a php script and a Javascript AJAX function in a loop for a semi-real time game. He very quickly realized that performance degraded as more people joined, simply because he was executing a ton of queries per second which hammered the database.
So polling the database for each client sounds as unscalable and ineffective as building an AJAX chat application.
What I'm asking for
I guess that every possible programming approach must have been named and covered by now, so what is this one called? What is the common advice/approach here?
Best Answer
You want a connection pool - 100 clients will be throttled to using a pool of a handful of DB connections so your DB will not get overloaded responding to many simultaneous requests.
You could try to optimise the calls but you will still need to allow the clients to access the DB through a mechanism identical to a connection pool, even if there is just 1 connection available in the pool, you can combine all the IDs from the pending clients together into a single where clause to pass to the DB and unpack the results to each client when it returns. You'll either have to track which client is in the request or ensure the results have a result for each requesting client as more clients will arrive while the DB query is in progress, when the results are distributed to the clients, you repeat with the new ones.
If you have a pool like this,and your circumstances allow it, you can also delay DB requests to a gentle rate, but hitting the DB repeatedly on a single connection doesn't impact performance anything like as much as 100 clients all jostling for attention simultaneously.