mysql query (small doubt) - Printable Version

mysql query (small doubt) - 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)
+--- Thread: mysql query (small doubt) (/showthread.php?tid=558264)

mysql query (small doubt) - Bondage - 16.01.2015

Solved

Re: mysql query (small doubt) - Bondage - 16.01.2015

bump

Re: mysql query (small doubt) - xVIP3Rx - 16.01.2015

Huge query might take some time to be executed and (With unthreaded queries ) It might cause the server to freezes as much as I know.

You can see how much the execution took by logging the query using mysql_log

Also it's recommended to thread all of your queries to prevent lag/freeze

Re: mysql query (small doubt) - JeaSon - 16.01.2015

use strcat for less size

Re: mysql query (small doubt) - Bondage - 16.01.2015

Quote:

Originally Posted by xVIP3Rx

Huge query might take some time to be executed and (With unthreaded queries ) It might cause the server to freezes as much as I know.

You can see how much the execution took by logging the query using mysql_log

Also it's recommended to thread all of your queries to prevent lag/freeze

I've enabled the log and which shows an average of 5000 ms it takes (5 seconds). Thats kinda weird

And how do i thread the query, an example?

Re: mysql query (small doubt) - xVIP3Rx - 16.01.2015

Using mysql_tquery and mysql_pquery
There're alot of tutorials out there, just search.

Re: mysql query (small doubt) - Bondage - 16.01.2015

Quote:

Originally Posted by xVIP3Rx

Using mysql_tquery and mysql_pquery
There're alot of tutorials out there, just search.

I actually used mysql_tquery everywhere, i'm just bothered about the execution time now as it takes like 5 seconds and more. i guess i will need to reduce the query sizes, i may try that hope it will work.

Re: mysql query (small doubt) - xVIP3Rx - 16.01.2015

Try to make the MySQL Database on the same address as the server, it reduces the time taken to execute it, and just update the data you wanna update, once it changes, instead of making huge queries

Re: mysql query (small doubt) - Bondage - 16.01.2015

Quote:

Originally Posted by xVIP3Rx

Try to make the MySQL Database on the same address as the server, it reduces the time taken to execute it, and just update the data you wanna update, once it changes, instead of making huge queries

I have a big doubt in this section tho

pawn Код:

stock SavePlayerData(playerid)
{
    if(PlayerInfo[playerid][pLoggedIn] == 1)

        new year,month,day;
        getdate(year, month, day);
        new Float:x,Float:y,Float:z, interior;
        GetPlayerPos(playerid,x,y,z);   interior = GetPlayerInterior(playerid);
        new query[1600], pname[24];
        GetPlayerName(playerid, pname, 24);

        mysql_format(mysql, query, sizeof(query), "UPDATE players SET TP=%d, Function1=%d, Function2=%d, Function3=%d, Function4=%d, Function5=%d, Function6=%d, Bank=%d, weed=%d, Condoms=%d, inalcatraz=%d, hours=%d, minutes=%d, seconds=%d WHERE user='%e'",
        PlayerInfo[playerid][TP],PlayerInfo[playerid][Function1],PlayerInfo[playerid][Function2],PlayerInfo[playerid][Function3],PlayerInfo[playerid][Function4],PlayerInfo[playerid][Function5],PlayerInfo[playerid][Function6],PlayerInfo[playerid][bank],PlayerInfo[playerid][weed],PlayerInfo[playerid][condoms],PlayerInfo[playerid][inalcatraz],PlayerInfo[playerid][pHour],PlayerInfo[playerid][pMin],PlayerInfo[playerid][pSec],pname);
        mysql_tquery(mysql, query, "", "");
        
        mysql_format(mysql, query, sizeof(query), "UPDATE `players` SET `score`=%d, `money`=%d, `kills`=%d, `deaths`=%d, `registered`=%d, `loggedin`=%d, `level`=%d, `cookies`=%d, `cakes`=%d, `biscuits`=%d, `icecreams`=%d, `GT`=%d, `Donator`=%d WHERE `user`='%e'",
        GetPlayerScore(playerid),GetPlayerMoney(playerid),PlayerInfo[playerid][Kills],PlayerInfo[playerid][Deaths],PlayerInfo[playerid][Registered],PlayerInfo[playerid][LoggedIn],PlayerInfo[playerid][Level],PlayerInfo[playerid][Cookies],PlayerInfo[playerid][Cakes],PlayerInfo[playerid][Biscuits],PlayerInfo[playerid][IceCreams],PlayerInfo[playerid][GT],PlayerInfo[playerid][Donator],pname);
        mysql_tquery(mysql, query, "", "");
        
        mysql_format(mysql, query, sizeof(query), "UPDATE players SET Race=%d, ATMCard=%d, AdminKills=%d, CashToBank=%d, HidenPickUp=%d, VOwner=%d, VOwned=%d,VOwner2=%d ,VOwned2=%d ,VOwner3=%d ,VOwned3=%d ,VOwner4=%d ,VOwned4=%d ,VOwner5=%d ,VOwned5=%d ,VOwner6=%d ,VOwned6=%d WHERE user='%e'",
        PlayerInfo[playerid][RaceWon],PlayerInfo[playerid][ATMCard],PlayerInfo[playerid][AdminKills],PlayerInfo[playerid][CashToBank],PlayerInfo[playerid][HidenPickUp],PlayerInfo[playerid][vowner],PlayerInfo[playerid][vowned],PlayerInfo[playerid][vowner2],PlayerInfo[playerid][vowned2],
        PlayerInfo[playerid][vowner3],PlayerInfo[playerid][vowned3],PlayerInfo[playerid][vowner4],PlayerInfo[playerid][vowned4],PlayerInfo[playerid][vowner5],PlayerInfo[playerid][vowned5],PlayerInfo[playerid][vowner6],PlayerInfo[playerid][vowned6],pname);
        mysql_tquery(mysql, query, "", "");
        
        mysql_format(mysql, query, sizeof(query), "UPDATE players SET Skin=%d, PositionX=%f, PositionY=%f, PositionZ=%f, interior1=%d, TotalCmdsUsed=%d, Complaints=%d, Banned=%d, TotalLogins=%d, TotalKicks=%d, TotalBans=%d, TotalWarnings=%d, ReactionTestWon=%d WHERE user='%e'",
        PlayerInfo[playerid][pSkin],x,y,z,interior,PlayerInfo[playerid][Totcmdsused],PlayerInfo[playerid][pReports],PlayerInfo[playerid][pBanned],PlayerInfo[playerid][Totallogins],PlayerInfo[playerid][Totalkicks],PlayerInfo[playerid][Totalbans],PlayerInfo[playerid][Totalwarnings],PlayerInfo[playerid][ReactionTestWon],pname);
        mysql_tquery(mysql, query, "", "");
        
    }
    return 1;
}

This uses 1600 query size and i set it as a timer to save the data as well as a player leaves but it takes like as i said 5-8 seconds to execute. Could you see anything in this to reduce so that i wont take that long?

Re: mysql query (small doubt) - xVIP3Rx - 16.01.2015

Try to put em all in one query, here you go try that one

pawn Код:

stock SavePlayerData(playerid)
{
    if(PlayerInfo[playerid][pLoggedIn] == 1)
    {
        new year,month,day;             getdate(year, month, day);
        new Float:x,Float:y,Float:z;    GetPlayerPos(playerid,x,y,z);
        new pname[MAX_PLAYER_NAME];     GetPlayerName(playerid, pname, MAX_PLAYER_NAME);
        new interior = GetPlayerInterior(playerid);
        
        new tempstring[512], query[1600];
        mysql_format(mysql, tempstring, sizeof(tempstring), "UPDATE `players` SET TP=%d, Function1=%d, Function2=%d, Function3=%d, Function4=%d, Function5=%d, Function6=%d, Bank=%d, weed=%d, Condoms=%d, inalcatraz=%d, hours=%d, minutes=%d, seconds=%d, ",
        PlayerInfo[playerid][TP],PlayerInfo[playerid][Function1],PlayerInfo[playerid][Function2],PlayerInfo[playerid][Function3],PlayerInfo[playerid][Function4],PlayerInfo[playerid][Function5],PlayerInfo[playerid][Function6],PlayerInfo[playerid][bank],PlayerInfo[playerid][weed],PlayerInfo[playerid][condoms],PlayerInfo[playerid][inalcatraz],PlayerInfo[playerid][pHour],PlayerInfo[playerid][pMin],PlayerInfo[playerid][pSec]);

        strcat(query, tempstring);

        mysql_format(mysql, tempstring, sizeof(tempstring), "`score`=%d, `money`=%d, `kills`=%d, `deaths`=%d, `registered`=%d, `loggedin`=%d, `level`=%d, `cookies`=%d, `cakes`=%d, `biscuits`=%d, `icecreams`=%d, `GT`=%d, `Donator`=%d, ",
        GetPlayerScore(playerid),GetPlayerMoney(playerid),PlayerInfo[playerid][Kills],PlayerInfo[playerid][Deaths],PlayerInfo[playerid][Registered],PlayerInfo[playerid][LoggedIn],PlayerInfo[playerid][Level],PlayerInfo[playerid][Cookies],PlayerInfo[playerid][Cakes],PlayerInfo[playerid][Biscuits],PlayerInfo[playerid][IceCreams],PlayerInfo[playerid][GT],PlayerInfo[playerid][Donator]);

        strcat(query, tempstring);

        mysql_format(mysql, tempstring, sizeof(tempstring), "`Race`=%d, ATMCard=%d, AdminKills=%d, CashToBank=%d, HidenPickUp=%d, VOwner=%d, VOwned=%d,VOwner2=%d ,VOwned2=%d ,VOwner3=%d ,VOwned3=%d ,VOwner4=%d ,VOwned4=%d ,VOwner5=%d ,VOwned5=%d ,VOwner6=%d ,VOwned6=%d, ",
        PlayerInfo[playerid][RaceWon],PlayerInfo[playerid][ATMCard],PlayerInfo[playerid][AdminKills],PlayerInfo[playerid][CashToBank],PlayerInfo[playerid][HidenPickUp],PlayerInfo[playerid][vowner],PlayerInfo[playerid][vowned],PlayerInfo[playerid][vowner2],PlayerInfo[playerid][vowned2],
        PlayerInfo[playerid][vowner3],PlayerInfo[playerid][vowned3],PlayerInfo[playerid][vowner4],PlayerInfo[playerid][vowned4],PlayerInfo[playerid][vowner5],PlayerInfo[playerid][vowned5],PlayerInfo[playerid][vowner6],PlayerInfo[playerid][vowned6]);

        strcat(query, tempstring);

        mysql_format(mysql, tempstring, sizeof(tempstring), "Skin=%d, PositionX=%f, PositionY=%f, PositionZ=%f, interior1=%d, TotalCmdsUsed=%d, Complaints=%d, Banned=%d, TotalLogins=%d, TotalKicks=%d, TotalBans=%d, TotalWarnings=%d, ReactionTestWon=%d WHERE user='%e'",
        PlayerInfo[playerid][pSkin],x,y,z,interior,PlayerInfo[playerid][Totcmdsused],PlayerInfo[playerid][pReports],PlayerInfo[playerid][pBanned],PlayerInfo[playerid][Totallogins],PlayerInfo[playerid][Totalkicks],PlayerInfo[playerid][Totalbans],PlayerInfo[playerid][Totalwarnings],PlayerInfo[playerid][ReactionTestWon],pname);

        strcat(query, tempstring);

        mysql_tquery(mysql, query, "", "");
    }
    return 1;
}