[jbankss]

Hi. for some reason SQLite takes a long time to perform queries. it's random, ranges from 20-2000 ms.
The thing is everything else on the server is responsive and fast, its as if SQL is slacking behind, even though it gets the job done.

Any ideas?


Edit: apparently its just one, benchmarked alot of them and they were fine, besides the one that backup all the playerstats.

Here is the query format:

Код:

format(Query,sizeof(Query),"UPDATE `USERS` SET ALEVEL = %i, KILLS = %i, DEATHS = %i, HSPREE = %i, TIMEP = '%s', MAINSET = '%s', PTOKEN = %i, PMONEY = %i, PlayerLevel = %i, PlayerLevelExp = %i, TotalExp = %i WHERE `UserID` = %i COLLATE NOCASE",
	PlayerInfo[playerid][aLevel], PlayerInfo[playerid][Kills], PlayerInfo[playerid][Deaths], PlayerInfo[playerid][hSpree], DB_Escape(timeString), DB_Escape(cfgString), PlayerInfo[playerid][pTokens], PlayerInfo[playerid][pMoney], PlayerExp[playerid][0], PlayerExp[playerid][1], PlayerExp[playerid][2], PlayerInfo[playerid][UserID]);

[MarkNelson]

Hmm 20-2000? Ranges?

[jbankss]

Quote:

Originally Posted by MarkNelson Hmm 20-2000? Ranges?

20-2000 milliseconds. How big the range of time is that it takes to perform the query.

[Jefff]

Remove COLLATE NOCASE, UserID should be primary auto_increment

[jbankss]

Quote:

Originally Posted by Jefff Remove COLLATE NOCASE, UserID should be primary auto_increment

Ah, fixed, however it didnt decrease the time it takes for the query to save, though :/

[Jefff]

You need show us full code for that save

[jbankss]

Here it is:

PHP код:

stock SavePlayerData(playerid)
{
    if(pLogged[playerid] == false) return 0;
    new tick = tickcount();
 
    format(Query,sizeof(Query),"UPDATE `USERS` SET ALEVEL = %i, KILLS = %i, DEATHS = %i, HSPREE = %i, TIMEP = '%s', MAINSET = '%s', PTOKEN = %i, PMONEY = %i, PlayerLevel = %i, PlayerLevelExp = %i, TotalExp = %i WHERE `UserID` = %i",
    PlayerInfo[playerid][aLevel], PlayerInfo[playerid][Kills], PlayerInfo[playerid][Deaths], PlayerInfo[playerid][hSpree], DB_Escape(timeString), DB_Escape(cfgString), PlayerInfo[playerid][pTokens], PlayerInfo[playerid][pMoney], PlayerExp[playerid][0], PlayerExp[playerid][1], PlayerExp[playerid][2], PlayerInfo[playerid][UserID]);
    db_query(Database, Query);
    
    printf("SavePlayerData took: %i ms", tickcount() - tick);
    return 1;
} 


[Jefff]

Its ok, problem can be in DB_Escape only, you dont need DB_Escape() anymore read info
https://sampwiki.blast.hk/wiki/Escaping_Strings_SQLite

%q in format

[Lordzy]

Data such as 'ALEVEL' (I'm assuming administrator level) doesn't required to be updated under this function. They're meant to vary rarely and requires update on such occasion only. I'd add the columns that are frequently updated only under this function. I'm not implying that this could bring a lot of time difference but it does count.

[jbankss]

Quote:

Originally Posted by Jefff Its ok, problem can be in DB_Escape only, you dont need DB_Escape() anymore read info https://sampwiki.blast.hk/wiki/Escaping_Strings_SQLite %q in format

Ah I see, updated the code, the time it takes to perform is still very random. I just tried hosting it on my local computer and it ranged between 6-78 ms whereas my VPS it went between 6-1200 ms.

Is there anything else I could try?

Quote:

Originally Posted by Lordzy Data such as 'ALEVEL' (I'm assuming administrator level) doesn't required to be updated under this function. They're meant to vary rarely and requires update on such occasion only. I'd add the columns that are frequently updated only under this function. I'm not implying that this could bring a lot of time difference but it does count.

I understand, I might switch it up but the amount of time this query takes I'm assuming something else might be whats causing it :7