SQL Lite

[sage_chaozu]

I've been reading around and noticed there has been an update that gives us access to a SQL lite database. This is a really nice addition to graal but I was wondering what the performance is like when your database gets bigger. I know that the old way of scripting, that we would store inventory specific variables on the player with clientr. We would also store economy using serverr. Should we replace this functionality with the sql queries?

[WhiteDragon]

It will perform better than anything Graal has built into it if you have a lot of data. If you only have small amounts of data (which I think classifies 99% of Graal), avoiding the overhead of SQLite is probably faster.

But it any case, it's an extra layer of complexity. Sometimes that complexity is required, and if you find yourself implementing data structures in GS2 to help you lookup data faster, then you should probably starting using SQLite since it's already done for you.


It's also important to note here that it's really easy to suck at SQL (just like anything else) and make everything run slow as hell. You need to be careful with how you define your schema and queries, make sure to index properly (and sometimes even give up certain relational properties) in order to maximize speed. If you aren't careful you can definitely make stuff run slower than whatever you would do in GS2, probably.


So in summary, it's another tool, and imo it only needs to be used when you have particularly large amounts of data, and when you do, you need to go read about a bunch of stuff regarding relational databases and proper schema design.

[fowlplay4]

SQL Advice: Make sure you learn about normalization (importantly learning up to 3rd normal form) if you want to be "good" at it.

I would recommend re-thinking your approach with the "serverr." values if you feel that SQL is a good alternative to it.

Explain what exactly you're trying to do first please.

[cbk1994]

Any form of SQL is only as fast as you make it. Data stored in SQLite tables is not stored in memory, so it takes longer to get that data. Depending on your scenario, you may want to cache this information in player variables. Some reasons for using SQLite for data storage are easy offline access, easy backup, organization, and easy querying/retrieving that data.

Ultimately if you decide to use SQLite, index it well and make sure you know how to use indexes. This page in particular may be helpful. Read section 1.1 carefully so you understand how the left-most column in indexes works; if you don't, your indexes won't do ****.

[sage_chaozu]

Originally Posted by fowlplay4

SQL Advice: Make sure you learn about normalization (importantly learning up to 3rd normal form) if you want to be "good" at it.

I would recommend re-thinking your approach with the "serverr." values if you feel that SQL is a good alternative to it.

Explain what exactly you're trying to do first please.

Well, nothing too fancy. Just imagine using it for keeping track of money (all "objects"), keeping track of inventory, keeping track of event statistics (wins, details) and other things. Of course this will be done with relational tables.

[fowlplay4]

Originally Posted by sage_chaozu

Well, nothing too fancy. Just imagine using it for keeping track of money (all "objects"), keeping track of inventory, keeping track of event statistics (wins, details) and other things. Of course this will be done with relational tables.

Go with SQL then, especially if you plan to keep track of quantities, and where they're stored exactly.

[WhiteDragon]

Originally Posted by cbk1994

Data stored in SQLite tables is not stored in memory, so it takes longer to get that data.

I think this is a little overly general.

To refine it a little for anyone who is interested:

1. Indexes are stored in memory. This means that looking up the rows you need will be fast, only reading it after finding it will be slow.
2. But, even reading the rows won't neccessarily be slow either, since SQLite keeps a cache of a bunch of rows in memory at all times. When reading hot (frequently/recently accessed) data, it'll be from memory.

Similarly, writes are done into memory, and the way Graal's SQLite stuff is set up is that it doesn't wait to write to disk before returning. So a write will just take as long as writing anything into memory.


But even with this, if you need the guarantee that something will be in memory, it's a good idea to keep it in a regular ol' client variable since you may not want the lag if the data is cold and its needed for something important (e.g., sword strength or something which needs to be there in real time).


But yeah, complexity. It may not be worth all this.

[sage_chaozu]

Originally Posted by WhiteDragon

I think this is a little overly general.

To refine it a little for anyone who is interested:

1. Indexes are stored in memory. This means that looking up the rows you need will be fast, only reading it after finding it will be slow.
2. But, even reading the rows won't neccessarily be slow either, since SQLite keeps a cache of a bunch of rows in memory at all times. When reading hot (frequently/recently accessed) data, it'll be from memory.

Similarly, writes are done into memory, and the way Graal's SQLite stuff is set up is that it doesn't wait to write to disk before returning. So a write will just take as long as writing anything into memory.


But even with this, if you need the guarantee that something will be in memory, it's a good idea to keep it in a regular ol' client variable since you may not want the lag if the data is cold and its needed for something important (e.g., sword strength or something which needs to be there in real time).


But yeah, complexity. It may not be worth all this.

Then what would one use a SQL lite database for? It's starting to seem like a lost cause due to performance issues. Then again this does guarantee secure data storage and manipulation. I believe this is due to SQL transactions actually locking the DB table before writing to avoid overwriting.

