Author Topic: C# Aurora Database  (Read 9972 times)

0 Members and 1 Guest are viewing this topic.

Offline alex_brunius

  • Rear Admiral
  • **********
  • Posts: 854
  • Thanked: 22 times
    • View Profile
Re: C# Aurora Database
« Reply #15 on: October 27, 2016, 01:40:27 AM »
The 3-5 minute timescale is an estimate for this very large game. Until I write all the saving code, I don't know the actual number. Currently, it takes less than 10 seconds to load the above game entirely into memory.

That's reassuring, it wasn't super clear from your post that this was from a very large game. If you had written that saving will probably take similar amount of time that currently doing a turn does at most, I wouldn't have been so alarmed.

Even if that's not good save performance, it's more acceptable.
 

Offline Narc

  • Able Ordinary Rate
  • Posts: 2
    • View Profile
Re: C# Aurora Database
« Reply #16 on: November 18, 2016, 05:51:17 AM »
Quote from: Steve Walmsley link=topic=9117.    msg98165#msg98165 date=1476954967
[. . . ]One option is to flag every object that is changed/deleted at any point and then update the DB based on those flags.     However, that is open to errors (if I forget to flag something) and I have to remember to ignore any 'deleted' records.    [. . . ]
One option I'm surprised nobody brought up was to use an ORM like EntityFramework, which internally does exactly this (Edit: I forgot to mention -- it also combines really nicely with Linq expressions).  I've been using it (with SQL Server back-end) at work for the last four years and have nothing but praise for it.   

Bonus: EntityFramework supports most database back-ends you can think of (which claims to include SQLite; I've never tried it myself), so you can be database-agnostic.   

I suppose it's too late now, though.   

Can I suggest an auto-save feature? I'd hate to lose hours' worth of progress from a power failure.   
« Last Edit: November 18, 2016, 06:03:17 AM by Narc »
 

Offline Steve Walmsley

  • Moderator
  • Admiral of the Fleet
  • *****
  • Posts: 6511
  • Thanked: 853 times
    • View Profile
    • http://www.starfireassistant.com
Re: C# Aurora Database
« Reply #17 on: November 18, 2016, 11:37:06 AM »
One option I'm surprised nobody brought up was to use an ORM like EntityFramework, which internally does exactly this (Edit: I forgot to mention -- it also combines really nicely with Linq expressions).  I've been using it (with SQL Server back-end) at work for the last four years and have nothing but praise for it.   

Bonus: EntityFramework supports most database back-ends you can think of (which claims to include SQLite; I've never tried it myself), so you can be database-agnostic.   

I suppose it's too late now, though.   

Can I suggest an auto-save feature? I'd hate to lose hours' worth of progress from a power failure.

Auto-save would be possible.I am currently working my way through all object collections that need to be saved. Once that is done, I will start optimising. If the end result isn't too bad in terms of save time, I will add an auto-save option.
 

Offline Youtoo

  • Able Ordinary Rate
  • Posts: 3
  • Thanked: 1 times
    • View Profile
Re: C# Aurora Database
« Reply #18 on: November 25, 2016, 12:56:34 PM »
I have worked as a DBA for almost 20 years.    I mainly work with Oracle and some mysql, but I did some google searches on sqllite.    It has limited functionality.    However, here are some things that may speed up saving.    It should not take 3-5 minutes to refresh 60,000 rows.    It should take a few seconds at most.   

Here are a variety of tips for improving inserts.    Ill highlight the ones I think will help the most
hxxp: stackoverflow.   com/questions/1711631/improve-insert-per-second-performance-of-sqlite

Note: Steps 1-6 all work together. 

1.    Turn off auto commit.    Use 1 transaction for inserts and 1 transction for deletes.    This will exponentially improve performance.    This is the case
in all SQL DBs when doing bulk DML.   
https://www.   tutorialspoint.   com/sqlite/sqlite_transactions.   htm

2.    Deletes: Use Truncate optimization.    On how to do it at the bottom:
This should make deletes run in seconds
https://www.   sqlite.   org/lang_delete.   html

3.    Use bulk inserts.    Syntax for SQLite is wierd.    With Oracle you generally have declining returns after bundling 100 records together.   
If you do it as 100 you cut your inserts from 60,000 to 600
hxxp: stackoverflow.   com/questions/1609637/is-it-possible-to-insert-multiple-rows-at-a-time-in-an-sqlite-database

4.    Use pragma to increase cache size to improve bulk inserts.    Last tip at this link
hxxp: stackoverflow.   com/questions/1711631/improve-insert-per-second-performance-of-sqlite
I coudn't get any gain from transactions until I raised cache_size to a higher value i.   e.     PRAGMA cache_size=10000;

5.    Do NOT use row level triggres.    Get rid of them if you have them.    IF you have them you cant use truncate optimization and it will really slow down
bulk inserts

6.    Index all foreign keys.    So the child table with a foreign key back to the parent needs to have that field indexed.    If not, its causes
full tablescans when deleting from the parent.    All SQL DBs are like this.   

7.    Normalize data: IF you are using child tables normalize the data so you dont repeat.    Use a number field with a counter (called a surrogate key)
for the primary and foreign key link.    You then use a second unique key to identify the row.    You use surrogate keys so you only have to store a number
in the child table.    Normalizing the DB can save you alot on space and how long it takes to write out to a file.   

Referencing this:
"Everything is up and running and the Aurora DB is no longer Access, but SQLite.   It loads a little more quickly but the real time saver is writing to the DB.   I can delete the entire system body table (60,000 rows with 48 fields) and then re-insert all the records from the program in about 15 seconds.   While that isn't exactly super-fast it is much better than Access.   It probably means an actual save game will take a few minutes (maybe 3-5 mins once I code all the tables) but as some turns take that long now, this should be a huge improvement, especially as it means no disk access during play.  "

Every row does not need 48 fields.   Use child tables and look up tables.   You dont need to write out the engine name, over and over again.   Same with technology names, planet names, etc.  .  .   Even stuff people can name can get added to lookup tables.   It sounds like your save file is way too big.   


8.    Use lookup tables with Surrogate keys as explained in #7
-- anything that is repeated such as technologies, ship types, engine parts, planets, that are generated, keep in a lookup table.   
The table will be
Surrogate key (number), rest of the data.   
This way you only store that number instead of the full name.   
hxxp: stackoverflow.  com/questions/11304128/how-do-i-create-a-surrogate-key-on-a-database-with-composite-foreign-keys



Other Option: To keep everything in memory use an in memory database setting.    So all changes get inserted, updated (in memory anyway), then the 'save'
just dumps everything to disk.    This may be alot faster and less code that doing steps 1-8 and keep a separate code base to manage everything in memory.   

9.    Instead of using separate code to store data in memory, it may be easier to configure sqllite as an in memory database:
https://www.   sqlite.   org/inmemorydb.   html

So you have 1 code base for all changes, etc.   .   
Use this to save your in memory database to disk.    This should be faster than the sqls
https://www.   sqlite.   org/backup.   html

« Last Edit: November 25, 2016, 01:03:37 PM by Youtoo »
 
The following users thanked this post: lordcirth

Offline Steve Walmsley

  • Moderator
  • Admiral of the Fleet
  • *****
  • Posts: 6511
  • Thanked: 853 times
    • View Profile
    • http://www.starfireassistant.com
Re: C# Aurora Database
« Reply #19 on: November 26, 2016, 10:04:37 AM »
Thanks for the advice. I have completed all of the DB update code but haven't tried to optimise yet. For my large-scale game (500 systems, 23 races, 60,000 system bodies), total save time is 65 seconds.

I am going to begin optimising now and see how much I can reduce that. Even with 65 seconds, the huge performance advantages of running everything in memory means games will run a lot faster (combining run time and save time).

EDIT: I switched System Bodies to inserting only new records and for existing bodies updating only the fields for Alien Installations, Bearing, Surface Temp, Atmospheric Pressure, Albedo, Greenhouse Factor, Coordinates, Radiation, Dust, Comet Direction, Geo Team Survey Status and Name. For system body surveys, I am only inserting new records.

That has reduced the combined time for those two tables from 22 seconds to 4 seconds. With a few other tweaks, save time is about 45 seconds for the whole game. Obviously this will be much faster for 'normal' sized games.

There are no other tables with save times beyond 3 or 4 seconds but I'll still see if can improve that. Even so, I think the save times are now in the acceptable range.
« Last Edit: November 26, 2016, 11:41:38 AM by Steve Walmsley »
 
The following users thanked this post: TMaekler, TCD, lordcirth

Offline MarcAFK

  • Vice Admiral
  • **********
  • Posts: 1730
  • Thanked: 61 times
  • ...it's so simple an idiot could have devised it..
    • View Profile
Re: C# Aurora Database
« Reply #20 on: November 27, 2016, 08:02:38 AM »
I'm impressed with how easy you made it look.
" Why is this godforsaken hellhole worth dying for? "
". . .  We know nothing about them, their language, their history or what they look like.  But we can assume this.  They stand for everything we don't stand for.  Also they told me you guys look like dorks. "
"Stop exploding, you cowards.  "
 

Offline Person012345

  • Commander
  • *********
  • Posts: 385
  • Thanked: 2 times
    • View Profile
Re: C# Aurora Database
« Reply #21 on: November 28, 2016, 09:04:33 AM »
I'll happily take 5 minute saves if it means that late game turns won't be taking 5 - 10 minutes to complete.
 

Offline Person012345

  • Commander
  • *********
  • Posts: 385
  • Thanked: 2 times
    • View Profile
Re: C# Aurora Database
« Reply #22 on: November 28, 2016, 09:06:36 AM »
I have worked as a DBA for almost 20 years.    I mainly work with Oracle and some mysql, but I did some google searches on sqllite.    It has limited functionality.    However, here are some things that may speed up saving.    It should not take 3-5 minutes to refresh 60,000 rows.    It should take a few seconds at most.   
As steve already said, 60,000 rows takes 15 seconds, not 5 minutes.
 

Offline JesDer

  • Leading Rate
  • *
  • Posts: 6
    • View Profile
Re: C# Aurora Database
« Reply #23 on: December 02, 2016, 03:18:12 PM »
I know I am a bit late to this conversation. .  but instead of SQLLite you might want to consider SQL CE.  I played around with it a bit and found  SQL CE to be much faster than SQL Lite. 

Since SQL CE is Microsoft's compact DB of choice, it works really well with Visual Studio and C# and you could also take advantage of using Code First Entity Framework.  I haven't done much with Entity Framework, but what I have done with it impressed me.
 

Offline Steve Walmsley

  • Moderator
  • Admiral of the Fleet
  • *****
  • Posts: 6511
  • Thanked: 853 times
    • View Profile
    • http://www.starfireassistant.com
Re: C# Aurora Database
« Reply #24 on: December 03, 2016, 06:33:46 AM »
I know I am a bit late to this conversation. .  but instead of SQLLite you might want to consider SQL CE.  I played around with it a bit and found  SQL CE to be much faster than SQL Lite. 

Since SQL CE is Microsoft's compact DB of choice, it works really well with Visual Studio and C# and you could also take advantage of using Code First Entity Framework.  I haven't done much with Entity Framework, but what I have done with it impressed me.

I did consider CE but as Microsoft ceased development in 2013, I didn't want to rely on a technical solution that might not be supported in the future (already done that with VB6 :) )

I have completed the implementation of SQLite now and it seems to be working very well.
 

Offline JesDer

  • Leading Rate
  • *
  • Posts: 6
    • View Profile
Re: C# Aurora Database
« Reply #25 on: December 04, 2016, 10:51:09 AM »
Quote from: Steve Walmsley link=topic=9117. msg99474#msg99474 date=1480768426
I did consider CE but as Microsoft ceased development in 2013, I didn't want to rely on a technical solution that might not be supported in the future (already done that with VB6 :) )

I have completed the implementation of SQLite now and it seems to be working very well. 

That is understandable.  I forgot they had stopped working on CE.  It didn't seem like it, but I guess it has been a while since I last looked at it. 

Is there a reason you are messing around with Databases at all? Since you mentioned you are running the game in memory, you don't need to be able to query a DB.  Why not just serialize your objects to something like Json? If you really don't want plaintext then you could encrypt the files. 

 

Offline Steve Walmsley

  • Moderator
  • Admiral of the Fleet
  • *****
  • Posts: 6511
  • Thanked: 853 times
    • View Profile
    • http://www.starfireassistant.com
Re: C# Aurora Database
« Reply #26 on: December 04, 2016, 01:30:16 PM »
That is understandable.  I forgot they had stopped working on CE.  It didn't seem like it, but I guess it has been a while since I last looked at it. 

Is there a reason you are messing around with Databases at all? Since you mentioned you are running the game in memory, you don't need to be able to query a DB.  Why not just serialize your objects to something like Json? If you really don't want plaintext then you could encrypt the files.

Even though the data is being loaded into memory, I still need to be able to play around with the data itself. Debugging the data mid-game and making changes is easier with a DB.
 

Offline Denniz

  • Able Ordinary Rate
  • Posts: 2
    • View Profile
Re: C# Aurora Database
« Reply #27 on: December 12, 2016, 10:50:42 PM »
Quote from: Steve Walmsley link=topic=9117. msg99494#msg99494 date=1480879816
Even though the data is being loaded into memory, I still need to be able to play around with the data itself.  Debugging the data mid-game and making changes is easier with a DB.
You could go for the best of both worlds.  Use file(s) for the actual saves but build a utility to move the data in/out of the DB for development purposes. 
 

Offline QuakeIV

  • Registered
  • Lieutenant
  • *******
  • Posts: 166
  • Thanked: 8 times
    • View Profile
Re: C# Aurora Database
« Reply #28 on: December 13, 2016, 02:22:37 AM »
Pretty sure that wouldn't work as well.  Databases tend to do a relatively good job of maintaining the integrity of the information they are storing, especially if you have the database track its own history (which he doesn't seem to be doing admittedly).  You could end up with a permanently mutilated save file that wouldn't have happened if you had been using a database.
 

Offline db48x

  • Sub-Lieutenant
  • ******
  • Posts: 101
  • Thanked: 15 times
    • View Profile
Re: C# Aurora Database
« Reply #29 on: January 22, 2017, 07:10:07 AM »
EDIT: I switched System Bodies to inserting only new records and for existing bodies updating only the fields for Alien Installations, Bearing, Surface Temp, Atmospheric Pressure, Albedo, Greenhouse Factor, Coordinates, Radiation, Dust, Comet Direction, Geo Team Survey Status and Name. For system body surveys, I am only inserting new records.

This isn't really related to databases (sqlite is great though; good choice), but why update the bearing at all? If you just store the bearing that the body had at the beginning of the game, it's simple enough to calculate the current bearing given the current timestamp. This would save you from having to touch every row in the table; instead you would only end up touching the rows for a handful of settled worlds.
 
The following users thanked this post: Theeht

 

Sitemap 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51