Lag on connect caused by SQL - need help.

EvolDay · **Author**

Hey guys I'm having a problem, Whenever a new map loads on the server I get SQL lag while the server checks all the clients if they have rated or not. Is there anyway i could add a delay so that it does not check all the clients simotaniously.. Thanks for any help. I posted the code below that i am using.

PHP Code:


			
#include <amxmodx>

#include <amxmisc>

#include <sqlx>

#include <colorchat>



#define PLUGIN "ratemap"

#define VERSION "0.9.6"

#define AUTHOR "sTmN"



new mapname[30],tag[30]



new Handle:g_SqlTuple

new g_Error[512]

new ErrorCode,Handle:SqlConnection



new Host[64] = "DB Host"

new User[64] = "DB User"

new Pass[64] = "DB Pass"

new Db[64] = "DB Name"

new Table[32] = "DB Table"



new g_maxplayers



new bool:g_selected = false

new bool:do_vote[33] = false



public plugin_init()

{

    register_plugin(PLUGIN, VERSION, AUTHOR)

    

    register_dictionary("ratemap.txt");

    

    register_cvar("rm_tag","[RateMap]")

    get_cvar_string("rm_tag",tag,30) 

    

    get_mapname(mapname,30)    



    set_task(15.0, "RateMap", 272456, "", 0, "b")

    g_maxplayers = get_maxplayers()

    

    table_init();

}



public sql_connect()

{

    SqlConnection = SQL_Connect(g_SqlTuple,ErrorCode,g_Error,511)

    if(SqlConnection == Empty_Handle)

        set_fail_state(g_Error)

    

}



public table_init()

{

    g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)

    

    sql_connect();



    new Handle:Query = SQL_PrepareQuery(SqlConnection,"CREATE TABLE IF NOT EXISTS %s (steamid TEXT(32),mapname TEXT(32),rate INT(11))",Table)

    if(!SQL_Execute(Query))

    {

        SQL_QueryError(Query,g_Error,511)

        set_fail_state(g_Error)

    }



    server_print("# Connected to database succesfully.") 

    SQL_FreeHandle(SqlConnection)

}



public ratemenu(id)

{

    new Temp[64]

    

    format(Temp, 64, "%L", LANG_PLAYER, "MENU_TITLE");

    new menu = menu_create(Temp,"func_ratemenu")

    

    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_1");

    menu_additem(menu, Temp,"1",0)

    

    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_2");

    menu_additem(menu, Temp,"2",0)

    

    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_3");

    menu_additem(menu, Temp,"3",0)

    

    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_4");

    menu_additem(menu, Temp,"4",0)

    

    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_5");

    menu_additem(menu, Temp,"5",0)

    

    menu_setprop(menu,MPROP_EXIT,MEXIT_ALL)

    

    menu_display(id,menu,0)

}





public func_ratemenu(id,menu,item)

{

    

    if(item==MENU_EXIT)

    {

        menu_destroy(menu);

        return PLUGIN_HANDLED

    }

    

    new data[6], iName[64]

    new access, callback

    

    menu_item_getinfo(menu, item, access, data,5, iName, 63, callback);

    

    new key = str_to_num(data);

    

    switch(key)

    {

        case 1 : ratesql(id,key);

        case 2 : ratesql(id,key);

        case 3 : ratesql(id,key);

        case 4 : ratesql(id,key);

        case 5 : ratesql(id,key);

        case 6 : ratesql(id,key);

    }

    return PLUGIN_HANDLED

} 



public ratesql(id,key)

{

    new steamid[32]

    get_user_authid(id,steamid,32)

    

    new rate[15]

    

    num_to_str(key,rate,12)

    

    sql_connect();

    

    new Handle:Queries = SQL_PrepareQuery(SqlConnection,"INSERT INTO %s VALUES('%s','%s','%s')",Table,steamid,mapname,rate)

    

    if(key==1)

        format(rate, 15, "%L", LANG_PLAYER, "OPTION_1");

    

    else if(key==2)

        format(rate, 15, "%L", LANG_PLAYER, "OPTION_2");

    

    else if(key==3)

        format(rate, 15, "%L", LANG_PLAYER, "OPTION_3");

    

    else if(key==4)

        format(rate, 15, "%L", LANG_PLAYER, "OPTION_4");

    

    else if(key==5)

        format(rate, 15, "%L", LANG_PLAYER, "OPTION_5");

     

    do_vote[id] = false

    

    ColorChat(0, RED,"%L",LANG_PLAYER,"RATEINFO",tag,steamid,rate)

    

    if(!SQL_Execute(Queries))

    {

        SQL_QueryError(Queries,g_Error,511)

        set_fail_state(g_Error)

    }

    

    SQL_FreeHandle(SqlConnection)

}



