SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help

Reply
 
Thread Tools Display Modes
Old 31/01/2016, 06:42 PM   #1
SkyFlare
Huge Clucker
 
SkyFlare's Avatar
 
Join Date: Apr 2015
Location: NZ
Posts: 221
Reputation: 43
Default Very long MYSQL Query string help

Done it, but is there anyway to get rid of this?

Code:
Pawn compiler 3.2.3664	 	 	Copyright (c) 1997-2006, ITB CompuPhase

Header size:           5700 bytes
Code size:           483132 bytes
Data size:          1421108 bytes
Stack/heap size:      16384 bytes; estimated max. usage: unknown, due to recursion
Total requirements: 1926324 bytes
__________________
Purchasable Scripting Services Available, Inbox me with your Requests.
My Project Progress Tracker
My Work
Link
Information
S-Store System V2 LinkIngame Store in Dialogs
SkyFlare is offline   Reply With Quote
Old 31/01/2016, 06:59 PM   #2
AmigaBlizzard
Huge Clucker
 
Join Date: Jul 2012
Posts: 319
Reputation: 60
Default Re: Very long MYSQL Query string help

What kind of query holds 16k characters?
Are you saving 1000 variables at once during OnPlayerDisconnect, using only 1 query?
AmigaBlizzard is offline   Reply With Quote
Old 31/01/2016, 07:18 PM   #3
SkyFlare
Huge Clucker
 
SkyFlare's Avatar
 
Join Date: Apr 2015
Location: NZ
Posts: 221
Reputation: 43
Default Re: Very long MYSQL Query string help

Sure, will show
Code:
mysql_format(mysql, query, sizeof(query),
	"UPDATE `accounts` SET `Money` = %d, `PosX` = %f, `PosY` = %f, `PosZ` = %f, `PosA` = %f, `Weap0` = %d, `AWeap0` = %d, `Weap1` = %d, `AWeap1` = %d, `Weap2` = %d, `AWeap2` = %d, `Weap3` = %d, `AWeap3` = %d, `Weap4` = %d, `AWeap4` = %d, `Weap5` = %d, `AWeap5` = %d, `Weap6` = %d, `AWeap6` = %d, `Weap7` = %d, `AWeap7` = %d, `Weap8` = %d, `AWeap8` = %d, `Weap9` = %d, `AWeap9` = %d, `Weap10` = %d, `AWeap10` = %d, `Weap11` = %d, `AWeap11` = %d, `Weap12` = %d, `AWeap12` = %d WHERE `ID` = %d",
    GetPlayerMoney(playerid), pos[0], pos[1], pos[2], pos[3], Wea0,AWea0,Wea1,AWea1,Wea2,AWea2,Wea3,AWea3,Wea4,AWea4,Wea5,AWea5,Wea6,AWea6,Wea7,AWea7,Wea8,AWea8,Wea9,AWea9,Wea10,AWea10,Wea11,AWea11,Wea12,AWea12,Player[playerid][ID]);
    mysql_tquery(mysql, query, "", "");
__________________
Purchasable Scripting Services Available, Inbox me with your Requests.
My Project Progress Tracker
My Work
Link
Information
S-Store System V2 LinkIngame Store in Dialogs
SkyFlare is offline   Reply With Quote
Old 31/01/2016, 07:33 PM   #4
AmigaBlizzard
Huge Clucker
 
Join Date: Jul 2012
Posts: 319
Reputation: 60
Default Re: Very long MYSQL Query string help

You really need to split up that query.
When a player's money changes, it doesn't have any effect on weapondata, so why update weapondata along with it?

