Raised This Month: $12 Target: $400
 3% 

[Database - Server Connection] - Optimization of plugin


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
Fabregas21
Junior Member
Join Date: May 2012
Old 08-27-2018 , 13:59   [Database - Server Connection] - Optimization of plugin
Reply With Quote #1

Hello,
I need help with optimization a part of my plugin using concerning rank system.
After playing several maps, I'm getting this error: "User xxx already has more than 'max_user_connections' active connections". Of course I know what it is and the limit of this variable is set to 50. The problem is that hosting does not allow to increase the value of this parameter and instructed me to optimize the plugin.
How can this plugin be improved so that connections to the database are closed or to maintain the continuity of the connection? What should be changed or removed in the plugin?


PHP Code:
public load_data(id)
{
    new 
szError[512], ErrorCode
    
new szName[32]
    
get_user_name(idszNamecharsmaxszName ))
    
    new 
Handle:SqlConnection SQL_Connect(g_SqlTuple,ErrorCode,szError,511)
    
SQL_QuoteString(SqlConnectionszName32szName);
    
    new 
Handle:Query SQL_PrepareQuery(SqlConnection"SELECT `id` FROM `stats` WHERE `name`='%s';"szName)
    
    if(!
SQL_Execute(Query)) {
        
SQL_QueryError(Query,szError,511);
        
log_amx(szError);
        return ;
    }
    if(
SQL_NumResults(Query) > ) {
        
next_load_data(id""0);
    } 
    else {
        
register_player(id,"");
    }
    
    
SQL_FreeHandle(Query);
    
SQL_FreeHandle(SqlConnection);
}
    
public 
next_load_data(id,data[],len)
{
    new 
szName[33];
    
get_user_name(idszNamecharsmaxszName ));
    
    new 
szError[512], ErrorCode;
    new 
Handle:SqlConnection SQL_Connect(g_SqlTuple,ErrorCode,szError,511)
    
SQL_QuoteString(SqlConnectionszName32szName);
    
    if(
SqlConnection == Empty_Handle)
    {
        
log_amx(szError);
        return ;
    }
    
    new 
Handle:Query SQL_PrepareQuery(SqlConnection"SELECT * FROM `stats` WHERE `name`='%s';"szName)
    
    if(!
SQL_Execute(Query))
    {
        
SQL_QueryError(Query,szError,511)
        
log_amx(szError)
    }
    
    if( 
SQL_NumResults(Query) > 0)
    {
        
UserData[id][gPoints]= SQL_ReadResult(Query2);
        
UserData[id][gGoals]= SQL_ReadResult(Query3);
        
UserData[id][gAssists]= SQL_ReadResult(Query4);
        
UserData[id][gSteals]= SQL_ReadResult(Query5);
        
UserData[id][gStealsRv]= SQL_ReadResult(Query6);
        
UserData[id][gDisarm]= SQL_ReadResult(Query7);
        
UserData[id][gDisarmRv]= SQL_ReadResult(Query8);
        
UserData[id][gBKills]= SQL_ReadResult(Query9);
        
UserData[id][gBKillsRv]= SQL_ReadResult(Query10);    
        
UserData[id][gOffside]= SQL_ReadResult(Query11);
        
UserData[id][gFoul]= SQL_ReadResult(Query12);
        
UserData[id][gWarns]= SQL_ReadResult(Query13);    
    }
    
    
SQL_FreeHandle(Query);
    
SQL_FreeHandle(SqlConnection);
}    

public 
register_player(iddata[])
{
    new 
szName[33];
    
get_user_name(idszNamecharsmaxszName ));
    
    new 
szError[512], ErrorCode;
    new 
Handle:SqlConnection SQL_Connect(g_SqlTupleErrorCodeszError,511)
    
SQL_QuoteString(SqlConnectionszName32szName);
    
    if(
SqlConnection == Empty_Handle)
    {
        
log_amx(szError);
        return ;
    }
    
    new 