public client_authorized(id)

{

    new steamid[32]

    get_user_authid(id,steamid,31)



    sql_connect();

    new Handle:Query = SQL_PrepareQuery(SqlConnection,"SELECT steamid FROM %s WHERE steamid='%s' AND mapname='%s'",Table,steamid,mapname)

    

    if(!SQL_Execute(Query))

    {

        SQL_QueryError(Query,g_Error,511)

        set_fail_state(g_Error)

    }

        

    if(!SQL_MoreResults(Query))

    {

        do_vote[id] = true

    } 

    else 

    {      

        do_vote[id] = false

    }

    SQL_FreeHandle(SqlConnection)

}



public client_disconnect(id)

{

    do_vote[id] = false

}



public RateMap()

{

    new timeleft = get_timeleft()

        

    if (timeleft < 60 || timeleft > 308)

    {

        g_selected = false

        return

    }



    if (g_selected)

        return

    

    g_selected = true

    votecheck()

}



public votecheck()

{    

    for(new i = 1; i <= g_maxplayers; ++i)

    {

        if (do_vote[i] && !is_user_connected(i) && !is_user_connecting(i))

        {

            ratemenu(i)

        }

    }

}

Max! · *Last edited by Max!; 07-04-2009 at 01:25.*

im a newbie to coding n such, but couldnt you loop through and set a task like

PHP Code:


			
new j = 1;
for(new i=0; i < 33; i++)
{
 if(is_user_connected(i))
  {
    set_task(j, "function", i);
    j++;
  }
}

if im not mistaken and thats what you want, it would set a task for one person every second.

edit: i have been informed by a trustworthy source that doing it that way is very bad.

darn

EvolDay · 07-04-2009 , 01:46 Re: Lag on connect caused by SQL - need help.

Thanks for trying to help

, does anyone know of a way thats not a very bad way to do that?

Sylwester · *Last edited by Sylwester; 07-04-2009 at 04:14.*

There is no point in using sqlx if it is used like that...
Remove all SQL_Connect, SQL_PrepareQuery, SQL_Execute and use SQL_ThreadQuery instead.

edit:
With SQL_ThreadQuery you can check all players simultaneously and there will be no lag.

I don't understand what is this plugin supposed to do. It would make sense if there was only 1 map on server, but once map changes and players vote again, then vote for previous map is overwritten.
I checked orginal plugin and in my opinion it suck...

EvolDay · 07-04-2009 , 04:34 Re: Lag on connect caused by SQL - need help.

it lets you rate a maps on the server, and stores the rating in a db. then you can like put the rating of you mapcycle on your website.

I wouldnt know how to use SQL_ThreadQuery.

Sylwester · 07-04-2009 , 05:30 Re: Lag on connect caused by SQL - need help.

not tested:

PHP Code:


			
#include <amxmodx>
#include <amxmisc>
#include <sqlx>
#include <colorchat>

#define PLUGIN "ratemap"
#define VERSION "0.9.6"
#define AUTHOR "sTmN"

new mapname[30],tag[30]

new Handle:g_SqlTuple

new Host[64] = "DB Host"
new User[64] = "DB User"
new Pass[64] = "DB Pass"
new Db[64] = "DB Name"
new Table[32] = "DB Table"

new g_maxplayers

new bool:g_selected = false
new bool:do_vote[33] = false
new bool:g_db_ready
new bool:g_authorized[33]

public plugin_init()
{
    register_plugin(PLUGIN, VERSION, AUTHOR)
    
    register_dictionary("ratemap.txt");
    
    register_cvar("rm_tag","[RateMap]")
    get_cvar_string("rm_tag",tag,30) 
    
    get_mapname(mapname,30)    

    set_task(15.0, "RateMap", 272456, "", 0, "b")
    g_maxplayers = get_maxplayers()
    
    table_init();
}

public table_init()
{
    g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)
    g_db_ready = false
    new cache[128]
    format(cache, 127, "CREATE TABLE IF NOT EXISTS %s (steamid TEXT(32),mapname TEXT(32),rate INT(11))",Table)
    SQL_ThreadQuery(g_SqlTuple, "handle_create_table_query", cache)
}

