PDA

View Full Version : How fast should be loading 600 houses?


BloodyRP
15/04/2016, 01:54 PM
Hello for everyone.
At the start I need to say u that my english is so poor and I'm sorry about that, but I rly need your help.
If I have 661 houses on gamemode and that all work with mysql r39-5 how much seconds that should get to load all info?
[More info]:
first house load in 23.14.31
[23:14:31] [DEBUG] cache_get_row_int - row: 0, field_idx: 0, connection: 1
[23:14:31] [DEBUG] CMySQLResult::GetRowData - row: '0', field: '0', data: "1"
and last house end in 23.15.19
[23:15:19] [DEBUG] cache_get_row_int - row: 660, field_idx: 15, connection: 1
[23:15:19] [DEBUG] CMySQLResult::GetRowData - row: '660', field: '15', data: "0"

So I want to ask a good pro coders. Is it normal to 48 secs load all information and save in variables? If not - what I need to do for fixing this? Thanks for every help.


mysql_tquery(server, "SELECT * FROM houses", "LoadHouses");


forward LoadHouses();
public LoadHouses()
{
new time = GetTickCount();
for(new b; b != 660; ++b)
{
House[b][hID] = cache_get_row_int(b, 0);
House[b][hEnterX] = cache_get_row_float(b, 1);
House[b][hEnterY] = cache_get_row_float(b, 2);
House[b][hEnterZ] = cache_get_row_float(b, 3);
House[b][hExitX] = cache_get_row_float(b, 4);
House[b][hExitY] = cache_get_row_float(b, 5);
House[b][hExitZ] = cache_get_row_float(b, 6);
House[b][hCarX] = cache_get_row_float(b, 7);
House[b][hCarY] = cache_get_row_float(b, 8);
House[b][hCarZ] = cache_get_row_float(b, 9);
House[b][hCarC] = cache_get_row_float(b, 10);
cache_get_row(b, 11, House[b][hOwner]);
House[b][hCost] = cache_get_row_int(b, 12);
House[b][hInt] = cache_get_row_int(b, 13);
House[b][hVW] = cache_get_row_int(b, 14);
House[b][hLock] = cache_get_row_int(b, 15);
}
printf("%d ms",GetTickCount()-time);
}
How to make that faster?

saffierr
15/04/2016, 01:55 PM
40 seconds are incredibly much...

BloodyRP
15/04/2016, 01:56 PM
40 seconds are incredibly much...

so whats wrong in my code? how to do that faster?

Konstantinos
15/04/2016, 02:41 PM
If you have 661 houses, the size of House array shouldn't be 800 but 670-675 and if needed more, changing the slot and restart the server wouldn't be a problem.

Anyway, as for your queries. Use cache_get_row/cache_get_row_* functions instead because retrieving the field's name requires more time that just a number.

I still recommended using threaded queries (mysql_pquery is used for more than 1 connection) though.

Last, more of a question. You do have an hID in your table so why you use "b" and start indexes from 1 when 0 can be used too?

BloodyRP
15/04/2016, 03:01 PM
If you have 661 houses, the size of House array shouldn't be 800 but 670-675 and if needed more, changing the slot and restart the server wouldn't be a problem.

Anyway, as for your queries. Use cache_get_row/cache_get_row_* functions instead because retrieving the field's name requires more time that just a number.

I still recommended using threaded queries (mysql_pquery is used for more than 1 connection) though.

Last, more of a question. You do have an hID in your table so why you use "b" and start indexes from 1 when 0 can be used too?

Thank you, changed to 670 size.

I tryed like u said, now I have:

House[b][hID] = cache_get_row_int(h, 0);
House[b][hEnterX] = cache_get_row_float(h,1);
House[b][hEnterY] = cache_get_row_float(h,2);
House[b][hEnterZ] = cache_get_row_float(h,3);
House[b][hExitX] = cache_get_row_float(h,4);
House[b][hExitY] = cache_get_row_float(h,5);
House[b][hExitZ] = cache_get_row_float(h,6);
House[b][hCarX] = cache_get_row_float(h,7);
House[b][hCarY] = cache_get_row_float(h,8);
House[b][hCarZ] = cache_get_row_float(h,9);
House[b][hCarZ] = cache_get_row_float(h,10);
cache_get_row(h,11,House[b][hOwner]);
House[b][hCost] = cache_get_row_int(h,12);
House[b][hInt] = cache_get_row_int(h,13);
House[b][hVW] = cache_get_row_int(h,14);
House[b][hLock] = cache_get_row_int(h,15);
and I still have 47 seconds to load all info into variables;
first house:
[17:59:48] [DEBUG] cache_get_row_int - row: 0, field_idx: 0, connection: 1
[17:59:48] [DEBUG] CMySQLResult::GetRowData - row: '0', field: '0', data: "1"

last house:
[18:00:35] [DEBUG] cache_get_row_int - row: 660, field_idx: 15, connection: 1
[18:00:35] [DEBUG] CMySQLResult::GetRowData - row: '660', field: '15', data: "0"

BloodyRP
15/04/2016, 03:47 PM
I tryed to do that with pquery:
server = mysql_connect("", "", "", "",3306,true,5);
so u see that 5 connections I have. (Dont know how much is good option)
then load like this:
for(new i = 0; i < 661; i++) mysql_pquery(server, "SELECT * FROM houses", "LoadHouses","d",i);

and public:

forward LoadHouses(inter);
public LoadHouses(inter)
{
new b = inter+1;
House[b][hID] = cache_get_row_int(inter, 0);
House[b][hEnterX] = cache_get_row_float(inter,1);
House[b][hEnterY] = cache_get_row_float(inter,2);
House[b][hEnterZ] = cache_get_row_float(inter,3);
House[b][hExitX] = cache_get_row_float(inter,4);
House[b][hExitY] = cache_get_row_float(inter,5);
House[b][hExitZ] = cache_get_row_float(inter,6);
House[b][hCarX] = cache_get_row_float(inter,7);
House[b][hCarY] = cache_get_row_float(inter,8);
House[b][hCarZ] = cache_get_row_float(inter,9);
House[b][hCarZ] = cache_get_row_float(inter,10);
cache_get_row(inter,11,House[b][hOwner]);
House[b][hCost] = cache_get_row_int(inter,12);
House[b][hInt] = cache_get_row_int(inter,13);
House[b][hVW] = cache_get_row_int(inter,14);
House[b][hLock] = cache_get_row_int(inter,15);
}
and this still needs ~45 seconds to load all info. How to fix this ?! oh god..

Konstantinos
15/04/2016, 03:49 PM
It takes so much because you load 660 times the already loaded data. Do not use loop, the query itself selects ALL the data from the table.

EDIT: That's how it should be assuming hID is set to AUTO_INCREMENT.

mysql_tquery(server, "SELECT * FROM houses", "LoadHouses","");


forward LoadHouses();
public LoadHouses()
{
new rows = clamp(cache_get_row_count(server), 0, sizeof House - 1);
for(new b; b != rows; ++b)
{
House[b][hID] = cache_get_row_int(b, 0);
House[b][hEnterX] = cache_get_row_float(b, 1);
House[b][hEnterY] = cache_get_row_float(b, 2);
House[b][hEnterZ] = cache_get_row_float(b, 3);
House[b][hExitX] = cache_get_row_float(b, 4);
House[b][hExitY] = cache_get_row_float(b, 5);
House[b][hExitZ] = cache_get_row_float(b, 6);
House[b][hCarX] = cache_get_row_float(b, 7);
House[b][hCarY] = cache_get_row_float(b, 8);
House[b][hCarZ] = cache_get_row_float(b, 9);
House[b][hCarZ] = cache_get_row_float(b, 10);
cache_get_row(b, 11, House[b][hOwner]);
House[b][hCost] = cache_get_row_int(b, 12);
House[b][hInt] = cache_get_row_int(b, 13);
House[b][hVW] = cache_get_row_int(b, 14);
House[b][hLock] = cache_get_row_int(b, 15);
House[b][hKlass] = cache_get_row_int(b, 16);
}
}