Hopefully if I keep this simple and everything organized it will all fall into place.

[WhiteDragon]

Originally Posted by sage_chaozu

Then what would one use a SQL lite database for? It's starting to seem like a lost cause due to performance issues. Then again this does guarantee secure data storage and manipulation. I believe this is due to SQL transactions actually locking the DB table before writing to avoid overwriting.

It's faster because it uses proper data structures. In particular it uses a B-Tree for each index you make, which allows you to look up a row based on a certain condition in sub-linear time to the amount of rows you have.

It's written in C, so it's implementation of a B-Tree will be far faster than anything you could do in GS2.


Most of the time your entire dataset will be loaded into SQLite's memory anyways so you don't really need to worry about the disk.

[cbk1994]

Originally Posted by sage_chaozu

Then what would one use a SQL lite database for? It's starting to seem like a lost cause due to performance issues. Then again this does guarantee secure data storage and manipulation. I believe this is due to SQL transactions actually locking the DB table before writing to avoid overwriting.

SQLite offers many benefits over using flatfile or DBNPCs for storage.

For example, if you have a bank, and you want to find every account with over $5000 in the bank, you can run a query "SELECT * FROM bank WHERE money > 5000 ORDER BY money DESC". The equivalent code using a DBNPC would be something like

PHP Code:

temp.found = null;
for (temp.account : this.bank.getDynamicVarNames()) {
  temp.balance = this.bank.(@ account);
  
  if (balance > 5000) {
    found.add(account);
    found[found.size() - 1].balance = balanace;
  }
}

found = found.sortByVar("balance", "float", false); 


There are many reasons to use SQLite but also as many reasons not to use it. It really depends on your usage case.

The performance issues are not that big, either.

Originally Posted by cbk1994

It depends a lot more on how you structure your system than the limits of SQL.

A quick example:

PHP Code:

function r(str) {
  echo("+" @ (timevar2 - this.start) @ ": " @ str);
}

function onCreated() {
  this.join("func_sql");
  
  req("DROP TABLE IF EXISTS test_2");
  
  // this only has to be done once
  sqliteEnableFileSynchronization("default", false);
  this.start = timevar2;
  
  // begin transaction
  
  r("Beginning transaction");
  req("BEGIN");
  
  r("Creating table");
  req("CREATE TABLE IF NOT EXISTS test_2 (account TEXT NOT NULL DEFAULT '', itemid TEXT NOT NULL DEFAULT '', quantity INT NOT NULL DEFAULT 0)");
  
  r("Creating index");
  req("CREATE INDEX IF NOT EXISTS idx_test ON test_2 (account, itemid)");
  
  temp.items = {"uzi", "handgun", "shotgun", "ak47", "medpack", "skateboard", "ammo"};
  
  r("Adding 70,000 rows to table");
  temp.c = 0;
  
  // add 70,000 rows to the table
  for (temp.item : items) {
    for (temp.i = 0; i < 10000; i ++) {
      req("INSERT INTO test_2 (account, itemid, quantity) VALUES ('" @ i @ "', '" @ item @ "', " @ int(random(1, 1000)) @ ")");
      
      if (c % this.maxlooplimit == 0) {
        sleep(0.1); // avoid max loop limit
      }
      
      c ++;
    }
  }
  
  r("Done adding items");
  req("COMMIT");
  r("Transaction closed");
  
  // reset time
  echo("--time reset--");
  this.start = timevar2;
  
  temp.num = req("SELECT sum(quantity) FROM test_2 WHERE itemid = 'ak47'", true)[0][0];
  r("Total number of AK47s: " @ num);
  
  temp.num = req("SELECT * FROM test_2 WHERE itemid = 'ak47' AND account = '2653'", true)[0].quantity;
  r("How many AK47s account '2653' has: " @ num);
  
  req("UPDATE test_2 SET quantity = quantity + 200 WHERE account = '2653' AND itemid = 'ak47'");
  r("Added 200 AK47s to account '2653'");
  
  temp.num = req("SELECT * FROM test_2 WHERE itemid = 'ak47' AND account = '2653'", true)[0].quantity;
  r("How many AK47s account '2653' has: " @ num); 


outputs:

See also this thread.

[salesman]

Originally Posted by cbk1994

+4.176818847: Done adding items [4.18 seconds]

this 4 seconds is probably bloated because of the sleep()s. Without loop limitations, I'd imagine it would be a lot faster.

edit: .7 seconds faster, actually =p

just tested it myself, and adding 70k rows took 3.4 seconds. So minus .7 is ~2.7 seconds.

I also tested storing the values in the DBNPC instead of a SQLite table, and it took ~15 seconds

So yeah, when used properly, SQLite is a beast.

[WhiteDragon]

Also note that inserting the rows then creating the index after that will be faster, probably, if you feel like optimizing that benchmark.