public handle_create_table_query(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState)
    {
        log_amx("SQL Error: %s (%d)", Error, Errcode)
        return PLUGIN_HANDLED
    }
    g_db_ready = true
    for(new i=1; i<=g_maxplayers; i++)
        if(is_user_connected(i) && g_authorized[i])
            check_user_vote(i)
    return PLUGIN_CONTINUE
}

public ratemenu(id)
{
    new Temp[64]
    
    format(Temp, 64, "%L", LANG_PLAYER, "MENU_TITLE");
    new menu = menu_create(Temp,"func_ratemenu")
    
    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_1");
    menu_additem(menu, Temp,"1",0)
    
    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_2");
    menu_additem(menu, Temp,"2",0)
    
    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_3");
    menu_additem(menu, Temp,"3",0)
    
    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_4");
    menu_additem(menu, Temp,"4",0)
    
    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_5");
    menu_additem(menu, Temp,"5",0)
    
    menu_setprop(menu,MPROP_EXIT,MEXIT_ALL)
    
    menu_display(id,menu,0)
}


public func_ratemenu(id,menu,item)
{
    
    if(item==MENU_EXIT)
    {
        menu_destroy(menu);
        return PLUGIN_HANDLED
    }
    
    new data[6], iName[64]
    new access, callback
    
    menu_item_getinfo(menu, item, access, data,5, iName, 63, callback);
    
    new key = str_to_num(data);
    
    switch(key)
    {
        case 1 : ratesql(id,key);
        case 2 : ratesql(id,key);
        case 3 : ratesql(id,key);
        case 4 : ratesql(id,key);
        case 5 : ratesql(id,key);
        case 6 : ratesql(id,key);
    }
    return PLUGIN_HANDLED
} 

public ratesql(id,key)
{
    if(!g_db_ready)
        return
    new steamid[32]
    get_user_authid(id,steamid,32)
    
    new rate[15]
    
    num_to_str(key,rate,12)
    new cache[256]
    format(cache, 255, "INSERT INTO %s VALUES('%s','%s','%s')",Table,steamid,mapname,rate)
    SQL_ThreadQuery(g_SqlTuple, "handle_sql_insert", cache)

    if(key==1)
        format(rate, 15, "%L", LANG_PLAYER, "OPTION_1");
    
    else if(key==2)
        format(rate, 15, "%L", LANG_PLAYER, "OPTION_2");
    
    else if(key==3)
        format(rate, 15, "%L", LANG_PLAYER, "OPTION_3");
    
    else if(key==4)
        format(rate, 15, "%L", LANG_PLAYER, "OPTION_4");
    
    else if(key==5)
        format(rate, 15, "%L", LANG_PLAYER, "OPTION_5");
     
    do_vote[id] = false
    
    ColorChat(0, RED,"%L",LANG_PLAYER,"RATEINFO",tag,steamid,rate)
}

public handle_sql_insert(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState)
    {
        log_amx("SQL Error: %s (%d)", Error, Errcode)
        return PLUGIN_HANDLED
    }
    return PLUGIN_CONTINUE
}

public client_authorized(id)
{
    g_authorized[id] = true
    if(!g_db_ready)
        return
    check_user_vote(id)
}

public check_user_vote(id)
{
    new steamid[32], data[2], cache[128]
    get_user_authid(id,steamid,31)
    data[0] = id
    format(cache, 127, "SELECT steamid FROM %s WHERE steamid='%s' AND mapname='%s'",Table,steamid,mapname)
    SQL_ThreadQuery(g_SqlTuple, "handle_sql_check_vote", cache, data, 1)
}

public handle_sql_check_vote(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState)
    {
        log_amx("SQL Error: %s (%d)", Error, Errcode)
        return PLUGIN_HANDLED
    }
    if(!SQL_MoreResults(Query))
    {
        do_vote[Data[0]] = true
    }
    return PLUGIN_CONTINUE
}

public client_connect(id)
{
    do_vote[id] = false
    g_authorized[id] = false
}

public RateMap()
{
    new timeleft = get_timeleft()
        
    if (timeleft < 60 || timeleft > 308)
    {
        g_selected = false
        return
    }

    if (g_selected)
        return
    
    g_selected = true
    votecheck()
}

