Hi, I use SQL Server 2K to keep the data in my online game. The game is likely to have up to 500 players at the same time, with each of them (of course, player object in server) has its own ADO connection. The incoming messages in server are handled multithreadingly. Plus, there's another operator program which can access to database. So, is SQL Server 2K right choice for this ? Thanks.

I agree with the AP. You should only have one connection to the DB per process. Each thread in that process can and should share the connection.

The reasoning is as follows

Your server process uses more resource managing all those connections. The DB uses resources (CPU/RAM) managing all those connections. Fewer connections means fewer resources being consumed. Essentially the Database can handle 100 serial requests faster than it can handle 100 simultaneous requests. There could be situations where this is not true, Say the first request results in a large result set the has to be piped out over a network connection and the other 99 requests all have tiny result sets. The 2nd request could in theory start executing later and still send the result set back to the client before the first result set has completed, however large result sets are usually something that happens when running reports on a database. Thats not likely to be the sort of thing you will do with a game. I would bet that more than 99% of the requests you will make to the DB will select single records, or be updates and inserts.

There is a second thing to consider. When running multiple threads, and a game state, generally one one of those threads should modify the game state at any given time. Normally the thread management takes care of all this, but you can still run into the situation where one thread starts an update on the DB, meanwhile a second thread starts a select on the DB. since you can't be certain which thread will complete first, you could have the second thread select and start using data that is about to be out of date. The first thread meanwhile will update the database and use different data. This is bad. Having one connection means that only one database operation can run at a time. This means that any data that is selected from the database can always be considered the correct data.

The main thing to watch out for is one thread starting to use the connection, and another thread clobbering the object. You'll want to use some locking scheme to only allow one thread to use the connection at a time.


---must sleep now.

Depending on the database, you may get better performance with some number of connections. However, there should not be one connection per player; rather, there should be X connections per server process. The reason is that some database servers intrinsically time-slice between different connections while running operations, so you get better multi-tasking through multiple connections.

However, I would start with only one connection, and only if that seems to choke on serialized database access, add a few more connections. 500 sounds like way too much.

Thanks guy. How about this :
- One connection for periodical process on the database
- One connection for handling player incoming messages. I use critical section (so that no two or more threads use connection object simultaneously), like this :
EnterCriticalSection (&a);
ProcessMessage(PlayerMessage, PlayerID);
LeaveCriticalSection (&a);

- Two aforementioned connections are global objects. Sometimes, I open/close temporary connection (just for special case, such as when in registering).

Anyway, there is other database connection, but that's from the Operator's PC (it's in different workstation from the Server program).

BTW, can SQL Server 2K handle well the simultaneous operation on database ? (for example, SQL Server 2K blocks process from other connection until the current process is finished). And, each new process appears in milliseconds interval.

I haven't deployed anything that worked on top of 2K, so I can't answer that question. However, there seems to be an easy way to answer that question: mock something up, and measure it yourself! Please let us know how it goes.