Lua Tables VS Sql Queries

[Codinablack]

Ok so I have seen some people take different sides on this arguement and so I wanted to see what the developers here at otland think about this subject.

Discussion is about performance, functionality, maybe even modularity. The pro's and con's that anyone can think of, for using one or the other.

SERVER TFS 1.1

Meaning: Lua Tables, we are using to store w/e data, large amounts to small, versus storing the data in a sql database.

What do the pro's think?

@Printer @Ninja @Limos @Evan @cbrm @Evil Puncker @Evil Hero @zbizu @dominique120 @Summ @Red @forgee @Flatlander @Jotran

Just a few I know that are skilled in these area's, What do you guys think?

 

[cbrm]

TFS?

 

[Codinablack]

TFS?

TFS 1.1

 

[Codinablack]

Actually I would prefer the discussion to be on the general usage. Say if we were using optimal conditions for both methods, but if we must choose a type of TFS to get the topic moving, then of course 1.0 or 1.1 just 1.x

 

[Syntax]

You're comparing two completely different things.
Since Lua tables are stored in memory, they should be used for data that is only relevant for the lifetime of the server and does not contain important data.
SQL queries should be sent on server save or player save if possible to avoid desync of information.
If the information is important or desync isn't applicable, it's fine to send the query right away.


If you're talking about storing information offline in Lua files vs a SQL database, that's another discussion.
There are many talks about file storage vs SQL storage pro's and con's. I would go with SQL storage for any persistent OT data.

 

[Codinablack]

about storing information offline in Lua files vs a SQL database

That is exactly what I am talking about, like the challenge eldin issued to otland to make a pet system, there were people working on saving that data in lua tables, and zbizu said sql was better. I personally thought that performance wise lua should be way more op, think about it, it's like you said it's loaded in the RAM right there ready for access, but when you store it on a webserver like that then things are more dependent upon bandwidth and other things as well... I mean of course that depends on how you have your servers set up, but if the data can still be saved and loaded on startup why would sql be better?

 

[Summ]

Store the persistent data in your SQL database while the server is offline.
You can load the information into memory on startup / on player login so you can access it and save it back on server save / player save / shutdown.

 

[Flatlander]

I agree with Summ.

Anything you want persistantly saved when offline should be saved in the SQL.

I don't have any benchmark data or anything to back this up, but I believe sending a SQL Qeury is probably more efficient than opening a file and reading it.

So for example, if you had a file for every player (which the old XML servers did), every time a player logged on, or saved it would have to read and write to those files.

Now all it does is send SQL Queries.

 

[Syntax]

When it comes down to it, you have either disk storage or memory storage.
SQL is an answer to problems with flat file storage.

Stealing this from stackoverflow, but this points out just a few reasons to use Database storage.

1. Databases can handle querying tasks, so you don't have to walk over files manually. Databases can handle very complicated queries.
2. Databases can handle indexing tasks, so if tasks like get record with id = x can be VERY fast
3. Databases can handle multiprocess/multithreaded access.
4. Databases can handle access from network
5. Databases can watch for data integrity
6. Databases can update data easily (see 1) )
7. Databases are reliable
8. Databases can handle transactions and concurrent access
9. Databases + ORMs let you manipulate data in very programmer friendly way.

You can rig a setup that handles some of those problems, and some problems may not apply to you, but why would you not use SQL storage since it's already implemented.
Don't replace the wheel with a rock.

 

[Codinablack]

So far we have 3 people that believe that storing the data in the database is best overall, performance and everything... Is there anyone who would disagree with that?

 

[Nu77]

If you need keep the data stored even after shutdown sql queries will be the proper solution, but if you need something temporary using lua tables will do the work very well.
All depend what you want to do, when you has to think about it surely will find the best solution.

 

[Codinablack]

If you need keep the data stored even after shutdown sql queries will be the proper solution, but if you need something temporary using lua tables will do the work very well.
All depend what you want to do, when you has to think about it surely will find the best solution.

The thing is we can store the information in lua and it still be there and loaded on shutdown and startup, since people oftentimes make tables and talk about the speed of lua, I was curious as to why lua wouldn't be faster, especially since the data is already in the RAM

 

[Evil Hero]