public votecheck()
{    
    for(new i = 1; i <= g_maxplayers; i++)
    {
        if (do_vote[i] && is_user_connected(i))
        {
            ratemenu(i)
        }
    }
}

EvolDay · 07-04-2009 , 12:28 Re: Lag on connect caused by SQL - need help.

It seems to be working without any lag now. But there is one problem with it, It only allows you to vote once. On the nextmap if you voted for a different map it wont let you vote again.

Sylwester · 07-04-2009 , 13:05 Re: Lag on connect caused by SQL - need help.

I used same sql requests and I detect if you have already voted in the same way. If the old version worked then I'm not sure why this is not working.

You can try to set 2 primary keys (replace line):

PHP Code:


			
format(cache, 127, "CREATE TABLE IF NOT EXISTS %s (steamid TEXT(32),mapname TEXT(32),rate INT(11), PRIMARY KEY (steamid, mapname))",Table)

I'm not sure if this one will fix the problem (make sure you delete old table from database before you try this).

You can also create separate table for each map and store only steamid and rating and use 1 primary key.

EvolDay · 07-04-2009 , 14:51 Re: Lag on connect caused by SQL - need help.

i changed this, and i deleted the table in the sql but now votes dont happen at all.

PHP Code:


			
public table_init()

{

    g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)

    g_db_ready = false

    new cache[128]

    //format(cache, 127, "CREATE TABLE IF NOT EXISTS %s (steamid TEXT(32),mapname TEXT(32),rate INT(11))",Table)

    format(cache, 127, "CREATE TABLE IF NOT EXISTS %s (steamid TEXT(32),mapname TEXT(32),rate INT(11), PRIMARY KEY (steamid, mapname))",Table) 

    

    SQL_ThreadQuery(g_SqlTuple, "handle_create_table_query", cache)

}

i also changed this so i didnt have to wait forever to test it dunno of that could be the prob.

PHP Code:


			
    if (timeleft < 600 || timeleft > 908)

    {

        g_selected = false

        return

    }

Sylwester · *Last edited by Sylwester; 07-04-2009 at 16:44.*

I tried it and previous version was almost fine. After mapchange only users who didn't leave server were not able to vote because I forgot is_user_connecting().
this is tested and works fine:

PHP Code:


			
#include <amxmodx>
#include <amxmisc>
#include <sqlx>
#include <colorchat>

#define PLUGIN "ratemap"
#define VERSION "0.9.6"
#define AUTHOR "sTmN"

new mapname[30],tag[30]

new Handle:g_SqlTuple

new Host[64] = "127.0.0.1"
new User[64] = "root"
new Pass[64] = ""
new Db[64] = "test"
new Table[32] = "rm_rates2"

new g_maxplayers

new bool:g_selected = false
new bool:do_vote[33] = false
new bool:g_db_ready
new bool:g_authorized[33]

public plugin_init()
{
    register_plugin(PLUGIN, VERSION, AUTHOR)
    
    register_dictionary("ratemap.txt");
    
    register_cvar("rm_tag","[RateMap]")
    get_cvar_string("rm_tag",tag,30)
    
    get_mapname(mapname,30)    

    set_task(15.0, "RateMap", 272456, "", 0, "b")
    g_maxplayers = get_maxplayers()
    
    table_init();
}

public table_init()
{
    g_SqlTuple = SQL_MakeDbTuple(Host,User,Pass,Db)
    g_db_ready = false
    new cache[128]
    format(cache, 127, "CREATE TABLE IF NOT EXISTS %s (steamid TEXT(32),mapname TEXT(32),rate INT(11))",Table)
    SQL_ThreadQuery(g_SqlTuple, "handle_create_table_query", cache)
}

public handle_create_table_query(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState)
    {
        log_amx("SQL Error: %s (%d)", Error, Errcode)
        return PLUGIN_HANDLED
    }
    g_db_ready = true
    for(new i=1; i<=g_maxplayers; i++)
        if((is_user_connected(i) || is_user_connecting(i)) && g_authorized[i])
            check_user_vote(i)
    return PLUGIN_CONTINUE
}

