[Type-R]

Hey guys, i have a problem. I have a problem, where i scan through all the players on the database and they added up to 13k... Now when i scan through the database it takes a while for it to scan and it lags up the whole server, other players show up AFK. example:

query

Код:

mysql_tquery(mysql, "SELECT Vardas,Specialybe,Siandien,Dispejimai FROM vartotojai ORDER BY XP", "darbuotojai", "i", playerid);

public:

Код:

public darbuotojai(playerid)
{
    new PRAZAIDZIAUX;
	new PLAYERANMESAVEq[ MAX_PLAYER_NAME ];
	new PLAYERSPECIALYBE;
	new SAVESTRINGtqx5[ 3000 ];
	new PLAYERSTATUSqq[ 40 ];
	new ISPEJIMAIDKASD;

    new rows, fields; 
	cache_get_data(rows, fields, mysql);
	if(rows) 
	{
		for(new i=0; i<rows; i++)
		{
       		
		    cache_get_field_content(i, "Vardas", PLAYERANMESAVEq, mysql, 25);
			PLAYERSPECIALYBE = cache_get_field_content_int(i, "Specialybe");
			PRAZAIDZIAUX = cache_get_field_content_int(i, "Siandien");
			ISPEJIMAIDKASD = cache_get_field_content_int(i, "Dispejimai");
			if(GETPLAYERJOBFROMFACTION( playerid ) == PLAYERSPECIALYBE )
			{
		        if( GetPlayerIdFromNameEx( playerid, PLAYERANMESAVEq) == INVALID_PLAYER_ID )PLAYERSTATUSqq = "{FF0000}neprisijunges";
		        else
		        {
					PLAYERSTATUSqq = "{00FF00}prisijunges";
				}
		        format( SAVESTRINGtqx5,3000,"%s{AECEB3}%s {B4C877}(%s{B4C877}) (siandien zaide: {AECEB3}%d {B4C877}min.) ({AECEB3}%d {B4C877}ispejimu(-as))\n",SAVESTRINGtqx5,PLAYERANMESAVEq,PLAYERSTATUSqq,PRAZAIDZIAUX,ISPEJIMAIDKASD );
		    }
		}
        strcat( SAVESTRINGtqx5,"\n\nIsmesti darbuotoja irasykite jo pilna varda:");
		ShowPlayerDialog(playerid,1879,DIALOG_STYLE_INPUT,"{A3E4FF}Darbuotojai",SAVESTRINGtqx5,"Gerai","Iseiti");
	}
	return 1;
}

This produces lag on the whole server. Is there a way around lag, other than deleting the players? Thank you.

[Vince]

Honestly, what do you even expect if you try to parse 13,000 results? That's why pagination was invented.

[Type-R]

Thats what i thought. What do you think is the best way to protect against this? delete players after 30-60 days of inactivity? look up players in a different way? or is there a different way? Also what do you think, is the maximum rows i should go through?

[AndySedeyn]

Vince mentioned exactly what you should do:

Quote:

Originally Posted by Vince That's why pagination was invented.

Instead of loading all the rows at once, you do it in parts. To achieve this, you spread the amount of rows loaded at once over an x amount of pages. The pro of doing it this way is that instead of loading all the rows at once, you have to load the amount of rows that fit on the page.

An example:
13,000 rows. Let us say that you can fit 20 rows on one page. 13,000/20 = 650 pages of data. For the first page you load the first 20 rows in the database. When the player goes to the second page you load another 20 rows and so forth.

EDIT: alternatively you could only load in the rows of the players that have been active in the timespan of a month or two months to lower the amount of rows that you should load.

[Type-R]

What about when i am doing lets say TOP10 for player kills and then i want to know my own place in all of the players? How could i use pages then? wouldn't i need to go through all the rows?

[AmigaBlizzard]

The maximum amount of rows in a dialog is 50 (tested it myself sometime ago).
Maximum amount of characters is 4096 characters for a dialog.

To get the top 10 players, you sort them in a descending order.

PHP код:

SELECT Vardas,Specialybe,Siandien,Dispejimai FROM vartotojai ORDER BY XP DESC LIMIT 10 


This will read only 10 players who have the highest XP.
You could have a page variable that increases by 10 every time you flip a page.

PHP код:

format(query, sizeof(query), "SELECT Vardas,Specialybe,Siandien,Dispejimai FROM vartotojai ORDER BY XP DESC LIMIT %i, 10", page); 


This would read 10 players at a time, according to the page requested.