I'm quite on the other case, I've been consistently working with lua storage files lately and when it comes up to I/O lua & luajit handles this in a really fast and good way, ofcourse we have to take the hdd in consideration.
I/O is ofcourse a lot more efficient if you run the files on a ssd instead of a hdd.
Another case is how tfs currently handles queries which is still quite the hassle, as the sql server could handle it far better then tfs is actually making use of.
I somehow don't like the fact that's it's rather not convenient to load / save data through the luasql bridge which used to bug out a lot of times for me in the past if you do the slightest misstake with it.

Here is a list of pro's and con's for lua (my opinion)

1. You don't have to load information which you basicly don't need at the moment (chunk loading) (if you want to handle queries through the sourcecode and not through the lua bridge) -- pro
   
2. You can save tables easily from ram into a file without much effort and clear the data out of memory once it's saved to free your ram (efficency) -- pro
   
3. You are not dependable on a luasql bridge which sometimes doesn't work properly and you also cannot handle multiple queries at the exact same time. -- pro
   
4. If you want to save something new in your sql database then you have to create a table etc. therefore first, lua however can handle this on the fly without the need of beforehand preparations. -- pro
5. You can make a good use of modular systems and handle extensions for systems (like a pet system) easily by just adding the required stuff into the already made table and it'll be saved without any changes made. -- pro
   
6. If you don't setup your tables correctly and use to much different files for the same meaning it'll get messy and slows down everything. -- con
7. If you need to load all lua files at the server start it'll definitely be slower then if you use sql. -- con

What I basicly just wanted to say is the following:

If you know your stuff in lua, then you can make this for your own sake really efficient and it can help you in a lot of different ways without messing to much around with your database.
However if you are a pure beginner to lua I wouldn't recommend to even touch it, because it might cause more harm to your server then you'll do good to it

 

[Syntax]

I'm quite on the other case, I've been consistently working with lua storage files lately and when it comes up to I/O lua & luajit handles this in a really fast and good way, ofcourse we have to take the hdd in consideration.
I/O is ofcourse a lot more efficient if you run the files on a ssd instead of a hdd.
Another case is how tfs currently handles queries which is still quite the hassle, as the sql server could handle it far better then tfs is actually making use of.
I somehow don't like the fact that's it's rather not convenient to load / save data through the luasql bridge which used to bug out a lot of times for me in the past if you do the slightest misstake with it.

Here is a list of pro's and con's for lua (my opinion)

1. You don't have to load information which you basicly don't need at the moment (chunk loading) (if you want to handle queries through the sourcecode and not through the lua bridge) -- pro
   
2. You can save tables easily from ram into a file without much effort and clear the data out of memory once it's saved to free your ram (efficency) -- pro
   
3. You are not dependable on a luasql bridge which sometimes doesn't work properly and you also cannot handle multiple queries at the exact same time. -- pro
   
4. If you want to save something new in your sql database then you have to create a table etc. therefore first, lua however can handle this on the fly without the need of beforehand preparations. -- pro
5. You can make a good use of modular systems and handle extensions for systems (like a pet system) easily by just adding the required stuff into the already made table and it'll be saved without any changes made. -- pro
   
6. If you don't setup your tables correctly and use to much different files for the same meaning it'll get messy and slows down everything. -- con
7. If you need to load all lua files at the server start it'll definitely be slower then if you use sql. -- con

What I basicly just wanted to say is the following:

If you know your stuff in lua, then you can make this for your own sake really efficient and it can help you in a lot of different ways without messing to much around with your database.
However if you are a pure beginner to lua I wouldn't recommend to even touch it, because it might cause more harm to your server then you'll do good to it

Set up a NoSQL server at that point.

 

[Evil Hero]

Set up a NoSQL server at that point.

There was an already existing alpha server years ago handling everything completly in lua, which worked quite well tbh (Nightfall 861 Alpha v2).
I don't have the knowledge myself (c++ wise) to be able to change everything necessary in the source code, else I would have given it a shot already

 

[Codinablack]

I'm quite on the other case, I've been consistently working with lua storage files lately and when it comes up to I/O lua & luajit handles this in a really fast and good way, ofcourse we have to take the hdd in consideration.
I/O is ofcourse a lot more efficient if you run the files on a ssd instead of a hdd.
Another case is how tfs currently handles queries which is still quite the hassle, as the sql server could handle it far better then tfs is actually making use of.
I somehow don't like the fact that's it's rather not convenient to load / save data through the luasql bridge which used to bug out a lot of times for me in the past if you do the slightest misstake with it.

Here is a list of pro's and con's for lua (my opinion)

