AlliedModders

AlliedModders (https://forums.alliedmods.net/index.php)
-   Scripting Help (https://forums.alliedmods.net/forumdisplay.php?f=11)
-   -   sql optimization (https://forums.alliedmods.net/showthread.php?t=168797)

vershinin 10-04-2011 04:33

sql optimization
 
hello!i wrote a simple plugin
Code:

#include <amxmodx>
#include <sqlx>
#define PLUGIN "monic"
#define VERSION "1.0"
#define AUTHOR "lee"
new host[32],login[32],pass[32],bdname[32],table[32]
public plugin_init()
{
        register_plugin(PLUGIN, VERSION, AUTHOR)       
        register_event("SendAudio","round_end","a","2=%!MRAD_terwin","2=%!MRAD_ctwin")       
        register_cvar("monic_host", "")
        register_cvar("monic_login", "")
        register_cvar("monic_pass", "")
        register_cvar("monic_bdname", "")
        register_cvar("monic_table", "")
        get_cvar_string("monic_host",host,31)
        get_cvar_string("monic_login",login,31)
        get_cvar_string("monic_pass",pass,31)
        get_cvar_string("monic_bdname",bdname,31)
        get_cvar_string("monic_table",table,31)
}

new Handle:SQL_Tuple
new Handle:SQL_Connection
new err, error[256]
public round_end()
{       
                       
        SQL_Tuple =  SQL_MakeDbTuple(host, login, pass, bdname)
        SQL_Connection = SQL_Connect(SQL_Tuple, err, error, charsmax(error))       
        if(SQL_Connection != Empty_Handle)
        {
                //log_amx("host = %s, login = %s, pass= %s, bdname = %s, table = %s",host,login,pass,bdname,table)
                new Handle:query_tranc = SQL_PrepareQuery(SQL_Connection,"TRUNCATE TABLE `%s`",table)
                if (!SQL_Execute(query_tranc))
                {                               
                        log_amx("Cant TRUNCATE table: %s ",error)                               
                        return PLUGIN_HANDLED
                }       
                new map[32]
                get_mapname(map,31)
                new Handle:query_map = SQL_PrepareQuery(SQL_Connection,"INSERT INTO `%s` (`map`) VALUES ('%s')",table, map)
                if (!SQL_Execute(query_map))
                {                               
                        log_amx("Cant INSERT table: %s ",error)                               
                        return PLUGIN_HANDLED
                }       
                new online       
                online=get_playersnum()
                if (online == 0 ) return PLUGIN_HANDLED                               
                new Players[32]
                new Count, i               
                new name[32][40],sid[32][40],frag[32],tm[32]
                get_players(Players, Count, "h")
                       
                for (i=0; i<Count; i++)
                        {                               
                                get_user_name(Players[i],name[i],36)
                                trim(name[i])
                                replace_all(name[i],40,"'","ap1")
                                replace_all(name[i],40,"`","ap2")
                                frag[i]=get_user_frags(Players[i])
                                get_user_authid(Players[i],sid[i],36)
                                tm[i]=get_user_team(Players[i])                               
                        }                                       
                       
                for (i=0; i<Count; i++)
                        {       
                                new Handle:query = SQL_PrepareQuery(SQL_Connection,"INSERT INTO `%s` (`name`,`sid`,`frag`, `team`) VALUES ('%s','%s',%d, %d)",table,name[i],sid[i],frag[i], tm[i])//вставка в таблицу
                                if (!SQL_Execute(query))
                                {                               
                                        log_amx("Cant INSERT table: %s ",error)
                                        log_amx("name = %s, sid = %s, team = %d, frag = %d",name[i],sid[i],tm[i],frag[i])
                                        return PLUGIN_HANDLED
                                }       
                        }               
                SQL_FreeHandle(SQL_Connection )
                SQL_FreeHandle(SQL_Tuple)               
                return PLUGIN_HANDLED
                }else{
                        log_amx("cant connect %s ",error)
                        return PLUGIN_HANDLED
                }
               
        return PLUGIN_HANDLED
}

connection with database is not good. so in this part of code
Code:

for (i=0; i<Count; i++)
{       
new Handle:query = SQL_PrepareQuery(SQL_Connection,"INSERT INTO `%s` (`name`,`sid`,`frag`, `team`) VALUES ('%s','%s',%d, %d)",table,name[i],sid[i],frag[i], tm[i])//вставка в таблицу
if (!SQL_Execute(query))
        {                               
        log_amx("Cant INSERT table: %s ",error)
        log_amx("name = %s, sid = %s, team = %d, frag = %d",name[i],sid[i],tm[i],frag[i])
        return PLUGIN_HANDLED
        }

server is paused a few seconds.. how it optimize? sql_threadquery?

Xellath 10-04-2011 15:52

Re: sql optimization
 
The thing about your code is that:
You use variable i incorrectly. It's used as the index of a player, where it alone just represents an index in the Players[] array. So using for instance; name[i] would be incorrect, whereas using name[players[i]] would be correct. Even better:
Code:
new player = players[i]; name[player] = ...
That would use less resources.

You could also try using threaded queries (note that they are based on a FIFO (first-in-first-out) basis; meaning that they stack up and wait for execution):
Code:
new const Host[ ] = ""; new const User[ ] = ""; new const Pass[ ] = ""; new const DB[ ] = ""; new Handle:SQLTuple; SQLTuple = SQL_MakeDbTuple( Host, User, Pass, DB ); new Query[ 128 ]; formatex( Query, charsmax( Query ), "QUERY GOES HERE" ); SQL_ThreadQuery( SQLTuple, "QueryHandler", Query /*, ... data, datasize */); public QueryHandler( Failstate, Handle:Query, Error[], ErrorCode, Data[], Size, Float:QueueTime ) {     if( Failstate == TQUERY_CONNECT_FAILED     || Failstate == TQUERY_QUERY_FAILED )     {         log_amx( "SQL Error: %s (%i)", Error, ErrorCode );         return;     } }

vershinin 10-06-2011 02:19

Re: sql optimization
 
ok.. but there are many threads of query , it is not good..how to fix this?


All times are GMT -4. The time now is 19:34.

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