Database performance

[Papillon]

Here is an excerpt from the updated ODBC2 documentation.

To give you an idea what to expect from the various database options, we conducted a small test involving 500 writes and reads. Note that this test is very artificial, since many aspects like table fragmentation, concurrent access, database size, and more realistic queries are not factored in. All tests were done on a Athlon 64 3200+ with database server, NWServer, and NWClient running local (NWClient with reduced process priority).

Writes were done with the following code:

[RansomStark]

I'm setting up a new PW with NWNX and I've got choices in front of me.
I started, since I've worked a bit with MySQL before, by installing MySQL with an ODBC layer as was outlined in the tutorial on the frontpage of nwnx.org.

I then downloaded the Database plugin and I found all of these comparisons...

So the question is, is there any disadvantage in using the built in SQLite?
It seems fastest overall....

Can I run queries on it externally to NWN?
With MySQL I know how to administrate, but with SQLite?
Is there a command line interface or ...?

I'm going to be running a single-server single-computer PW if that helps for context. The DB is going to be maintained on the same local box, so no need for networking interfaces. Also the OS will be windows, so no need for inter-OS layers...

Any thoughts?

[Primogenitor]

Here a link to the thread with links to the windows SQlite DB viewer. It has a SQL query box built in. http://www.nwnx.org/phpBB2/viewtopic.php?t=110

Main disadvantage to SQLite is that it is transaction based. So unless you regularly close your transations with COMMIT and start another one with BEGIN TRANSACTION if your server crashes (power loss for example) youll loose all the data since the last commit because it only exists in memory. http://www.nwnx.org/phpBB2/viewtopic.php?t=1419 esp the end)

[Grinning Fool]

I'm curious, is pwdata indexed for SQLLite and mysql? If not, it may be worth re-running with an indexed version, since I think you''ll see some noticeable differences in read/write times.

[Papillon]

Well, most DB professionals would say that having transaction support is a major advantage It may not be a simple to handle as a database without transactions and especially not in NWN, though, that is why I am starting the implicit transaction in the beginning.

The simplest way to get around this is to do a commit once every minute with DelayCommand.
_________________
Papillon

[RansomStark]

[Primogenitor]

Its the Can't seem to write value to SQLite db - blank values thread in this forum, 2 lower than this topic at the moment.

[redils]

Papillon, could you perform a postgresql test with your machine ? I'd like to compare it with the other RDBMS. Thx

[Papillon]

Unfortunately, I don't have postgresql installed on any machine.
_________________
Papillon

[twinj]

What is the same comparison with the object memo functionality?

Id like to know .

If I use a system where I only read and load player data into memory once .. and flush the data from memory as needed then Bioware DB would be a better answer for me?

What are any disadvantages or advantages will I get if I go to MySQL for example? A read speed of 10ms is nice but doesnt really matter if I read all data from memory anyway.

I use an object(uti) based database system and would like to see if changing to MySQL storage would benefit me in any way using the new HOOKing feature?

The system is fast and accurate and the only data loss i have experienced is when I used teh wrong functions... doh!

Would like some more comparisons considering I get better read/write performance with standard int's and strings from this system? Thanks

BTW thanks for such a handy system for PW's use its been a life saver and great addition to NWN! ( we currenlty use a couple of the modules available )

[Acrodania]

Beyond just "speed" comparisons there are other advantages to using MySQL or SQLite instead of the Bioware database or even object variables (those are very fast).

Advanced searching is a HUGE benefit. Can you accurately tell who has what in their journal? Have they completed this quest, are on this step of another quest, etc. With a simple SQL query you can get ACCURATE counts of where people are, how many have this status, etc. Without adding any extra tracking code. You can tell also how many players have completed which quests, letting you know easily if you are running low on canned quests

The other largest benefit is the ability to SEE the data. You can easily troubleshoot and repair what you can see. With the standard DB that means manually going through each entry since you can't effectively search. And it means shutting down the server to make changes. With Item/Object variables you cannot do it henace repairing errors gets much harder very quickly....

Stability is another big advantage. MySQL or SQLite just doesn't blow up for no reason like the default database. Objects CAN get corrupted leading to loss of all data regardless of whether you store them in the Bioware DB or MySQL. It is simple to backup and recover true databases like SQLite and MySQL making it less likely you will be set back to the beginning and losing all player data.

Speed is a concern, but advanced functionality, ability to manipulate the data and stability are more so, IMHO!!! Also remember that the more local variables you set (including on objects) the larger your memory footprint and the more frequently you will need to restart your server. There is a fine line between fast access and actually causing you problems later on. If your server can handle adding/retrieving data from various systems straight from the database while at full load you will stay up longer doing it that way instead of using local variables for everything. It all depends on how much data is being past and how often.