Saving Key/Value Pairs (cont'd)

[Lokey]

First ditto on monezz's post, using that method as well.

Also agree with JeroenB above that the differences in time to execute between local or NWNx database is probably minimal.

[Undertowe]

Monezz, the approach you described sounds essentially the same as my approach. Except, I hadn't decided on a way to determine which variables to read from the PC object and store in the DB during a "save point."

As for your solution to that problem: consulting what is currently stored in the DB to determine what to look for on the PC, I agree that this is one way to do it. But, it sure does seem like an awful lot of reading from the DB. If we take into consideration that you would already be reading those values from the DB OnClientEnter, and then again during each "save point", and also OnClientLeave, the increased efficiency of such an approach becomes more and more dubious, at least compared to "real-time" reading/writing to the DB.

Well, on one hand we have Monezz and Lokey taking the approach of "caching" variables on the PC object (I knew you guys were out there somewhere!). On the other hand, we have JeroenB insisting that we are better off bypassing that step completely, because of increased overhead, and such.

Wow, I'm still not sure what the most efficient approach is. Is there some quantitative data out there somewhere?

Lokey, I'm curious: Why are you using the approach that Monezz described if you agree with JeroenB that the differences in execution time between accessing local and DB data is minimal? Did you start off thinking one way, and then change your mind?

Monezz, you say that for a given set of records, deleting them all and inserting them anew is, "easier, cleaner and faster" than updating them. This seems very counter-intuitive to me. Could you please elaborate?

[monezz]

insert/delete vs updates:
easier:
You don't have to worry about the existing values in the database. Only the values stored on the character are stored in the database. So its very easy to control youre persitent parameters through the local variables.

cleaner:
When you decide you don't need a parameter for a character anymore, it will be deleted from the database. So you always have only acurate parameters in youre db.
(I had some experiences with MyIsam tables creating overhead on update statements, but I haven't done any research on it.)

faster
Update statements require more extensive locking. I'm not sure how it affects MyIsam tables since tables of that type only can be locked on table level. I have done large experiments and benchmarks with updates vs delete/inserts on oracle. Oracle uses more or less the same locking aproach as the innoDB table type in MySQL. The delete/insert aproach was in some situations more than 10 times faster!

Monezz.

[monezz]

[Papillon]

If we would release a little module that allows to accurately time things, would someone care to enlighten us with some data on the various approaches discussed here ?

In Avlis, I used approach 3. There's an include file aps_cache, that transparently caches read and writes. It's basically a write-through cache. I have a feeling that this approach is the most balanced one, without actually beeing able to rely on any valid performance data. Just a feeling.
_________________
Papillon

[monezz]

With a timing module, I could set up a test environment.
I have a nwn server and seperate MySQL server availiable.

[Lokey]

[Papillon]

I have put up a timer plugin on the download page, so test away !
_________________
Papillon

[Asmodae]

With performance consideration in mind, recall that MySQL is a disk based system, so reads/writes will never be faster than your HDD's seak/access/read times. MySQL does a world of wonders to optimize that, but keep that in mind. Executions in RAM (ie local vars and such) will be faster as a matter of course as long as its within reason. I did a test with several thousand local variables not long ago looking for memory leaks.. and found that anything past 2000 was a visually NOTICEABLE performance difference when accessing a local variable on an object. When I say noticeable I mean watching output messages. So the actual limit you want for performance is FAR less, I would say make sure you have less than 100 on any given object as an absolute maximum.

A balance must be struck. Optimized and normalized tables are a great way to tune up your database, combine your queries into one rather than several calls, make use of indexes and combined primary keys for maximum efficiency. So rather than do a dozen reads/writes to various database variables in seperate calls, it is better to combine them to one SQL statement so that MySQL can use its internal optimizations.

Also in some of the cases listed you mention Many-to-Many style relationships which can be handled a number of ways, but the best way is a linker table. For example: in Exodus we use custom quests, so there's a table for those, and a table to store common information about characters. Since one character can do many quests, and one quest can be done by many characters there's a linker table in the middle with a primary key that is a combination of the characterID and the QuestID. Each data pair is a unique table entry, and since its a combine primary key it is highly optimized for searches and joins, etc.

After a lot of research I found that's the best way to do the kind of data relation you describe inside MySQL.
_________________
Nepenthe - An NWN2 Persistant World, coming to a planet near you. http://www.nepentheonline.com

[Papillon]

Btw, did anybody test MySQL vs. ODBC connection performance ? I suspect switching from ODBC to MySQL might not be the big improvement some people ( ) have been preaching all the time... at least my scorco tests showed virtually no difference when dealing with objects.
_________________
Papillon

[weldieran]

How is it that you would loop through and save to DB onClientExit?

It was my understanding that very little is available onClientExit - GetName namely.

Are variables stored on the PC Exiting still accessible? But inventory is not right? So variables stored on inventory items is not accessible?

[Alosynth]

The PC object still exists after the player leaves. However, the GetPlayer* functions don't work to retreive player information from the object because the player is detached from the object.

Theres some more detailed information on what happens with On Client Leave here