public ratemenu(id)
{
    new Temp[64]
    
    format(Temp, 64, "%L", LANG_PLAYER, "MENU_TITLE");
    new menu = menu_create(Temp,"func_ratemenu")
    
    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_1");
    menu_additem(menu, Temp,"1",0)
    
    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_2");
    menu_additem(menu, Temp,"2",0)
    
    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_3");
    menu_additem(menu, Temp,"3",0)

    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_4");
    menu_additem(menu, Temp,"4",0)
    
    format(Temp, 64, "%L", LANG_PLAYER, "OPTION_5");
    menu_additem(menu, Temp,"5",0)
    
    menu_setprop(menu,MPROP_EXIT,MEXIT_ALL)
    
    menu_display(id,menu,0)
}


public func_ratemenu(id,menu,item)
{
    
    if(item==MENU_EXIT)
    {
        menu_destroy(menu);
        return PLUGIN_HANDLED
    }
    
    new data[6], iName[64]
    new access, callback

    menu_item_getinfo(menu, item, access, data,5, iName, 63, callback);
    
    new key = str_to_num(data);

    switch(key)
    {
        case 1 : ratesql(id,key);
        case 2 : ratesql(id,key);
        case 3 : ratesql(id,key);
        case 4 : ratesql(id,key);
        case 5 : ratesql(id,key);
        case 6 : ratesql(id,key);
    }
    return PLUGIN_HANDLED
} 

public ratesql(id,key)
{
    if(!g_db_ready)
        return
    new steamid[32]
    get_user_authid(id,steamid,32)
    
    new rate[15]
    
    num_to_str(key,rate,12)
    new cache[256]
    format(cache, 255, "INSERT INTO %s VALUES('%s','%s','%s')",Table,steamid,mapname,rate)
    SQL_ThreadQuery(g_SqlTuple, "handle_sql_insert", cache)

    if(key==1)
        format(rate, 15, "%L", LANG_PLAYER, "OPTION_1");
    
    else if(key==2)
        format(rate, 15, "%L", LANG_PLAYER, "OPTION_2");
    
    else if(key==3)
        format(rate, 15, "%L", LANG_PLAYER, "OPTION_3");
    
    else if(key==4)
        format(rate, 15, "%L", LANG_PLAYER, "OPTION_4");
    
    else if(key==5)
        format(rate, 15, "%L", LANG_PLAYER, "OPTION_5");
     
    do_vote[id] = false
    
    ColorChat(0, RED,"%L",LANG_PLAYER,"RATEINFO",tag,steamid,rate)
}

public handle_sql_insert(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState)
    {
        log_amx("SQL Error: %s (%d)", Error, Errcode)
        return PLUGIN_HANDLED
    }
    return PLUGIN_CONTINUE
}

public client_authorized(id)
{
    if(is_user_bot(id))
        return
    g_authorized[id] = true
    if(!g_db_ready)
        return
    check_user_vote(id)
}

public check_user_vote(id)
{
    new steamid[32], data[2], cache[256]
    get_user_authid(id,steamid,31)
    data[0] = id
    format(cache, 255, "SELECT steamid FROM %s WHERE steamid='%s' AND mapname='%s'",Table,steamid,mapname)
    SQL_ThreadQuery(g_SqlTuple, "handle_sql_check_vote", cache, data, 1)
}

public handle_sql_check_vote(FailState,Handle:Query,Error[],Errcode,Data[],DataSize)
{
    if(FailState)
    {
        log_amx("SQL Error: %s (%d)", Error, Errcode)
        return PLUGIN_HANDLED
    }
    if(!SQL_MoreResults(Query))
    {
        do_vote[Data[0]] = true
    }
    return PLUGIN_CONTINUE
}

public client_connect(id)
{
    do_vote[id] = false
    g_authorized[id] = false
}

public RateMap()
{
    new timeleft = get_timeleft()
    if (timeleft < 60 || timeleft > 308)
    {
        g_selected = false
        return
    }

    if (g_selected)
        return
    
    g_selected = true
    votecheck()
}

public votecheck()
{
    for(new i = 1; i <= g_maxplayers; i++)
    {
        if (do_vote[i] && is_user_connected(i))
        {
            ratemenu(i)
        }
    }
}

Also you should do something about vote starting. That task is unnecessary and users should get some warning (countdown etc.) before vote start. Vote should appear for player after he played current map some time (not 15 seconds

).

EDIT: if voting does not appear and you don't know why then change this and it will appear 15 secs after mapchange:

PHP Code:


			
public RateMap()
{
    new timeleft = get_timeleft()
    /*if (timeleft < 60 || timeleft > 308)
    {
        g_selected = false
        return
    } */

    if (g_selected)
        return
    
    g_selected = true
    votecheck()
}