MySql (optimization) - Printable Version

MySql (optimization) - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Help Archive (https://sampforum.blast.hk/forumdisplay.php?fid=89)
+---- Thread: MySql (optimization) (/showthread.php?tid=212852)

MySql (optimization) - DRIFT_HUNTER - 18.01.2011

Simple question

Is it better to use big query's and fetch

pawn Код:

new Query[200]; //Creates the Query string and the Pname
    format(Query, sizeof(Query), "SELECT * FROM `users` WHERE `User` = '%s'", ESCpName);
    mysql_query(Query);

And now fetch row (it will be very big and its very hard to script it)
Or use it like

pawn Код:

stock GetFromAccount(username[], obtaining[], holdingvar[])
{
    format(Query, sizeof(Query), "SELECT `%s` FROM `Accounts` WHERE `Username` = '%s'", obtaining, username);
    mysql_query(Query);
    mysql_store_result();

    if(mysql_fetch_row(holdingvar) == 1)
    {
        mysql_free_result();
    }
    return 1;
}

These one is easy way same like wirh DINI or DJSON but i think it will be slower than fetch row....

So what you say? what is better for server speed?

Re: MySql (optimization) - Grim_ - 18.01.2011

It would be better to get all the information from the database at once.

You brought up a good point, it's like Dini vs djSon - Getting the data from the source all at once, or separately, one at a time. Since we know the answer as to which is best vs them, we know which is best to your question (thought don't always apply that logic).

Re: MySql (optimization) - DRIFT_HUNTER - 18.01.2011

I afraid of lag...server will need to deal with very big cell's size's...and its using much memory....
But i guess i will gain at speed of o just get all info and then server fetch it...

Re: MySql (optimization) - Grim_ - 18.01.2011

You would be surprised how much data you can fetch at once. I had a database retrieving over 1000 fields for each player at the beginning of each login (I had vehicles, weapons, skins, and other things saved for each player) and while I had about 10 players online, I still did not detect any lag what so ever.

People seem to under estimate the power behind everything. For example, you may think writing a value to a file 100000 times would be fairly hard for a computer. However, it's the exact opposite. With the following code, it took only .4 seconds to write all 100000 values to the file:

pawn Код:

public OnFilterScriptInit( )
{
   new 
      File: open = fopen( "test.txt", io_write );
   ;

   if( open )
   {
      for( new i = 0; i < 100000; i++ )
      {
         fwrite( open, "2040\r\n" );
      }
   }
   fclose( open );
   return 1;
}

And with that, only a CPU spike of 3% was seen.

Re: MySql (optimization) - Not available - 18.01.2011

Load all the data and store it in to variables whenever a player logs in.

Re: MySql (optimization) - Calgon - 18.01.2011

It's better to get all data at once, the only reason I created that stock function was so I could use it for additional items during game run-time and I didn't even end up using it in the end, then RealCop228 decided to copy it from my MySQL script release.