BloodyRP
15/04/2016, 03:52 PM
It takes so much because you load 660 times the already loaded data. Do not use loop, the query itself selects ALL the data from the table.

EDIT: That's how it should be assuming hID is set to AUTO_INCREMENT.

mysql_tquery(server, "SELECT * FROM houses", "LoadHouses","");


forward LoadHouses();
public LoadHouses()
{
new rows = clamp(cache_get_row_count(server), 0, sizeof House - 1);
for(new b; b != rows; ++b)
{
House[b][hID] = cache_get_row_int(b, 0);
House[b][hEnterX] = cache_get_row_float(b, 1);
House[b][hEnterY] = cache_get_row_float(b, 2);
House[b][hEnterZ] = cache_get_row_float(b, 3);
House[b][hExitX] = cache_get_row_float(b, 4);
House[b][hExitY] = cache_get_row_float(b, 5);
House[b][hExitZ] = cache_get_row_float(b, 6);
House[b][hCarX] = cache_get_row_float(b, 7);
House[b][hCarY] = cache_get_row_float(b, 8);
House[b][hCarZ] = cache_get_row_float(b, 9);
House[b][hCarZ] = cache_get_row_float(b, 10);
cache_get_row(b, 11, House[b][hOwner]);
House[b][hCost] = cache_get_row_int(b, 12);
House[b][hInt] = cache_get_row_int(b, 13);
House[b][hVW] = cache_get_row_int(b, 14);
House[b][hLock] = cache_get_row_int(b, 15);
House[b][hKlass] = cache_get_row_int(b, 16);
}
}

with Pquery, not tquery like u said it still getting 47 seconds to load all houses :D
with Tquery right the same: 47-48 seconds.

Cant understand this sheeeit

AmigaBlizzard
15/04/2016, 07:55 PM
Do you still use this:

for(new i = 0; i < 661; i++) mysql_pquery(server, "SELECT * FROM houses", "LoadHouses","d",i);


Or this:

mysql_tquery(server, "SELECT * FROM houses", "LoadHouses","");


If you still use the first (a loop to load the entire table 662 times), then yes, 47 seconds is normal. Because you're loading 662 houses 662 times, that would be 438244 houses to load.

For the second, this should be loaded within one second as you only send 1 query instead of 662 queries.
The query already loads ALL houses from the table at once, there is no need to loop through each house separately and send a query for each house.

BloodyRP
15/04/2016, 07:59 PM
Do you still use this:

for(new i = 0; i < 661; i++) mysql_pquery(server, "SELECT * FROM houses", "LoadHouses","d",i);


Or this:

mysql_tquery(server, "SELECT * FROM houses", "LoadHouses","");


If you still use the first (a loop to load the entire table 662 times), then yes, 47 seconds is normal. Because you're loading 662 houses 662 times, that would be 438244 houses to load.

For the second, this should be loaded within one second as you only send 1 query instead of 662 queries.
The query already loads ALL houses from the table at once, there is no need to loop through each house separately and send a query for each house.
yeah. I load already second type.
Like:
mysql_tquery(server, "SELECT * FROM houses", "LoadHouses");

and:

forward LoadHouses();
public LoadHouses()
{
new time = GetTickCount();
for(new b; b != 660; ++b)
{
House[b][hID] = cache_get_row_int(b, 0);
House[b][hEnterX] = cache_get_row_float(b, 1);
House[b][hEnterY] = cache_get_row_float(b, 2);
House[b][hEnterZ] = cache_get_row_float(b, 3);
House[b][hExitX] = cache_get_row_float(b, 4);
House[b][hExitY] = cache_get_row_float(b, 5);
House[b][hExitZ] = cache_get_row_float(b, 6);
House[b][hCarX] = cache_get_row_float(b, 7);
House[b][hCarY] = cache_get_row_float(b, 8);
House[b][hCarZ] = cache_get_row_float(b, 9);
House[b][hCarC] = cache_get_row_float(b, 10);
cache_get_row(b, 11, House[b][hOwner]);
House[b][hCost] = cache_get_row_int(b, 12);
House[b][hInt] = cache_get_row_int(b, 13);
House[b][hVW] = cache_get_row_int(b, 14);
House[b][hLock] = cache_get_row_int(b, 15);
}
printf("%d ms",GetTickCount()-time);
}]
this need ~45k ms = 45 seconds to load.

BloodyRP
15/04/2016, 08:27 PM
Solved guys. Thank all....
all was in mysql_log(LOG_ALL);
I disabled that and now all load in 10 ms.
Can be closed.

maddinat0r
15/04/2016, 08:33 PM
Disable debug logging.
People don't realize that debug logging considerably slows down the plugin (like 10-50x slower!). Debug logging is only intended for debug purposes, like tracing down plugin bugs.

EDIT:
Removing that call to mysql_log is a correct solution, since the standard log levels are "warning" and "error" (the most important ones and the only ones that should be used under normal circumstances).

BloodyRP
15/04/2016, 08:35 PM
Disable debug logging.
People don't realize that debug logging considerably slows down the plugin (like 10-20x slower!). Debug logging is only intended for debug purposes, like tracing down plugin bugs.

thank u, dude, but I'm already disabled that :D
never knew that debug will slow server in more than 40000 times.

introzen
16/04/2016, 10:12 AM
#define MAX_HOUSES 1000
mysql_tquery(server, "SELECT * FROM houses", "LoadHouses","");


No matter what MAX_HOUSES is, this loop will stop at the first variable reaching it's max. For example: The loop will stop if the database grabs 1200 rows, because MAX_HOUSES is 1000.

The loop will also stop at 500 if returned rows are 500 and MAX_HOUSES is 1000.

forward LoadHouses();
public LoadHouses()
{
for(new b, c=cache_get_row_count(); b < c && b < MAX_HOUSES; ++b)
{
House[b][hID] = cache_get_row_int(b, 0);
House[b][hEnterX] = cache_get_row_float(b, 1);
House[b][hEnterY] = cache_get_row_float(b, 2);
House[b][hEnterZ] = cache_get_row_float(b, 3);
House[b][hExitX] = cache_get_row_float(b, 4);
House[b][hExitY] = cache_get_row_float(b, 5);
House[b][hExitZ] = cache_get_row_float(b, 6);
House[b][hCarX] = cache_get_row_float(b, 7);
House[b][hCarY] = cache_get_row_float(b, 8);
House[b][hCarZ] = cache_get_row_float(b, 9);
House[b][hCarZ] = cache_get_row_float(b, 10);
cache_get_row(b, 11, House[b][hOwner]);
House[b][hCost] = cache_get_row_int(b, 12);
House[b][hInt] = cache_get_row_int(b, 13);
House[b][hVW] = cache_get_row_int(b, 14);
House[b][hLock] = cache_get_row_int(b, 15);
House[b][hKlass] = cache_get_row_int(b, 16);
}
}

You also define hCarZ twice:
House[b][hCarZ] = cache_get_row_float(b, 9);
House[b][hCarZ] = cache_get_row_float(b, 10);

Should the last one be rotation?

House[b][hCarA] = cache_get_row_float(b, 10);

Konstantinos
16/04/2016, 11:23 AM
I told him to reduce the size of MAX_HOUSES so it will not be a waste.

Something apparently all of us missed is this line:

cache_get_row(b, 11, House[b][hOwner]);



You have to provide the size (max_len) by yourself if you use an enum-array as destination.


so it becomes:

cache_get_row(b, 11, House[b][hOwner], server, MAX_PLAYER_NAME);

BloodyRP
16/04/2016, 08:02 PM
Thanks for all, guys.