Handle:Query SQL_PrepareQuery(SqlConnection"INSERT INTO `stats` (`name`, `points`, `goals`, `assists`, `steals`, `stealsrv`, `disarm`, `disarmrv`, `bkills`, `bkillsrv`, `offside`, `foul`, `warns`) VALUES ('%s', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');"szName);

    if(!
SQL_Execute(Query))
    {
        
SQL_QueryError(Query,szError,511)
        
log_amx(szError)
    }
    
    
SQL_FreeHandle(Query);
    
SQL_FreeHandle(SqlConnection);
    
    
UserData[id][gPoints]= 0;
    
UserData[id][gGoals]= 0;
    
UserData[id][gSteals]= 0;
    
UserData[id][gStealsRv]= 0;
    
UserData[id][gAssists]= 0;
    
UserData[id][gDisarm]= 0;
    
UserData[id][gDisarmRv]= 0;
    
UserData[id][gBKills]= 0;
    
UserData[id][gBKillsRv]= 0;
    
UserData[id][gOffside]= 0;
    
UserData[id][gFoul]= 0;
    
UserData[id][gWarns]= 0;
}
    
public 
save_usr(id)
{
    new 
szName[33];
    
get_user_name(idszNamecharsmaxszName ));
    
    new 
szError[512], ErrorCode;
    new 
Handle:szUpdate;
    new 
Handle:SqlConnection SQL_Connect(g_SqlTupleErrorCodeszError511)
    
    
SQL_QuoteString(SqlConnectionszName32szName)
    
    if(
SqlConnection == Empty_Handle)
    {
        
log_amx(szError);
        return ;
    }
    
    
szUpdate SQL_PrepareQuery(SqlConnection"UPDATE `stats` SET `points`='%d', `goals`='%d', `assists`='%d', `steals`='%d', `stealsrv`='%d', `disarm`='%d', `disarmrv`='%d', `bkills`='%d', `bkillsrv`='%d', `offside`='%d', `foul`='%d', `warns`='%d' WHERE `stats`.`name`='%s';"
    
UserData[id][gPoints], UserData[id][gGoals], UserData[id][gAssists], UserData[id][gSteals], UserData[id][gStealsRv], UserData[id][gDisarm], UserData[id][gDisarmRv], UserData[id][gBKills], UserData[id][gBKillsRv], UserData[id][gOffside], UserData[id][gFoul], UserData[id][gWarns], szName)
    
    if(!
SQL_Execute(szUpdate)) 
    {
        
SQL_QueryError(szUpdateszErrorcharsmaxszError ))
        
set_fail_stateszError )
    }
}

