Raised This Month: $ Target: $400
 0% 

MySQL client_aughorized lag


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
raa
Senior Member
Join Date: Oct 2005
Old 01-12-2007 , 21:00   MySQL client_aughorized lag
Reply With Quote #1

How can I help to make this not lag everyone when a player joins the server?

Code:
public client_authorized(id)     
	{
	if(get_cvar_num("sv_ranks")==0)
		return PLUGIN_HANDLED
	
	new HN_NOTAPP_XP = 0
	new HN_WARRANT_OFFICER_XP = 1000
	new HN_CHIEF_WARRANT_OFFICER_XP = 1200
	new HN_SECOND_LIEUTENANT_XP = 1400
	new HN_FIRST_LIEUTENANT_XP = 1600
	new HN_CAPTAIN_XP = 1800
	new KN_NOTAPP_XP = 0
	new KN_PRIVATE_XP = 100
	new KN_PRIVATE_FIRST_CLASS_XP = 150
	new KN_LANCE_CORPORAL_XP = 225
	new KN_CORPORAL_XP = 337
	new KN_SERGEANT_XP = 506
	new KN_STAFF_SERGEANT_XP = 759
	new KN_FIRST_SERGEANT_XP = 1139
	new KN_MASTER_SERGEANT_XP = 1708
	new KN_SERGEANT_MAJOR_XP = 2562
	new KN_WARRANT_OFFICER_XP = 3583
	new KN_CHIEF_WARRANT_OFFICER_XP = 4325
	new KN_SECOND_LIEUTENANT_XP = 6487
	new KN_FIRST_LIEUTENANT_XP = 9731
	new KN_CAPTAIN_XP = 14597
		
	new g_authid[32]
	get_user_authid(id,g_authid,31)
	
	// sql connect
	new query[256]
	g_dbc = dbi_connect(g_host,g_user,g_pass,g_dbname,g_error,MAX_NAME_LENGTH)
	
	if (g_dbc == SQL_FAILED)
		log_amx("[sqlStats] SQL Connection Failed")
	else{
		format( query, 255, "SELECT authid,kills,headshots FROM statstable WHERE authid='%s'", g_authid)
		result = dbi_query(g_dbc, query)
		
		if (g_dbc == SQL_FAILED )
			{
			server_print( "Query failed!")
			client_print(id,print_chat, "* Database Unavailable");
			dbi_free_result(result)
			return PLUGIN_HANDLED
		}
		
		new g_Data_kills[33]
		new g_Data_HS[33]
		while(dbi_nextrow(result))
			{
			g_Data_kills[id] = dbi_result(result,"kills")
			g_Data_HS[id] = dbi_result(result,"headshots")
			if(g_Data_kills[id] < 100)
				{
				PlayerRank[id] = RANK_CIVILIAN
				KILLSREMAIN[id] = (KN_PRIVATE_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_NOTAPP_XP)
			}
			if(g_Data_kills[id] >= 100 && g_Data_kills[id] <= 150)
				{
				PlayerRank[id] = RANK_PRIVATE
				KILLSREMAIN[id] = (KN_PRIVATE_FIRST_CLASS_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_NOTAPP_XP)
			}
			if(g_Data_kills[id] >= 150 && g_Data_kills[id] <= 225)
				{
				PlayerRank[id] = RANK_PRIVATE_FIRST_CLASS
				KILLSREMAIN[id] = (KN_LANCE_CORPORAL_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_NOTAPP_XP)
			}
			if(g_Data_kills[id] >= 225 && g_Data_kills[id] <= 337)
				{
				PlayerRank[id] = RANK_LANCE_CORPORAL
				KILLSREMAIN[id] = (KN_CORPORAL_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_NOTAPP_XP)
			}
			if(g_Data_kills[id] >= 337 && g_Data_kills[id] <= 506)
				{
				PlayerRank[id] = RANK_CORPORAL
				KILLSREMAIN[id] = (KN_SERGEANT_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_NOTAPP_XP)
			}
			if(g_Data_kills[id] >= 506 && g_Data_kills[id] <= 759)
				{
				PlayerRank[id] = RANK_SERGEANT
				KILLSREMAIN[id] = (KN_STAFF_SERGEANT_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_NOTAPP_XP)
			}
			if(g_Data_kills[id] >= 759 && g_Data_kills[id] <= 1139)
				{
				PlayerRank[id] = RANK_STAFF_SERGEANT
				KILLSREMAIN[id] = (KN_FIRST_SERGEANT_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_NOTAPP_XP)
			}
			if(g_Data_kills[id] >= 1139 && g_Data_kills[id] <= 1708)
				{
				PlayerRank[id] = RANK_FIRST_SERGEANT
				KILLSREMAIN[id] = (KN_MASTER_SERGEANT_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_NOTAPP_XP)
			}
			if(g_Data_kills[id] >= 1708 && g_Data_kills[id] <= 2562)
				{
				PlayerRank[id] = RANK_MASTER_SERGEANT
				KILLSREMAIN[id] = (KN_SERGEANT_MAJOR_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_NOTAPP_XP)
			}
			if(g_Data_kills[id] >= 2562 && g_Data_kills[id] <= 3583)
				{
				PlayerRank[id] = RANK_SERGEANT_MAJOR
				KILLSREMAIN[id] = (KN_WARRANT_OFFICER_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_NOTAPP_XP)
			}
			if(g_Data_kills[id] >= 3583 && g_Data_kills[id] <= 4325 && g_Data_HS[id] >= 0 && g_Data_HS[id] < 1000)
				{
				PlayerRank[id] = RANK_SERGEANT_MAJOR
				KILLSREMAIN[id] = (KN_NOTAPP_XP)
				HSREMAIN[id] = (HN_WARRANT_OFFICER_XP  -  g_Data_HS[id])
			}
			if(g_Data_kills[id] >= 4325 && g_Data_kills[id] <= 6487 && g_Data_HS[id] >= 1000 && g_Data_HS[id] < 1200)
				{
				PlayerRank[id] = RANK_WARRANT_OFFICER
				KILLSREMAIN[id] = (KN_NOTAPP_XP)
				HSREMAIN[id] = (HN_CHIEF_WARRANT_OFFICER_XP  -  g_Data_HS[id])
			}
			if(g_Data_kills[id] >= 6487 && g_Data_kills[id] <= 9731 && g_Data_HS[id] >= 1200 && g_Data_HS[id] < 1400)
				{
				PlayerRank[id] = RANK_CHIEF_WARRANT_OFFICER
				KILLSREMAIN[id] = (KN_NOTAPP_XP)
				HSREMAIN[id] = (HN_SECOND_LIEUTENANT_XP  -  g_Data_HS[id])
			}
			if(g_Data_kills[id] >= 9731 && g_Data_kills[id] <= 14597 && g_Data_HS[id] >= 1400 && g_Data_HS[id] < 1600)
				{
				PlayerRank[id] = RANK_SECOND_LIEUTENANT
				KILLSREMAIN[id] = (KN_NOTAPP_XP)
				HSREMAIN[id] = (HN_FIRST_LIEUTENANT_XP -  g_Data_HS[id])
			}
			if(g_Data_kills[id] >= 14597 && g_Data_kills[id] <= 21895 && g_Data_HS[id] >= 1600 && g_Data_HS[id] < 1800)
				{
				PlayerRank[id] = RANK_FIRST_LIEUTENANT
				KILLSREMAIN[id] = (KN_NOTAPP_XP)
				HSREMAIN[id] = (HN_CAPTAIN_XP  -  g_Data_HS[id])
			}
			if(g_Data_kills[id] >= 21895 && g_Data_kills[id] <= 32842 && g_Data_HS[id] >= 1800 && g_Data_HS[id] < 2000)
				{
				PlayerRank[id] = RANK_CAPTAIN
				KILLSREMAIN[id] = (KN_NOTAPP_XP)
				HSREMAIN[id] = (HN_MAJOR_XP  -  g_Data_HS[id])
			}
			//HS
			if(g_Data_kills[id] >= 3583 && g_Data_kills[id] <= 4325 && g_Data_HS[id] >= 1000)
				{
				PlayerRank[id] = RANK_WARRANT_OFFICER
				KILLSREMAIN[id] = (KN_CHIEF_WARRANT_OFFICER_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_CHIEF_WARRANT_OFFICER_XP  -  g_Data_HS[id])
			}
			if(g_Data_kills[id] >= 4325 && g_Data_kills[id] <= 6487 && g_Data_HS[id] >= 1200)
				{
				PlayerRank[id] = RANK_CHIEF_WARRANT_OFFICER
				KILLSREMAIN[id] = (KN_SECOND_LIEUTENANT_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_SECOND_LIEUTENANT_XP  -  g_Data_HS[id])
			}
			if(g_Data_kills[id] >= 6487 && g_Data_kills[id] <= 9731 && g_Data_HS[id] >= 1400)
				{
				PlayerRank[id] = RANK_SECOND_LIEUTENANT
				KILLSREMAIN[id] = (KN_FIRST_LIEUTENANT_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_FIRST_LIEUTENANT_XP -  g_Data_HS[id])
			}
			if(g_Data_kills[id] >= 9731 && g_Data_kills[id] <= 14597 && g_Data_HS[id] >= 1600)
				{
				PlayerRank[id] = RANK_FIRST_LIEUTENANT
				KILLSREMAIN[id] = (KN_CAPTAIN_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_CAPTAIN_XP  -  g_Data_HS[id])
			}
			if(g_Data_kills[id] >= 14597 && g_Data_kills[id] <= 21895 && g_Data_HS[id] >= 1800)
				{
				PlayerRank[id] = RANK_CAPTAIN
				KILLSREMAIN[id] = (KN_MAJOR_XP  -  g_Data_kills[id])
				HSREMAIN[id] = (HN_MAJOR_XP  -  g_Data_HS[id])
			}
			if(HSREMAIN[id] < 0)
				{
				HSREMAIN[id] = 0
			}
			client_print(id,print_chat, "* You are a %s with %d kills and %d headshots.", RANKS[PlayerRank[id]], g_Data_kills[id], g_Data_HS[id]);
			client_print(id,print_chat, "* You need %d kills and %d headshots until your next advancement.", KILLSREMAIN[id], HSREMAIN[id]);
			dbi_free_result(result)
			dbi_close(g_dbc)
			
		}
	}
	return PLUGIN_CONTINUE
}
__________________
raa is offline
Brad
AMX Mod X Team Member
Join Date: Jun 2004
Old 01-12-2007 , 21:41   Re: MySQL client_aughorized lag
Reply With Quote #2

