Aurora 4x
C# Aurora => Development Discussions => Topic started by: Steve Walmsley on October 20, 2016, 04:16:07 AM
-
While the current debate on the nature of the Aether is being resolved, I have taken a break from coding functionality to look at the database situation. VB6 Aurora uses an Access database and writes to that DB frequently (One of the reasons for the slow performance).
C# Aurora (at the moment) also uses Access, although everything is loaded into memory at game start (about 10-12 seconds on my PC). I have, however, run into a problem.
My intention is to save at user request, rather than the continual save in VB6 Aurora. 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. So I developed a cunning plan. I would backup the DB first, then simply remove all non-static data relating to the current game and replace with it whatever was currently held in memory. Even if this took a few minutes, it would still be far superior to the current situation of slow increments and I am not going to miss anything.
Yesterday I finally got around to implementing this plan and discovered you can't use Bulk Insert on an Access database. Aaagh!
So faced with going through all the existing code and flagging every change or simply changing the database, I have decided on the latter. Fortunately the program was set up with the intention of being flexible on the DB and all data access is handled within a single class. For example, one function accepts SQL and returns a data table so I can change the code in that function without affecting any of the code that calls it.
So now the question is, which database? Or do I even need a database? Another option is to save to XML or basic files.
I use SQL Server at work and there are a few different versions, including SQL Server Express, SQL Server Local DB and Compact Edition. For deployment purposes, the last of those options seems best but Microsoft have discontinued the product with no replacement apparent. The former two require SQL Server installs on the target PC, which is not ideal.
So I have been looking at SQLite. This seems to fulfil what I need and apparently I can write a series of records before committing the transaction, which should make things much faster. So, for those who know much more about this than me, a couple of questions.
1) Does SQlite seem like a good idea in this situation. If not, can you suggest an alternative?
2) If SQLite is suitable, which GUI should I choose? So far I have downloaded trial versions of:
a) DB Browser for SQLite (basic but free)
b) SQLiteManager (not keen as it flickered a LOT)
c) SQLite Expert ($100 but my favourite so far)
Any comments appreciated.
-
This (https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems) might be of use.
IME, MySQL is the best if you like copying solutions from Stack Overflow. Incredible amounts of help are available on the internet. SQLite appears to be suitable to your needs (fast, embedded); provided you don't want to tinker with performance (I infer you do not), this is a solid choice. I wouldn't recommend PostreSQL, because it's, well, overfeatured for relatively simple applications and slow.
I have looked at the free SQLite software and did not like it. I don't know how the purchasable software handles.
-
I use SQLite for the Astra Imperia aid. You don't necessarily need a GUI, but I use Valentina (http://www.valentina-db.com/en/studio-for-sqlite)
-
After using both postgres and mysql, I'd advise using postgres as mysql is poorly optimized for large datasets or subviews. This is largely because MySQL is barely at all supported nowadays and recent developments for SQL have been more hacked into MySQL than supported.
-
Why not just use a serialiser? For example SharpSerializer, just give it a object/s to save and its done almost without doing anything else.
-
I agree, if you're loading everything into memory, there's no reason to use a database if you're not using it for set based operations.
I can think of some other benefits to storing as files instead of in a database
- You can separate data into separate files, so things like themes, initial planet configurations, etc can be saved apart from the same itself. So adding Planet 9 when it's discovered just means releasing a new Sol.txt file instead of a whole database.
- No more fussing with adding/removing columns, making sure your C# data types match SQL, foreign keys, indexes, etc.
- Save games are separate from each other, so I can delete/backup/share as necessary. On a related note, it's easier to submit a save with a bug if it's a standalone file and not a full database.
- For better or worse, it makes it much easier for players to open their saves up and 'tweak' things if necessary (or for the lulz)
From an end-user perspective, I would go with an XML serialization method. While I deal with JSON more often (I'm a web developer), I find XML easier to read and search for larger data sets. Then again, from a developer viewpoint, it's hard to argue against how easy Json.NET is to use.
-
A quick update: I've played around with a few different options, including XML and some DB options - even CSV :)
Finally I have decided to go with SQLite, despite abandoning that idea twice in the process due to problems making it work.
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.
I'll need to check if updating every system body record (due to orbital movement) is faster than delete and replace but I doubt it. For some tables thought I can also be a little more imaginative than simply deleting everything and updating it (history tables for example).
I'm also auto-backing up the DB before saving so I can make that more useful too and create separate save files.
-
Updating a couple columns should be a bit faster in an RDBMS than delete and insert on the entire table. RDBMS use column widths to make shortcuts with pointer references, so updating elements pointed to with x pointers vs. y pointers should be faster if x < y.
I'm not a SQLlite expert, though, so I could be mistaken with this particular platform.
-
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.
Hey Steve,
If this (3-5 mins for a save) is true, that sounds like a significant regression in people's ability to execute "undo"/"backup" strategies. Due to e.g. the bugs in detection, I like to save several times while exploring a new system where I suspect there might be aliens (so that I can use reasonable impulses while out of contact while still knowing that I can go back and re-run using smaller impulses if Aurora messes up). In the old days, this consisted of a simple copy of the database file (a few seconds). If it really does take minutes to do a save, then this strategy won't be available.
One suggestion: It seems like a lot of the information in the system body table shouldn't change of the course of the game. Would it be possible to break big tables in two (a "read-only" and "non-const" table) so that you don't spend time re-saving big configuration tables?
John
-
I don't understand why it would take 3-5 minutes to save the database ???
And how dependent is this on game size? Will a fresh game still save fast but a huge one could take even longer upwards to 10min+?
Is it influenced by having SSD/HDD?
As sloanjh wrote 3-5min is a big step backwards as currently Windows does a copy + paste of the database in a few seconds max, and add a few more seconds and you get it compressed too...
Would it be possible to have the game save without user having to wait for it? ( like in the background from a memory snapshot or something ). As long as you can look around and do other stuff while saving it wouldn't be so bad, and I have to admit that I never actually played a game further then until each economy turn is taking more then 5-10 seconds. So for me 3-5min would feel really bad.
-
Hmm, if you are updating 60000 rows in a loop and the index is updated on each update I could easily see it taking a few minutes. One simple workaround could be that you run a DROP INDEX first and then recreate it after the updates: https://sqlite.org/lang_dropindex.html
Possibly some db restructuring to move the orbit data out of the main table could also be helpful. That could allow for a complete rewrite of the orbit table on each save so you do inserts instead of updates and save yourself the seek time for the records to update as well.
-
If a normal savegame serializer can save game states from normal games from memory in 0.3-5 seconds for most games, it seems odd to cling to a database with 60-600 times worse save performance.
Unless it would be an insurmountable effort to rewrite it to a more normal serialization that is. Or there are other reasons the game must use a database.
Was the option to skip a database and write to XML or basic files evaluated from a performance standpoint?
-
Just wondering, how long does it take to load the table that you mentioned taking 15 seconds to write?
What is your estimate for how long it would take to load everything into memory (i.e. start the game)?
I would expect that load and write times should be similar unless there is large amount of unnecessary overhead involved in writing.
-
I don't understand why it would take 3-5 minutes to save the database ???
And how dependent is this on game size? Will a fresh game still save fast but a huge one could take even longer upwards to 10min+?
Is it influenced by having SSD/HDD?
As sloanjh wrote 3-5min is a big step backwards as currently Windows does a copy + paste of the database in a few seconds max, and add a few more seconds and you get it compressed too...
Would it be possible to have the game save without user having to wait for it? ( like in the background from a memory snapshot or something ). As long as you can look around and do other stuff while saving it wouldn't be so bad, and I have to admit that I never actually played a game further then until each economy turn is taking more then 5-10 seconds. So for me 3-5min would feel really bad.
It is a trade-off. in VB6 Aurora, the game saves as you play but this means the turns take a while. In C# Aurora, the turns are very fast because I am not saving anything as you play.
The 15 second save I quoted for the system body table is in my game with 23 players and 500 generated systems. It will obviously be a lot faster for a 'normal' size game. 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.
-
Hmm, if you are updating 60000 rows in a loop and the index is updated on each update I could easily see it taking a few minutes. One simple workaround could be that you run a DROP INDEX first and then recreate it after the updates: https://sqlite.org/lang_dropindex.html
Possibly some db restructuring to move the orbit data out of the main table could also be helpful. That could allow for a complete rewrite of the orbit table on each save so you do inserts instead of updates and save yourself the seek time for the records to update as well.
The 60,000 rows I mentioned only needs 15 seconds. The 3-5 minutes is an estimate for the whole database, which has 203 tables (not all of which would be saved as they consist of static data). I'll have a more accurate number once I have done more work.
-
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.
-
[. . . ]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.
-
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.
-
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
-
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.
-
I'm impressed with how easy you made it look.
-
I'll happily take 5 minute saves if it means that late game turns won't be taking 5 - 10 minutes to complete.
-
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.
-
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 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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
Maybe use polar coordinates for all bodies?
Database contain 3 CONSTANT for every body (or 5, if need store starting angle Ao and time to):
ParentId - id for parent body (planet, star and etc)
R - radius of orbit (distance from parent body)
Va - angle speed (around parent body)
Calculate relative body position (from parent body) on any time t with easy formula:
X=R*sin(Ao+Va*(t-to))
Y=R*cos(Ao+Va*(t-to))
sin and cos can stored in precalculated tables.
Positions of all bodies can restored or prognozed on any time without database change.
-
For orbital movement, I know the bearing of each body, the length of time since the last update and the length of time to orbit the parent body. So change in bearing is (Increment time / orbit time) / 360.
Then I set the new body position based on distance and bearing from parent.
I could calculate as needed, but it is used a lot so its easier to maintain it - especially now I am only saving to the DB occasionally instead of every time.
-
The positions of the celestial bodies in the game is deterministic and easily calculated.
Therefore, they do not need periodic stored in the database.
The calculation of the current position of the bodies at the static source data requires much less resources and time than finding and modifying records in the database.
The position of celestial bodies should be calculated only when needed (e.g. when displaying or calculating targeting and distances) and should not lead to costly write operations.
The polar coordinate system allows the use of three STATIC numbers to describe the motion of any heavenly body for any period of time without loss of accuracy of computation.
Is the radius of the orbit, the angular velocity (or orbital period) and the start angle.
In contrast to the "current coordinates to the previous point-in-time" these values do not change throughout the game (unless of course will not put the Death Star)
As a programmer, I can advise to use class methods for the calculation of the current coordinates of celestial bodies available in the database of static data. Or use a simple proxy class for recalculation of coordinates (when necessary)
(sorry my bad english)
-
For orbital movement, I know the bearing of each body, the length of time since the last update and the length of time to orbit the parent body. So change in bearing is (Increment time / orbit time) / 360.
Sure. You could treat the bearing field as the bearing at time=0 (the beginning of the game), and then the current bearing is just (current time / orbit time) / 360. Same formula, fewer writes to the database. It also generalizes much better to elliptical orbits (which I think you've historically avoided due to complexity; fair enough). Comets have a similar formula, but for radius instead of bearing.
On the other hand, I think you're saying that the code currently just reads straight from the database, and this would complicate your life because you now would have to know the current time when you do so. That's fair too, but I think there are ways to minimize the impact. LittleWolf mentions a wrapper class, which would work. I think you could use a view instead, and not have modify your code much at all. The current time is presumably stored in the database somewhere, so a view that computes the current bearing from that timestamp would be easy enough. Then the existing code just selects from the view instead.
Either way I think it's safe to say that we're all salivating at the thought of the next release. Thanks!
-
:words:
I remember a senior Java lead years ago telling me words to the effect, "Hit disk as infrequently as possible. Disk time is more precious than CPU time in most applications."
-
Whether I store the bearing or not is going to make very little difference. There is no writing to disk during play - only when you press the Save button. I am writing several fields in each system body record anyway, so one extra field on those records will affect overall save time by a few milliseconds. Orbital movement is extremely fast now so it won't make much difference there either.
I am calculating some information as I need it, such as colony cost, but with bearing it was just easier to maintain it than remember to calculate it.
BTW I'm not an expert programmer by any means. I am still learning C# as I go :) The forerunner to Aurora, Starfire Assistant, was a project to teach myself VB.
-
Whether I store the bearing or not is going to make very little difference. There is no writing to disk during play - only when you press the Save button. I am writing several fields in each system body record anyway, so one extra field on those records will affect overall save time by a few milliseconds. Orbital movement is extremely fast now so it won't make much difference there either.
I am calculating some information as I need it, such as colony cost, but with bearing it was just easier to maintain it than remember to calculate it.
BTW I'm not an expert programmer by any means. I am still learning C# as I go :) The forerunner to Aurora, Starfire Assistant, was a project to teach myself VB.
Steve, have you thought about reading the sqlite database into a memory DB on load, then writing it back out to a file on save? You could even implement autosaving without slowing the main game down by backing it up to a separate in-memory database when the save is triggered and writing that one out in a different thread.
There's a great Stack Overflow solution to that here: hxxp: stackoverflow. com/a/11385280
The comment about durability guarantees being lost doesn't matter in this case, as you'd be writing the DB out to disk every now and again.
-
You might want to try mysql. This is free. I am a dba as I said in a previous post. My group managers oracle and mysql. We get pretty good performance out of mysql, plus there are more performance tips for mysql.
There are alot more posts for speeding up mysql. It sounds like a save is 50,000 records or so? That isnt much if you do it right.
hxxp: jotschi. de/2014/02/03/high-performance-mysql-testdatabase/
-
Why you didn't use an in-memory DB ?
You'll make your executions more quickly and one save in file (and you can change the save at every save if you want ^^ )
If you already use it, sorry for the interruption ^^
-
***EDIT** I read through everything (shame on me for not doing that first) and saw that this was covered already, at least a little. Sorry for asking twice. Disregard and carry on.
There is no writing to disk during play - only when you press the Save button.
Would it be too much to request an autosave option/feature? Perhaps every production cycle, or once a month? At least once a year, if possible, please.
I'm the sort of person who is going to forget to press the save button. Every. Time.
-
free save is EVIL.. play roguelike (ironmode) :)
-
sqlite is in fact the best solution for c# aurora
4 reasons
* sqlite is the fastest db available (yes, even faster than mysql)
* no need for installing a database server (for all other db-systems like mysql, postgres, oracle this is mandatory), only a dll is needed.
* very easy sql dialect, recursive cte´s are possbile
* sqlite is public domain
i´m working >17 years with access, sqlite and ms-sql and sqlite is by far the easiest one. the sql-syntax is very gentle and you can do things easy, which are not possible in ms-ssql or jet-sql
* i. e. you can "group by" a column which is in the select a aggregate with the "AS" keyword and there is no need to rewrite the complete aggreate, the alias is enough
* you can group by a single column and there is no need to enumerate all other columns from the select statement like ms-ssql it forces, because this is complete pointless -the result doesn´t
canges after the first grouped column
* recursive cte´s. it´s fantastic. i had a sql-query which builds a tree of items and the longest runtime was appr. 30 seconds. after the rewrite to recursive variation the runtime was 4 seconds!
tenim
-
Steve said above he is already using SQLite for the C# version. While I never worked with the embedded version of MySQL I imagine it would be similar, but there seems to be a catch to "free". Looking at the MySQL webpage I think Steve would have to either get a commercial licence from Oracle or release Aurora under GPL. This would be in line with how it works for the regular MySQL server. This also explains that SQLite seems to be a much more popular choice for hobbyist/indie development I guess.
-
If the coordinate issue is strongly in doubt, I would strongly recommend not going polar. Due to the vast scale difference, truncation and handling of floats at large distances gets to be a mess. This is one reason KSP originally had to have such small system, and even then, issues with fluidity at high values still caused issues. Quill18 has a good tutorial about procedural star system creation that really discusses this. By using integer Cartesian you can avoid that. You just do you gloat point trig and then cast final results back into integer.
-
On a hardware note, I just upgraded to a high end NVMe SSD drive. Wow, it makes a difference for all the IO in the game.
-
i dont understand why u would have to use a database for savegames
the easy way is to just use the 'System. Runtime. Serialization. Formatters. BinaryFormatter'
- create a class 'Savegame' which contains all the objects u want to save
- this class and every class of every object inside this class has to be marked with [Serializable]
- use the binaryformatter to save an object of 'Savegame' into a file
you could even throw everything into an object array and serialize that, but having a 'Savegame' class makes it more readable and adjustable for the programmer
-
I don't think the game is structured that way that it could easily handle the full game state in a serializable object, so could be a pretty big change. It rather does several small writes at different times like every time you change something from the gui. For this kind of use a db is actually not bad.. except for the fact that it is (was) Access in this case which is always bad.
-
I do a lot of manual editing of the data (especially while programming), so a database makes this much easier.
-
On a hardware note, I just upgraded to a high end NVMe SSD drive. Wow, it makes a difference for all the IO in the game.
What did you have before? I've run the game solely on SSDs (Crucial BX100/200) for a while now and it still gets really slow after a while.
-
What did you have before? I've run the game solely on SSDs (Crucial BX100/200) for a while now and it still gets really slow after a while.
Worth noting the NVMe is a PCI-e SSD, while your own (and mine) are SATA.
While a SATA SSD still has incredible throughput a PCIE-e SSD is currently the fastest available on the market. Can often be twice as fast as a SATA.
-
Whilst I'd obviously like to see as shorter save time as possible, I honestly believe that the most important thing is avoiding in game slowdown and stability. If it means that saving and loading take a bit longer then I'm fine with that.
Saving and loading are infrequent actions, and they're ones that you can basically be doing other stuff with. We're not in the days of computers only being able to run one program at a time - checking a few emails whilst it's saving isn't going to kill me, but 5 minute turn times will!
-
Worth noting the NVMe is a PCI-e SSD, while your own (and mine) are SATA.
While a SATA SSD still has incredible throughput a PCIE-e SSD is currently the fastest available on the market. Can often be twice as fast as a SATA.
I know, and the latency is better too, but I was wondering if he went from HDD to NVMe or SATA SSD to NVMe. An SSD is plenty faster than HDD, but the game still got slow. I even tried it on a RAMdisk once (DDR3) and even that did not help.
-
I know, and the latency is better too, but I was wondering if he went from HDD to NVMe or SATA SSD to NVMe. An SSD is plenty faster than HDD, but the game still got slow. I even tried it on a RAMdisk once (DDR3) and even that did not help
I had a middle grade SSD, a Crucial drive.
Another thing with SATA vs NVMe, is that SATA was designed for spinning disks, so NVMe is the first real standard designed to handle non sequential IO. My benchmarks show almost x5 IO performance over my old SSD. Of course, a lot of that likely gets bottle necked somewhere else, as you are at that point sucking out data faster than anything else can really handle. Still, I was wondering if the ability to parallel, small IO might make database IO better and thus improve turn times. Or then again, maybe its placebo effect :)