Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-25 : 09:05:21
|
| Joe Enzminger writes "This may be a bad design issue, but humor me and let me know if there are any "standard" ways to do this. Our application is a gaming application (gambling actually), that stores millions of ticket records that must be "dealt" sequentially to clients on request. Our initial implementation was a custom, flat-file storage system that was relatively fast, but the huge volume of data (gigabytes), quickly made administering and protecting this data very difficult. Because of the volume of data, we decided that storing the ticket records on the database might make keeping track of this data easier.Right now, ticket records are stored in a single table, with the primary key being a Game Serial Number, Subset Number, and Outcome Number.Our current system manages about 10,000 games, each with about 200 subsets, each with about 10,000 Outcomes. Prior to distribution to clients, the system stores just the first 2 subsets from each game in a separate table. It rotates new subsets in, and the old out, as they are distributed.....Clients request games by GameSerialNumber (i.e select top 1 * from SmallOutcomeTable where GameSN = @GameSN)Now the questions:First, we need to make sure that no ticket gets dispensed more than once, and that they get distributed in sequence (Outcome Number) so we need to implement some kind of select, then update or delete scheme on the table. What is the best, highest performance way to do this? Second, performance is a big issue, with around 2500 - 4000 clients requesting games on an average of once every 12 seconds. Is there a way to control more tightly the concurrency (locking and unlocking of the "RequestOutcome" stored procedure?Finally, should we even be using the database to try to do this? Is there a better way?Thanks for any information you can provide." |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-02-25 : 09:18:05
|
| Similar to a telecoms rating system.Just don't try to do it one at a time.Select a batch of records that you want to work with.This could be 1 request from each game - gives you a max of 10,000 recs which is a nice number to fit to memory.Put them in a temp table if necessary to do the work - this will guarantee that they all stay in memory and don't take up extra space.You should make the processing of the batch single threaded so that there are no locking issues.Make sure that the input table doesn't have many indexes (1 clustered on an identity would be good) so that you don't get fragmentation or have to maintain it.Having this input table - one output table and all other tables either static or get cleared down for every batch would be a ood design.The amount of data you are working with is similar in size to a rating system I implemented a few years ago - it would keep up on a destop so you shouldn't have any problems on a proper server with lots of memory.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
Implementing a Queue Scheme