SQL versus HashMap

Im wondering if SQL is faster/better/safer than using HashMap's?

I use SQL to store my inventories and DigiLocks directly to the database instead of using HaspMaps and then save them later. What is best / fastest / safest?

 

Very different types of things your trying to compare there...

 

You can't serialize inventory objects. Use hashmaps if you are keeping them in the cache, and then if you want to store them using SQL, you'll have to save every datatype and store each datatype separately.

 

Thats exactly what I have done

I know I cant serialize an inventory object BUT I can serialize the data in the slot in the inventory and thats what I have done.

 

I am using a similar concept; I am storing a bunch of data locally in a hashmap, and then on an automatic timer exporting the entire hashmap to an SQLite database on a 15 minute interval.
This worked fine and dandy on my test server, but when I installed my plug-in on my public server it began to “Server can’t keep up” when the plug-in executes an export.
Currently my system is dropping the table then regenerating it, so that could be a contributing factor to the issue (I am going to recode it later this evening to just update records instead of dumping the whole table and re-create it).

The second issue that may be causing my problem is that I am using a Synchronous Tasks timer to call my database export every 15 minutes, not an Asynchronous. What this means is that I am using the main MineCraft thread to execute my database export, not a dedicated thread. Because of this as my database size grew, the time it took to execute the export did as well, resulting in main MineCraft thread bogging out (“Server can’t keep up”).
I used the Synchronous Tasks timer because it was advised to use it due to synchronization issues, but on further contemplation I think that I should be safe from any sync issues because I never call the Bukkit API in my export functions (I actually do once to get a blockatlocation… but I will fix that).

So I guess this post is trying to accomplish two things, one is to warn you to be careful about populating a map and then exporting it all to a SQLite database, it can bog out your main MineCraft thread

Secondly, I have a question. New records aren’t being generated by my plug-in very often; I would be amazed if it was ever over 50 in a minute (and at the moment it’s more like 1 to 5 every hour). So my question is this…

Would it be faster to write to both my HashMap and SQLite database at once when the record is being created and use the HashMap for the server use and the SQL for data persistence, or would it be faster to do what I am doing now, which is store all data in the HashMap when it is created, then every 15 minutes export the entire table to the database?

I want to optimize this to be as efficient as possible because I plan on releasing this plug-in to the public and I want it to be efficient for larger servers. Thank you to anyone who is able/willing to lend their experience to me!

 

SQL will never be faster than Hashmaps. Hashmaps are in RAM, and lookup time is considered to be 1 step and instant (for most purposes). SQL is also one step, but has to pass through an intermediary connection, making it slower.

Of course, the advantage to SQL is that it is persistent, whereas Hashmaps are cleared on server shutdown or reloads.

 

Thank you for your response Aforress, I am always happy to see when you join a conversation! However, I feel I need to explain question with a bit more detail.

I agree with you fully with what you previously stated, but my question isn’t regarding which data storage medium is faster, but which exporting method is more resource intensive.

Will it consume more resources to export data to a SQLite database every time a record is created, or will it consume more by doing a big batch of them all at once? Regardless the server will continue to store and retrieve data from the hashmaps for the server’s functionality; I’m purely talking about the exporting to SQL for data persistence.

So if the server needs some stored data it will be retrieving it from the hashmap and not the SQL database; But which will bog the server out more when it needs to export data to the SQL database for retrieval after a server restart, doing it consistently as the data is created or storing it in a HashMap and then every so often dumping it all at once?

I ask because the method I am currently using (storing it in a hashmap and dumping all at once) is causing the server to throw the “Can’t keep up” console error every time it exports the hashmap. I am trying to identify what is bogging out my MineCrafts main thread during the data export to SQLite (Is it because its dumping the old table and not updating, is it because it’s exporting every record at once, etc).

Also, by moving this SQL data export to an asyncronized thread and not a synchronized thread is it still possible for this export to bog out the servers main thread, or can the export take as long as it wants? I don’t care how long the export takes as long as it consistently exports the data without bogging down the server (lag spike is what I mean by bogging down in case you didn't know).

I am rambling and restating my point, I am sorry for that but it’s been a difficult problem to verbalize. Thank you once more for lending me your extensive experience!

 

More resources will be used every time a record is created then a batch. If possible, use a batch.

Dumping the hashmap in intervals in a batch update to the SQL will be faster.

Because the SQL operations don't have to be on the same thread as the data is stored (batch updates), I recommend you move SQL updates to another thread. This will prevent the lag during batch updates. You will need to add a bit of synchronization when you move hashmap contents from the main thread to the SQL thread.