1. You don't have to load information which you basicly don't need at the moment (chunk loading) (if you want to handle queries through the sourcecode and not through the lua bridge) -- pro
   
2. You can save tables easily from ram into a file without much effort and clear the data out of memory once it's saved to free your ram (efficency) -- pro
   
3. You are not dependable on a luasql bridge which sometimes doesn't work properly and you also cannot handle multiple queries at the exact same time. -- pro
   
4. If you want to save something new in your sql database then you have to create a table etc. therefore first, lua however can handle this on the fly without the need of beforehand preparations. -- pro
5. You can make a good use of modular systems and handle extensions for systems (like a pet system) easily by just adding the required stuff into the already made table and it'll be saved without any changes made. -- pro
   
6. If you don't setup your tables correctly and use to much different files for the same meaning it'll get messy and slows down everything. -- con
7. If you need to load all lua files at the server start it'll definitely be slower then if you use sql. -- con

What I basicly just wanted to say is the following:

If you know your stuff in lua, then you can make this for your own sake really efficient and it can help you in a lot of different ways without messing to much around with your database.
However if you are a pure beginner to lua I wouldn't recommend to even touch it, because it might cause more harm to your server then you'll do good to it

See that's how you do it! Pros and cons, thank you very much for that. Now that all being said, we now have 4 for sql, and 2 (one being myself) for lua. OK so now we can see atleast two cons to using lua, does anyone else know of any more? Also if we were to take out "the way tfs handles queries" as a factor, say they utilize it to the fullest extent, would you still be on the same side?

@Syntax what is a nosql server?

 

[Syntax]

See that's how you do it! Pros and cons, thank you very much for that. Now that all being said, we now have 4 for sql, and 2 (one being myself) for lua. OK so now we can see atleast two cons to using lua, does anyone else know of any more? Also if we were to take out "the way tfs handles queries" as a factor, say they utilize it to the fullest extent, would you still be on the same side?

@Syntax what is a nosql server?

It basically has all the pro's @Evil Hero listed.
There's no structure, it's all key-value stores.
You can have full memory database like Redis, with persistent file saves.
Or you can have something like MongoDB which uses a memory cache in addition to file storage (similar to SQL)

Flat file storage (Lua) has more cons than two, I listed many in my previous post.
I'm not quite sure why you're making this a poll, these are two different types of storage and they both have their uses in different scenarios.
It's the developers job to determine which he should use in a certain situation.

 

[Evil Hero]

See that's how you do it! Pros and cons, thank you very much for that. Now that all being said, we now have 4 for sql, and 2 (one being myself) for lua. OK so now we can see atleast two cons to using lua, does anyone else know of any more? Also if we were to take out "the way tfs handles queries" as a factor, say they utilize it to the fullest extent, would you still be on the same side?

@Syntax what is a nosql server?

If the overall sql communication would be solved in a better way, then I would consider changing my opinion

It basically has all the pro's @Evil Hero listed.
There's no structure, it's all key-value stores.
You can have full memory database like Redis, with persistent file saves.
Or you can have something like MongoDB which uses a memory cache in addition to file storage (similar to SQL)

Flat file storage (Lua) has more cons than two, I listed many in my previous post.
I'm not quite sure why you're making this a poll, these are two different types of storage and they both have their uses in different scenarios.
It's the developers job to determine which he should use in a certain situation.

Well ofc it has more cons, but I didn't wanted to re list what you already said
You also hit the nail on the head with your last two sentences, couldn't have said it better

 

[Summ]

There is not much of a point to store your user data in a lua table on your hard drive. You would not even be able to display any of the information on your website.

In the end they are 2 different things. I would not save my player & account data in a lua file but I would neither save the reward id of Annihilator in a database.
It depends on the scenario you are facing.

Edit: Somehow you also manage to make the notifications not work.

 

[Codinablack]

I'm not quite sure why you're making this a poll

This is why.

It's the developers job to determine which he should use in a certain situation

I have not had any good experience yet with mysql, yeah it has its benefits but I have crappy Internet and the fact that the server has to have constant connection with the database has always been an annoyance, when using an older version of tfs it used to crash because my internet is so crappy, very very old phone lines, anyways, that's not a problem with 1.x of course but I am trying to figure out why so many reputable people, who's opinions I highly value are telling me sql is better, but I see no problems with lua, like you said it is the programmers job to determine which he should use for which situations, thus I ask here where there are opinions I trust.