Database driven loot system?

[Kato]

Hi all,

Anyone knows if it would make sense to build a loot system relying on an object table(MySQL) rather than using CreateItemOnObject() or CopyItem()?

Currently my loot system retrieves items from hidden chests and uses CopyItem() to create the item in object's inventory(just as many loot systems do). It works fine, but requires one chest for each item type(gems, weapons etc...), and the more items I add to the chests, the more ressources the system uses, since for each stored item(the module is growing bigger and bigger with each added item), an object variable + an int variable for indexing are stored on the chest for later retrieval.

I know that CopyItem() is faster than CreateItemOnObject(), but what about retrieving the object(randomly) from a table(BLOB type, if I recall) with MySQL? Would it be fast enough to prevent any lag?

Thanks!

[virusman]

It won't be any faster than CopyItem(); the main advantage of DB is flexibility. In terms of performance, it will be the same or a bit slower than chests.
_________________
In Soviet Russia, NWN plays you!

[Kato]

Thank you, virusman. When you say a bit slower, do you mean that it would be feasible nonetheless? I'm asking this because my goal is to get rid of those chests and items and variables, relying solely on the database for my loot system. Now, I know that some builders prefer to store the resref instead of the whole object, yet this implies using CreateItemOnObject().

So, wich option would create the less lag? Retrieving the resref + CreateItemOnObject() or simply retrieving the whole object from the DB, knowing that the items are created in the OnDeath event of monsters and the OnOpen event of some placeables?

Thank you very much for taking the time to answer this

[virusman]

Database latency is probably higher than object->GFF conversion, so I'd still use chests for caching (populate with items from database on module load, then reload cache when DB contents are changed).
The main question here is how flexible you want it to be. There is no point in switching from in-game to DB objects solely for performance.
_________________
In Soviet Russia, NWN plays you!

[Kato]

Thank you, I'll stick to the chests for now, yet I still wonder what will happen when those will hold 500+ items...

[Skywing]

Note that both the item creation routines and the database deserialization support involve unpacking an object from its saved GFF form - so you pay the cost for that work either way. As virusman notes, with the database, you just have the additional work of pulling things from MySQL.

[eeriegeek]

I am currently generating all treasure drops via database look-ups on my (always under development) PW. There has not been a problem with performance with 6500 (2200 items in 3 grouping systems) items in the table, but the PW is admittedly lightly loaded. You have to be careful how you design your random look-ups so you don't end up doing full table scans for each drop.

[Kato]

Indeed, thank you for this precison.

[elven]

Querying a good database doesn't really involve that much overhead, latency-wise. Most things will be in RAM and the worst to happen is some indirection and hitting the query planner. The overhead for de/serializing gff data will be much much bigger than doing a simple query on a properly indexed table. Either way, it doesn't matter - it's fast enough in any case even under heavy load.

I as well run a loot system based on wildcard-matched resrefs, with random chance for various items and lots of flexibility. No problems there, and it really cut down on maintenance regarding utc templates.

[Kato]

Thank you for the infos, Elven. By the way, would you agree to give me some clues regarding the formula used to randomly retrieve the "weighted" items, please? (My table would hold the fields "resref", "category", "weight" and "rarity", the last one being used to update weights upon adding/removing items from any category)

Thank you!

Kato

[elven]

[Kato]

Thank you very much!!!

[Baaleos]

I wouldnt replace the chest system completely, at least, not at first. I instead chose to supliment it.

http://rhun.wikia.com/wiki/Development_:_Dynamic_Loot_Generator

This is a walk through of how our Dynamic Loot generator works.


It works on the simple premise of

1 - Select a random item type/resref from the stored item details database table.

( select * from item_templates order by rand() limit 1 )

This gets us the resref of the template.

We create it.
We can then get its item type, and then we can further query the database for the item properties we desire for that item type.
eg - Select the itemproperty int value for Damage Bonus 2d12 Divine for item type Long Sword etc....


Our system works well, and doesnt have noticable lag.


Note - The sql Queries are blocking, so its important to remember that the further away your db is, eg- off site, then the longer the lag/delay between query start, and query end will be.

[Kato]

Thank you, Baaleos. Since you mention retrieving resrefs with RAND(), I seize the opportunity to share/evaluate a formula/query I found after a lot of researches:

SELECT resref FROM items WHERE category = (SELECT category FROM items ORDER BY -LOG(1.0 - RAND()) / avail limit 1) ORDER BY -LOG(1.0 - RAND()) / weight LIMIT 1

Now the goal is to retrieve a random weighted item resref from a random weighted category. The higher the weight, the higher the chance for the resref to be picked. Same for category, this time the "weight" being the field "avail".

The query is made for a table with fields: resref, category, weight, avail. For weight and avail, I currently use values between 1 and 99. The query works, and it takes 0,0472 seconds to execute, I don't know if it's considered fast or not...

However, for categories, the subquery is not flawless, it will tend to pick categories containing the most resrefs more often. Any idea on how to improve the subquery? (or the whole query if it's wrong)

Thank you!

Kato

[Kato]

I should be ok, thank you. A MySQL expert advised me to create 2 tables, simplifying the queries a lot. Thank you all for reading and answering this.