public 
top_players(id){
    
    new 
szError[512], ErrorCode;    
    new 
num 0;
    new 
name[10][32], points[10][11], goals[10][11], assists[10][11], steals[10][11], stealsrv[10][11], disarm[10][11], disarmrv[10][11], bkills[10][11], bkillsrv[10][11], offside[10][11], foul[10][11];
    
    new 
Handle:SqlConnection SQL_Connect(g_SqlTupleErrorCodeszError511)
    new 
Handle:Query SQL_PrepareQuery(SqlConnection"SELECT * FROM `stats` ORDER BY `points` DESC, `goals` DESC")
    
    
SQL_Execute(Query)
 
    while(
SQL_MoreResults(Query) && num <= 9){
        
        
SQL_ReadResult(Query1name[num], 31); 
        
SQL_ReadResult(Query2points[num], 10);
        
SQL_ReadResult(Query3goals[num], 10);
        
SQL_ReadResult(Query4assists[num], 10); 
        
SQL_ReadResult(Query5steals[num], 10);
        
SQL_ReadResult(Query6stealsrv[num], 10);
        
SQL_ReadResult(Query7disarm[num], 10); 
        
SQL_ReadResult(Query8disarmrv[num], 10);
        
SQL_ReadResult(Query9bkills[num], 10);
        
SQL_ReadResult(Query10bkillsrv[num], 10);
        
SQL_ReadResult(Query11offside[num], 10);
        
SQL_ReadResult(Query12foul[num], 10); 
        
        
num++
        
SQL_NextRow(Query)
    }
    
    new 
motd[2001], iLen;
    
    
iLen format(motdsizeof motd 1"<body bgcolor=#000000><font color=#98f5ff><pre>"); 
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen"<center><h2>---- SoccerJam Top 10 ----</h2></center>^n^n");
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen"<b><U>%2s %-22.22s %8s %6s %6s %6s %6s %6s %6s %6s %6s %6s %6s</U></b>^n""#""Nick""Points""Goals""Assists""Steals""StealsRv""Disarm""DisarmRv""BKills""BKillsRv""Offsides""Fouls");
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"1  %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n"name[0], str_to_num(points[0]), str_to_num(goals[0]), str_to_num(assists[0]), str_to_num(steals[0]), str_to_num(stealsrv[0]), str_to_num(disarm[0]), str_to_num(disarmrv[0]), str_to_num(bkills[0]), str_to_num(bkillsrv[0]), str_to_num(offside[0]), str_to_num(foul[0]))
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"2  %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n"name[1], str_to_num(points[1]), str_to_num(goals[1]), str_to_num(assists[1]), str_to_num(steals[1]), str_to_num(stealsrv[1]), str_to_num(disarm[1]), str_to_num(disarmrv[1]), str_to_num(bkills[1]), str_to_num(bkillsrv[1]), str_to_num(offside[1]), str_to_num(foul[1]))
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"3  %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n"name[2], str_to_num(points[2]), str_to_num(goals[2]), str_to_num(assists[2]), str_to_num(steals[2]), str_to_num(stealsrv[2]), str_to_num(disarm[2]), str_to_num(disarmrv[2]), str_to_num(bkills[2]), str_to_num(bkillsrv[2]), str_to_num(offside[2]), str_to_num(foul[2]))
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"4  %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n"name[3], str_to_num(points[3]), str_to_num(goals[3]), str_to_num(assists[3]), str_to_num(steals[3]), str_to_num(stealsrv[3]), str_to_num(disarm[3]), str_to_num(disarmrv[3]), str_to_num(bkills[3]), str_to_num(bkillsrv[3]), str_to_num(offside[3]), str_to_num(foul[3]))
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"5  %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n"name[4], str_to_num(points[4]), str_to_num(goals[4]), str_to_num(assists[4]), str_to_num(steals[4]), str_to_num(stealsrv[4]), str_to_num(disarm[4]), str_to_num(disarmrv[4]), str_to_num(bkills[4]), str_to_num(bkillsrv[4]), str_to_num(offside[4]), str_to_num(foul[4]))
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"6  %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n"name[5], str_to_num(points[5]), str_to_num(goals[5]), str_to_num(assists[5]), str_to_num(steals[5]), str_to_num(stealsrv[5]), str_to_num(disarm[5]), str_to_num(disarmrv[5]), str_to_num(bkills[5]), str_to_num(bkillsrv[5]), str_to_num(offside[5]), str_to_num(foul[5]))
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"7  %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n"name[6], str_to_num(points[6]), str_to_num(goals[6]), str_to_num(assists[6]), str_to_num(steals[6]), str_to_num(stealsrv[6]), str_to_num(disarm[6]), str_to_num(disarmrv[6]), str_to_num(bkills[6]), str_to_num(bkillsrv[6]), str_to_num(offside[6]), str_to_num(foul[6]))
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"8  %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n"name[7], str_to_num(points[7]), str_to_num(goals[7]), str_to_num(assists[7]), str_to_num(steals[7]), str_to_num(stealsrv[7]), str_to_num(disarm[7]), str_to_num(disarmrv[7]), str_to_num(bkills[7]), str_to_num(bkillsrv[7]), str_to_num(offside[7]), str_to_num(foul[7]))
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"9  %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n"name[8], str_to_num(points[8]), str_to_num(goals[8]), str_to_num(assists[8]), str_to_num(steals[8]), str_to_num(stealsrv[8]), str_to_num(disarm[8]), str_to_num(disarmrv[8]), str_to_num(bkills[8]), str_to_num(bkillsrv[8]), str_to_num(offside[8]), str_to_num(foul[8]))
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"10 %-22.22s %6i %7i %6i %7i %5i %8i %7i %7i %6i %6i %6i^n"name[9], str_to_num(points[9]), str_to_num(goals[9]), str_to_num(assists[9]), str_to_num(steals[9]), str_to_num(stealsrv[9]), str_to_num(disarm[9]), str_to_num(disarmrv[9]), str_to_num(bkills[9]), str_to_num(bkillsrv[9]), str_to_num(offside[9]), str_to_num(foul[9]))
    
    
show_motd(idmotd"Top 10");
}