Use SQLx, the new threaded query system instead. Player is authorized (though I'd use client_putinserver instead), you fire off the query, game goes on, query comes back and you act on it.

I'd put it in client_putinserver instead because you'll perform the query less often as not everyone who authorizes actually makes it into the server. Further, if you're using sv_downloadurl, clients will authorize twice very frequently, thus you'd be running the query and code much more often than you need to.
__________________
Brad is offline
raa
Senior Member
Join Date: Oct 2005
Old 01-12-2007 , 23:17   Re: MySQL client_aughorized lag
Reply With Quote #3

SQLx huh...

Thats what I was afraid of.. I'm pretty dull already and struggled getting DBI to work. Every time I've sat down and went their the tutorial for SQLx I ended up running away with my tail between my legs... lol

I will definitely keep trying though.

I will also use client_putinserver as well.

One thing I was thinking that might be causing some of lag is the basic nature at which I'm determining the Ranks. (ie. If else if else if else, etc. bleh)

I started to try and adapt that function like so;

Code:
new PlayerRank[33]

new const rankNames[MAXRANKS][] = 
{ 
	"rank1",
	"rank2",
	"rank3",
	"rank4"
}

new const rankXP[MAXRANKS] = 
{
	0, 100, 150, 225
}

public plugin_init() 
{
 register_plugin("bleh", "0.1", "bleh")
}

public client_putinserver(id)
{ 
	set_task(HUD_INTERVAL, "ShowHUD", id)
	return PLUGIN_HANDLED
}

public ShowHUD(id)     
{  
	if(!is_user_connected(id))
		return 0
 
	static stats[8], hits[8], name[33]
	get_user_stats(id, stats, hits)
 	get_user_name(id, name, 32)
 
	new currentPlayerRank = 0;
	while(currentPlayerRank < (MAXRANKS - 1))
	{
		if(stats[0] >= rankXP[currentPlayerRank + 1])
			++currentPlayerRank;
		else
			break;
	}
	
	//code here
 
	return PLUGIN_HANDLED
}
But I then realized that that example I was working off of only had one prerequisite. While mine has two, "kills and headshots". And I couldn't figure out how to include that extra condition.

Anyway, does anyone think I should put any effort into adapting the above?
Or perhaps use CASE's instead of IF ELSE?

Would either of those even produce any noticeable optimization?
__________________
raa is offline
Brad
AMX Mod X Team Member
Join Date: Jun 2004
Old 01-13-2007 , 14:04   Re: MySQL client_aughorized lag
Reply With Quote #4

Using a switch instead of if/else is faster though I don't know that you could do it with your code. Also, it's not appreciably faster in this context.

If you want to know exactly what in your code is causing the lag, you need to instrument it. In other words, do something like this around statements that you think could be causing a problem:

Code:
new start = seconds(); // one or more statements log_to_file("_test", "blahblah took %i seconds", seconds() - start);
Doing this will tell you where you need to optimize the code. My guess is that the dbi_query line is causing your problems thus my suggestion to use SQLx.
__________________
Brad is offline
raa
Senior Member
Join Date: Oct 2005
Old 01-13-2007 , 18:59   Re: MySQL client_aughorized lag
Reply With Quote #5

Thanks for the reply. I will add that code for sure.

I see that you give assistance for a decent price. I will PM you now.
__________________
raa is offline
Reply



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 22:25.


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