11.12.2016, 15:27
I have a lot of player data to save and I thought which would be better : to save everything with one big query (~2000 symbols) or to split that query into 2 or 3 querys? Would it even make a difference?
Is it good to save a lot of data with one query?
11.12.2016, 15:51
Honestly, not really. MySQL is kinda built for that kinda thing.
Unless there's some personal organisation factor to splitting up the queries like inserting into different tables, then I wouldn't really bother... just have 1 query.
Unless there's some personal organisation factor to splitting up the queries like inserting into different tables, then I wouldn't really bother... just have 1 query.
11.12.2016, 23:31
I don't think it's a good idea to always use an immense query when it's not needed.
If your player completes a job and gets paid, the only thing that changes is his money.
Why would you execute a query that's 2000 characters long and updates 100 fields in your database, when all you wanted to do is change 1 value?
When you have a book (100 pages long), written with a pencil so you can wipe out and correct it easily for simplicity's sake, and you see a typo somewhere, would you just correct that typo and leave the rest alone?
Or would you re-write the entire book to fix one typo?
Just fixing the typo is done alot faster and you'll get the same result, so why waste time to rewrite the entire book?
The same happens with MySQL.
Since a query is pure text, it needs to be interpreted.
Interpreted languages are quite slow in comparison to compiled languages.
Interpreting a string of 2000 characters takes 40 times longer than a query that's only 50 characters long.
Executing a query with a length of 2000 characters will take alot more time to finish compared to a query that's only 50 characters long, which ultimately gets the same job done (change the money of your player).
Add to that the time MySQL needs to search your database for the appropriate fields in order to update them all.
Searching 100 fields and updating them in a database will take about 100 times longer than searching for the money alone.
And don't forget the harddrive speed and overhead.
Updating a field means overwriting the value, which needs to be saved to the harddisk too.
Creating extra overhead on your harddrive (to update fields that don't need to be updated) will cause additional wear and tear on your harddrive and it will fail faster.
To begin with, creating the query using "format" also takes time.
Merging 100 variables into a super-long string of 2000 characters takes alot more time than generating a query that's only 50 chars long with only a few variables.
I've seen scripts that update every damn field in the database when they just want to update the money.
How often does the sex of a player change? Or his skin? Or password? Or email?
Almost never.
Updating those fields in your extremely long query just consumes processor power and does absolutely nothing in the end because the result in the database stays exactly the same.
Also, a super-long query with an error in it may take some time to find the problem.
When all your fields get updated with one query and there is a problem with your query (you get errors in the log), nothing will get updated in your database and you'll need alot of time to find the problem.
MySQL is a database, you shouldn't treat it as if it was a textfile (which usually requires you to overwrite the entire file to update one value).
It's built so you can change value per value. Treating it as if it was a textfile (update everything in one go) is overkill and you should consider going back to using ini-files because they're faster than using a database for dumping data.
So make things easier, create many small functions that update whatever needs to be updated.
A small function like this does the job perfectly.
It's small, you'll spot problems alot faster and gets executed alot faster by MySQL than a super long query that does it all in one go.
Additionally, many scripts use a save-function to update everything every 5 minutes or so using one huge query.
I just update the fields that need to be updated when they change.
A player earns money? Update it immediately, and only update the money, nothing else.
A player gets experience and may gain a level? Update experience and level because they're connected, and update them immediately.
Why wait until your 5-minute save-function kicks in?
The server may have crashed in that time (or hacked) and your player might lose alot of progress.
He may have bought a house, upgrade the house, buy a house-car, finish some missions, gain a few kills, ...
And he may have done that just before a crash.
He'll be furious if he lost it all because it wasn't saved yet.
5 minutes is nothing, I know, but some scripts only save when the player disconnects, which means nothing was saved for hours if the player plays for hours in a row.
I wouldn't come back to your server if nothing gets saved while playing.
One crash is enough to lose hours of progress.
Save when something changes and do it immediately.
Also, that will spread the load on your server instead of executing several huge queries every few mins.
If your player completes a job and gets paid, the only thing that changes is his money.
Why would you execute a query that's 2000 characters long and updates 100 fields in your database, when all you wanted to do is change 1 value?
When you have a book (100 pages long), written with a pencil so you can wipe out and correct it easily for simplicity's sake, and you see a typo somewhere, would you just correct that typo and leave the rest alone?
Or would you re-write the entire book to fix one typo?
Just fixing the typo is done alot faster and you'll get the same result, so why waste time to rewrite the entire book?
The same happens with MySQL.
Since a query is pure text, it needs to be interpreted.
Interpreted languages are quite slow in comparison to compiled languages.
Interpreting a string of 2000 characters takes 40 times longer than a query that's only 50 characters long.
Executing a query with a length of 2000 characters will take alot more time to finish compared to a query that's only 50 characters long, which ultimately gets the same job done (change the money of your player).
Add to that the time MySQL needs to search your database for the appropriate fields in order to update them all.
Searching 100 fields and updating them in a database will take about 100 times longer than searching for the money alone.
And don't forget the harddrive speed and overhead.
Updating a field means overwriting the value, which needs to be saved to the harddisk too.
Creating extra overhead on your harddrive (to update fields that don't need to be updated) will cause additional wear and tear on your harddrive and it will fail faster.
To begin with, creating the query using "format" also takes time.
Merging 100 variables into a super-long string of 2000 characters takes alot more time than generating a query that's only 50 chars long with only a few variables.
I've seen scripts that update every damn field in the database when they just want to update the money.
How often does the sex of a player change? Or his skin? Or password? Or email?
Almost never.
Updating those fields in your extremely long query just consumes processor power and does absolutely nothing in the end because the result in the database stays exactly the same.
Also, a super-long query with an error in it may take some time to find the problem.
When all your fields get updated with one query and there is a problem with your query (you get errors in the log), nothing will get updated in your database and you'll need alot of time to find the problem.
MySQL is a database, you shouldn't treat it as if it was a textfile (which usually requires you to overwrite the entire file to update one value).
It's built so you can change value per value. Treating it as if it was a textfile (update everything in one go) is overkill and you should consider going back to using ini-files because they're faster than using a database for dumping data.
So make things easier, create many small functions that update whatever needs to be updated.
PHP код:
// This function is used to give (or take) money to/from the player
Player_GiveMoney(playerid, amount)
{
// Setup local variables
new query[75];
// Add the given Money to the player's account
APlayerData[playerid][Money] = APlayerData[playerid][Money] + amount;
// Also update the client immediately instead of waiting for the GlobalTimer1000 to update it
ResetPlayerMoney(playerid);
GivePlayerMoney(playerid, APlayerData[playerid][Money]);
// Update money for this player in the player's account in MySQL
mysql_format(SQL_db, query, sizeof(query), "UPDATE playerdata SET Money = '%i' WHERE UserID = '%i'", APlayerData[playerid][Money], APlayerData[playerid][UserID]);
mysql_tquery(SQL_db, query, "", "");
return 1;
}
It's small, you'll spot problems alot faster and gets executed alot faster by MySQL than a super long query that does it all in one go.
Additionally, many scripts use a save-function to update everything every 5 minutes or so using one huge query.
I just update the fields that need to be updated when they change.
A player earns money? Update it immediately, and only update the money, nothing else.
A player gets experience and may gain a level? Update experience and level because they're connected, and update them immediately.
Why wait until your 5-minute save-function kicks in?
The server may have crashed in that time (or hacked) and your player might lose alot of progress.
He may have bought a house, upgrade the house, buy a house-car, finish some missions, gain a few kills, ...
And he may have done that just before a crash.
He'll be furious if he lost it all because it wasn't saved yet.
5 minutes is nothing, I know, but some scripts only save when the player disconnects, which means nothing was saved for hours if the player plays for hours in a row.
I wouldn't come back to your server if nothing gets saved while playing.
One crash is enough to lose hours of progress.
Save when something changes and do it immediately.
Also, that will spread the load on your server instead of executing several huge queries every few mins.
12.12.2016, 07:34
And what purpose would sending a 2k char query to the database be?
Update? Only update those necessary.
Insert? Make use of default values.
Create Table? It's kinda okay but doing it manually is way better.
There's almost no excuse in sending that huge chunk of text to database and thats not the proper usage of it.
Update? Only update those necessary.
Insert? Make use of default values.
Create Table? It's kinda okay but doing it manually is way better.
There's almost no excuse in sending that huge chunk of text to database and thats not the proper usage of it.
« Next Oldest | Next Newest »
Users browsing this thread: 1 Guest(s)