BuzzerBeater Forums

Yeah, we use indexes, not to worry. I have to say, your way would maybe make sense in a different setting. Currently, I don't see how this could improve performance at all, since you'd still need to read and write the same data in the end. What you do in the middle doesn't help.

Believe me, we've had Charles and Forrest looking at this problem a couple of times, and if they couldn't figure out a better way, it's probably not there. Doesn't mean we'll stop looking for it though.

Yeah, but reading and writing have to be done either way. It's not the calculations that are slow, it's the I/O.

To get the data to the second server, you still need to read it from somewhere, right?

It's worth looking into. I'll try to pick the brains of our senior devs about it and see what they think. Thanks for the input.

To get the data to the second server, you still need to read it from somewhere, right?

Yes, that's right and here it's important to know how your two servers can communicate to each other. Here are my limitations. But, with Microsoft SQL server (I think that exists for oracle too), you can create a "linked server" object. If you succeed to create a linked server on the two SQL servers, you will be able to make SQL statements from one server to the other one like this:

insert server1.db_name.dbo.table_name (col1, col2,..., coln) select col1, col2,..., coln from server2.db_name.dbo.table_name

That means you could copy all the needed datas with very simple SQL statements. And the nice thing is that, even if you have million of records in your tables, these statements will be very, fast (some seconds).

I know how to create linked server on SQL Server Management Studio, but that supposes that the two servers are able to communicate to each other (same domain?). And here is the end of daily job. But you have many information about that on the net ;)

PS: just to be sure, a linked server is a SQL object which is created in a SQL Server which is installed on a server (hardware). Because there are too many "server" in this message :p

For pulling the data over, there's also replication which can work (well, in addition to mirroring, but that's primarily most effective for failover support). But if they're reading and then updating the same table, unless they could ensure that nothing else touched that table during the time it would take to process the box scores, I don't think relocating the data to another server would help. (Though as a programmer who is administering some databases but not a DBA, I freely admit I might be missing something relevant.)

I would think this scenario may instead be a case where DB redesign could be more effective, though of course that is not something to be undertaken lightly at all. Naturally, one could also scream "more hardware" and if the server were not using SSDs or enough RAM, that could be a consideration, but cost benefit issues arise there.

But if they're reading and then updating the same table, unless they could ensure that nothing else touched that table during the time it would take to process the box scores, I don't think relocating the data to another server would help.

But for the game calculations, you are sure that, during the quarter before the start of the games, the datas needed by the process can't be updated by the user. The key here is not to copy all the datas in the tables, but only the relevant datas. For example, it's obvious that the game calculations need the table wich contains the players, and that a player can change of owner during the calculations. But:

1) the process doesn't need all the BB players.

2) the process needs to know the owner to start the calculation but doesn't need to update the owner at the end of the process.

I'm not sure we can really analyze that fully without knowing the relevant code. Just as a hypothetical, what if player's experience level is updated with each minute of play - and then the updated value of experience reflects in further calculations? I don't consider this to be a likely scenario, but if that's the case, there'll be some small discrepancies.

I think more than this, though, it just seems an odd design choice to have the box scores be calculated after the completion of the game rather than as part of the output of the simulation itself. There may need to be some final summation performed, but the stats themselves could be updated into appropriate objects/tables and then as the match concludes, any necessary summation can be performed independently. This could also lead to staging of the box scores - i.e., immediately after the game you have the stats of both teams and the tactics/enthusiasm choice. If ratings and PP100 require more calculation, those could be blank with the note that "the reporters are finishing up their match ratings" or some flavor text like that. But there's certainly no reason at all that the tactics/enthusiasm should not be immediately visible (heck, even in the viewer!), and there's not a particularly good reason that the players' statlines should not be visible as well.