AlliedModders - Efficient Database Insertion

AlliedModders (https://forums.alliedmods.net/index.php)

- Scripting (https://forums.alliedmods.net/forumdisplay.php?f=107)

- - Efficient Database Insertion (https://forums.alliedmods.net/showthread.php?t=274733)

Efficient Database Insertion

So I am working on a plugin that logs events to a database. The event firings vary vastly. Some fire ~ at every game frame and some fire once every 5 minutes. As of right now, all callbacks record the data to a database using SQL_TQuery.

However, for such rapid event logging, is it efficient to keep on calling SQL_TQuery that frequently? Are transactions better? Give me some advice here. I have a feeling that spamming SQL_TQuery on average 15 times a second isn't a good idea but I really don't know. Transactions also have that other issue where the whole query is stored in memory (maybe even compiled into bytecode?) until the entire transaction is executed. Furthermore, if one of the queries in the transaction fails for some reason, the whole transaction is nuked.

This is an sqlite database, so each query takes microseconds (~ 0 ms) to execute. However, that still doesn't mean I'd use a shitty solution if there is a better way of doing it.

EDIT: Forgot to mention. All queries are INSERT INTO queries, so the callback is basically a blank function that logs errors if there is an SQL error.

The question boils down to: What is the most efficient way of inserting data to a database when the database is being asked to insert stuff at ~ 15 single queries/second?

Re: Efficient Database Insertion

A transaction should send the same amount of individual queries to the database so I'm not sure if that really makes that much of a difference.

This depends on your table structure (mind posting it and a sample insert?) but I'd use a query like this.

PHP Code:


		
			
INSERT INTO my_log (message) VALUES ('message1'),('message2')

Which would only be one query.

Re: Efficient Database Insertion

I'd use this if adding full records.

PHP Code:


		
			
INSERT INTO table VALUES (x, y, z)

Would it be possible to just save the logs as an arraylist and on map change do the queries? It of course depends on what you are logging.

Re: Efficient Database Insertion

Here's the table structure and stuff (probably more information than asked):

Spoiler

And Mehis that doesn't work because some arguments need to be null on the table. It's somewhat convoluted - I think the spoiler should make some more sense.

Re: Efficient Database Insertion

I think the idea here is, is spamming multiple SQL_TQuery calls more inefficient (and not like by 10% but like 50% more) than calling one transaction? I can care less how hard the database thread has to work since that runs off-thread. Since this is SQLite, the query lasts like 0 ms, (and 1 frame is 15 ms), so that's not even a concern. It's pawn performance that might be the killer here since that shares the same thread with srcds.

I mean if there is a negligible difference between a transaction and multiple SQL_TQuery calls (all queries are on the same default priority level - FYI), then I'd obviously take SQL_TQuery spam since transactions have that risk where if one query fails for some reason, the entire transaction is dropped.

Re: Efficient Database Insertion

I hoped your table structure is a little bit simpler so you could temporarily store the data you'd insert into an finite array which will be used to construct the multi insert query when full or some time has passed.

I have no better idea at the moment so i tested how big the difference would be with 10 simple queries to the sqlite database. The code using a transaction looked like this.

PHP Code:


		
			
char query[1024];

Transaction txn = SQL_CreateTransaction();
for (int i=0; i <= 10; i++)
{
    Format(query, sizeof(query), "INSERT INTO `test` (`id`) VALUES (%d)", GetRandomInt(1, 999999));
    txn.AddQuery(query);
}

SQL_ExecuteTransaction(g_hDbHandle, txn, TXN_OnSuccess, TXN_OnFailure);

It took 0.014 ms on average on my machine for 10 queries, 100 took around 0.057 ms.

Then i used normal queries where the code looked like this.

PHP Code:


		
			
char query[1024];

for (int i=0; i <= 10; i++)
{
    Format(query, sizeof(query), "INSERT INTO `test` (`id`) VALUES (%d)", GetRandomInt(1, 999999));
    g_hDbHandle.Query(SQLT_ErrorCheckCallback, query);
}

This took 0.015 ms on average on my machine for 10 queries, 100 took around 0.075 ms.

Is that what you were interested in?

Re: Efficient Database Insertion

I have not looked too much into details in the topic, but if you are worrying about running too many queries (slowing down server perhaps), you could aswell save all the data and run the queries OnMapEnd.
At that time it will not disturb anyone anyways.

Re: Efficient Database Insertion

@tommie: Nope, that won't work (45 minutes / mvm game x 60 seconds / minute x 15 queries / second). That's asking for a disaster right there with RAM (the number is 40k).

@Impact: That benchmark was really useful. So it is 30% slower (yikes) to spam SQL_TQuery multiple times than to run a full transaction.

There is a 60 second timer loop that runs forever throughout the game in the exact same plugin. Is it wiser to haul all the queries to a transaction object, and then once that 60 second timer loop fires, execute the transaction and make a new transaction object? Would there be potentially any issues with that? (I'd have to catch the transaction being executed under OnPluginEnd or a commandlistener of "quit".)

Re: Efficient Database Insertion

The transaction execution alone took about 0.005 ms with 900 (number shouldn't matter) queries added to it. If you don't add all the queries at once (which took me 0.4 ms) but over the time of those 60 seconds it should be fine. I can send you the code i used to test this via pm if you want, you might get slightly different results.

Re: Efficient Database Insertion

Keep in mind that, in transactions, if a single query fails, all the queries are rolled back.

That is, assuming you're using a transactional database (i.e. InnoDB table types in MySQL).