[Tutorial] The differences between MySQL queries - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Tutorials (https://sampforum.blast.hk/forumdisplay.php?fid=70)
+---- Thread: [Tutorial] The differences between MySQL queries (/showthread.php?tid=570505)

---

The differences between MySQL queries - Nicker - 10.04.2015

Introduction

Recently, a friend asked me what the difference between mysql_pquery and mysql_tquery is, so I thought I’d make a tutorial. The wiki actually explains the difference, but I thought I’d go into some more detail.

mysql_tquery

mysql_tquery will send a MySQL query that’ll be executed in another thread. You also have the option of specifying a callback and passing through any parameters to that callback, which will be called when the query has finished executing. So, what is a callback used for?

Callbacks

A callback is usually set to be called after a SELECT query, where data is selected from the MySQL database and needs something to be done with it. For example, if I did:

pawn Код:

mysql_tquery(connectionhandle, "SELECT * FROM `players`", "OnPlayerCount");

I’d then need to do something with the rows that we just selected from the database. So, when the query has finished executing (all the rows have been selected), we can call a callback in which we will process our data:

pawn Код:

forward OnPlayerCount();
public OnPlayerCount()
{
    new rows, fields; //These can be made global, of course
    
    cache_get_data(rows, fields, connectionhandle);
    //Fetching the amount of rows and fields found (in the result)
    
    printf("%i players found in the table.", rows);
    
    return 1;
}

You can also supply a specifier to pass certain parameters to the callback. For example, I can pass "playerid" through to the callback, so I can send the player a message with SendClientMessage:

pawn Код:

CMD:moneyscore(playerid, params[])
{
        new id;

        if(sscanf(params, "d", id)) return SendClientMessage(playerid, -1, "/moneyscore [reg ID]");

    format(query, sizeof(query), "SELECT * FROM `players` WHERE id = %d", id);
    mysql_tquery(connectionhandle, query, "OnPlayerDataSelected", "i", playerid);
    
    return 1;
}

forward OnPlayerDataSelected(playerid);
public OnPlayerDataSelected(playerid)
{
    new string[40], score, money;
    
    cache_get_row(0, 0, string); score = strval(string);
    cache_get_row(0, 1, string); money = strval(string); 
    //I doubt your money and score columns will ever be the first and second in the table, but it's just an example.

    format(string, sizeof(string), "Score: %d || Money: $%d", score, money);
    SendClientMessage(playerid, -1, string);
    
    return 1;
}

You may find you also need to call a callback for other queries, for example if you delete or update rows.


mysql_pquery

mysql_pquery is slightly different from mysql_tquery, but their usages and parameters are the same. mysql_pquery uses multi-threading, and therefore can be faster, depending on how many connections are used. The amount of connections in the connection pool can be set in mysql_connect. A connection pool is essentially a cache of connections to a database that are stored for further use to enhance performance speeds. So, mysql_pquery can execute queries on different connections, thus improving speed and performance.


Conclusion

"Which should I use?"

I suppose it doesn't entirely matter which of the two functions you use, but if you have multiple connections to your MySQL database and would prefer to see a performance enhancement, you might want to look into using mysql_pquery.


I hope I explained things well. If there's anything I explained incorrectly or badly, please let me know.

---

Re: The differences between MySQL queries - Abagail - 10.04.2015

You don't really mention how to use mysql_pquery or give any examples of it's usage.

---

Re: The differences between MySQL queries - Nicker - 10.04.2015

That's because its usage is the same as mysql_tquery, but I should specify that. Thanks.

---

Re: The differences between MySQL queries - Vince - 10.04.2015

I may be wrong but I believe that tqueries, while executed in another thread away from the main SA-MP server thread, are still queued up behind each other; pqueries on the other hand are executed concurrently (parallel) and immediately without regard for any queue. Race conditions may ensue.

I only use pqueries for a bunch of concurrent inserts which aren't required to be in any specific order. For example, I track all money changes for every player (much like a real bank account) so whenever I need to give money to a whole team I need to perform an insert for every player in that team. In such cases pqueries come in handy.

---

Re: The differences between MySQL queries - Nicker - 10.04.2015

I'm fairly sure you're correct about tqueries being linear. Well, there's a good example of pquery usage . Cheers.

---

Re: The differences between MySQL queries - JeaSon - 11.04.2015

pretty nice

---

Re: The differences between MySQL queries - Denis1 - 12.04.2015

Thanks for enlightening me, buddy. Everything seems so easy now.

---