Have small queries that only update what needs to be updated.
You can write many small functions to update data in the script and save it to mysql at the same time.
Like this one:
PHP Code:
// This function adds the given Money and Score values to the given player
Player_Reward(playeridMoneyScore)
{
    new 
Query[128];

    
// Add the given Money and Score to the player's account
    
pData[playerid][PlayerMoney] = pData[playerid][PlayerMoney] + Money;
    
pData[playerid][PlayerScore] = pData[playerid][PlayerScore] + Score;

    
// Update money and score for this player in the player's account in MySQL
    
mysql_format(SQL_dbQuerysizeof(Query), "UPDATE playerdata SET Money = '%i', Score = '%i' WHERE ID = '%i'"pData[playerid][PlayerMoney], pData[playerid][PlayerScore], pData[playerid][SQLID]);
    
mysql_tquery(SQL_dbQuery"""");

    return 
1;

Big queries like yours are slowing mysql down alot.
Mysql needs to analyze your big query, parse each column-name, search for it, update data and do some internal sorting stuff on the data to allow faster searching (called indexing).
With big queries, this takes alot more time compared to sending a few small ones.
You're not updating ALL fields every second or so anyways.

Also, you seem to have multiple columns for weapondata.
You need to normalize your tables (split them up) and save each weapon in a separate row.
General rule is if your column-names have numbers in them, you're doing it wrong.

Also, don't restrict your script to saving everything at once upon disconnecting.
If players keep playing for hours and make alot of progress, and your server goes down unexpectedly (server crash, power failure, DDOS attack, rare bug in the script, ...), all that data would be gone and your players will get mad because they lost hours of playtime.

Save whatever changes when it changes, simple as that.
Player earns money -> save the money.
Player earns scorepoints -> save the scorepoints.
Player buys a house -> update housedata (for that house only, don't update ALL houses).

Don't keep it all gathered in memory and just save when the player leaves, it will lead to dataloss and furious players.
AmigaBlizzard is offline   Reply With Quote
Old 31/01/2016, 07:45 PM   #5
SkyFlare
Huge Clucker
 
SkyFlare's Avatar
 
Join Date: Apr 2015
Location: NZ
Posts: 221
Reputation: 43
Default Re: Very long MYSQL Query string help

Quote:
Originally Posted by AmigaBlizzard View Post
You really need to split up that query.
When a player's money changes, it doesn't have any effect on weapondata, so why update weapondata along with it?

Have small queries that only update what needs to be updated.
You can write many small functions to update data in the script and save it to mysql at the same time.
Like this one:
PHP Code:
// This function adds the given Money and Score values to the given player
Player_Reward(playeridMoneyScore)
{
    new 
Query[128];

    
// Add the given Money and Score to the player's account
    
pData[playerid][PlayerMoney] = pData[playerid][PlayerMoney] + Money;
    
pData[playerid][PlayerScore] = pData[playerid][PlayerScore] + Score;

    
// Update money and score for this player in the player's account in MySQL
    
mysql_format(SQL_dbQuerysizeof(Query), "UPDATE playerdata SET Money = '%i', Score = '%i' WHERE ID = '%i'"pData[playerid][PlayerMoney], pData[playerid][PlayerScore], pData[playerid][SQLID]);
    
mysql_tquery(SQL_dbQuery"""");

    return 
1;

Big queries like yours are slowing mysql down alot.
Mysql needs to analyze your big query, parse each column-name, search for it, update data and do some internal sorting stuff on the data to allow faster searching (called indexing).
With big queries, this takes alot more time compared to sending a few small ones.
You're not updating ALL fields every second or so anyways.

Also, you seem to have multiple columns for weapondata.
You need to normalize your tables (split them up) and save each weapon in a separate row.
General rule is if your column-names have numbers in them, you're doing it wrong.

Also, don't restrict your script to saving everything at once upon disconnecting.
If players keep playing for hours and make alot of progress, and your server goes down unexpectedly (server crash, power failure, DDOS attack, rare bug in the script, ...), all that data would be gone and your players will get mad because they lost hours of playtime.

Save whatever changes when it changes, simple as that.
Player earns money -> save the money.
Player earns scorepoints -> save the scorepoints.
Player buys a house -> update housedata (for that house only, don't update ALL houses).

Don't keep it all gathered in memory and just save when the player leaves, it will lead to dataloss and furious players.
Should have known that tbh, thank you very much.... silly me XD
__________________
Purchasable Scripting Services Available, Inbox me with your Requests.
My Project Progress Tracker
My Work
Link
Information
S-Store System V2 LinkIngame Store in Dialogs
SkyFlare is offline   Reply With Quote
Old 31/01/2016, 08:49 PM   #6
Vince
Spam Machine
 
Vince's Avatar
 
Join Date: Sep 2007
Location: Belgium
Posts: 10,066
Reputation: 2660
Default Re: Very long MYSQL Query string help

Tutorials in my signature may help. There's one specifically for weapons.
__________________
Vince is offline   Reply With Quote
Old 01/02/2016, 02:50 AM   #7
SkyFlare
Huge Clucker
 
SkyFlare's Avatar
 
Join Date: Apr 2015
Location: NZ
Posts: 221
Reputation: 43
Default Re: Very long MYSQL Query string help

Quote:
Originally Posted by Vince View Post
Tutorials in my signature may help. There's one specifically for weapons.
Yes, since this thread i've already looked at that tutorial of yours, was about to use it awesome information Vince!
__________________
Purchasable Scripting Services Available, Inbox me with your Requests.
My Project Progress Tracker
My Work
Link
Information
S-Store System V2 LinkIngame Store in Dialogs
SkyFlare is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL Query String venomlivno8 Scripting Help 7 20/02/2014 12:12 PM
Too long MySQL Query Verbal Scripting Help 6 08/05/2012 06:22 PM
input line too long (after substitutions) - invalid string (possibly non-terminated string) MahmutBey Help Archive 6 10/03/2011 01:39 PM
[Mysql]Too Long Query (Idea Please) bkart Help Archive 21 31/12/2010 03:08 AM
[Help] MySQL String formatting for query. Ace_Menace Help Archive 3 01/08/2010 03:04 AM


All times are GMT. The time now is 08:23 PM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.