public 
rank_players(id){
    
    new 
szError[512], ErrorCode;    
    new 
num 1;
    new 
name[32], points[11], notes;
    new 
szName[32];
    
get_user_name(idszName31);
    
    new 
Handle:SqlConnection SQL_Connect(g_SqlTupleErrorCodeszError511);
    new 
Handle:Query SQL_PrepareQuery(SqlConnection"SELECT * FROM `stats` ORDER BY `points` DESC, `goals` DESC");
    
    
SQL_Execute(Query)
    
notes SQL_NumResults(Query)
    
    while(
SQL_MoreResults(Query))
    {
        
SQL_ReadResult(Query1name31)
    
        if(
equal(nameszName))
        {
            
SQL_ReadResult(Query2points10)
            
            break
        }else{
            
num++
            
            
SQL_NextRow(Query)
        }
        
    }
    
    
PrintChatColor(id_"%L"LANG_PLAYER"SJ_RANK"numnotesstr_to_num(points))

}

public 
rankstats_players(id){

    new 
motd[1501],iLen;
    new 
szName[33];
    
get_user_name(id,szName,32);
    
    
iLen format(motdsizeof motd 1,"<body bgcolor=#000000><font color=#98f5ff><pre>");
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<center><h2>---- SoccerJam Stats ----</h2></center>^n^n");
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<center><b>%s</b></center>^n"szName);
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<font color=#fff000><b>%-22.22s</b>: %10i^n""Points"UserData[id][gPoints]);
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<b>%-22.22s</b>: %10i^n""Goals"UserData[id][gGoals]);
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<b>%-22.22s</b>: %10i^n""Assists"UserData[id][gAssists]);
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<b>%-22.22s</b>: %10i^n""Steals"UserData[id][gSteals]);
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<b>%-22.22s</b>: %10i^n""StealsRv"UserData[id][gStealsRv]);
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<b>%-22.22s</b>: %10i^n""Disarm"UserData[id][gDisarm]);
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<b>%-22.22s</b>: %10i^n""DisarmRv"UserData[id][gDisarmRv]);
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<b>%-22.22s</b>: %10i^n""BKills"UserData[id][gBKills]);
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<b>%-22.22s</b>: %10i^n""BKillsRv"UserData[id][gBKillsRv]);
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<b>%-22.22s</b>: %10i^n""Offsides"UserData[id][gOffside]);
    
iLen += format(motd[iLen], (sizeof motd 1) - iLen,"<b>%-22.22s</b>: %10i^n^n""Fouls"UserData[id][gFoul]);

    
show_motd(idmotd"Stats");
    
    return 
PLUGIN_HANDLED
}

public 
plugin_end() {
    if(
g_SqlTuple != Empty_Handle)
        
SQL_FreeHandle(g_SqlTuple);
}

public 
fw_ServerDown() 

    for(new 
0<= MaxPlayersi++)
    {
        if(
is_user_connected(i))
            
save_usr(i)
    }
}

public 
StatsEnable() {
    new 
suma_player get_playersnum()
    
    if(
suma_player >= 6) {
        
stats_enable 1
    
}
    else {
        
stats_enable 0
    
}

Fabregas21 is offline
Fabregas21
Junior Member
Join Date: May 2012
Old 09-02-2018 , 07:28   Re: [Database - Server Connection] - Optimization of plugin
Reply With Quote #2

Bump
Fabregas21 is offline
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

Forum Jump


All times are GMT -4. The time now is 06:49.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode