Pawn Best way to load data from database (MySQL r41)

[Pawn] Best way to load data from database (MySQL r41) - Printable Version

+ open.mp forum (https://forum.open.mp)
-- Forum: SA-MP (https://forum.open.mp/forumdisplay.php?fid=3)
--- Forum: Pawn Scripting (https://forum.open.mp/forumdisplay.php?fid=10)
--- Thread: [Pawn] Best way to load data from database (MySQL r41) (/showthread.php?tid=1428)

Best way to load data from database (MySQL r41) - shane adevaratu - 2020-12-18

What is the most efficient way to load data from the database.
For example, how could I make the function below more efficient.

Code:
function loadDealerShip( ) {

    new DS_Models = cache_num_rows( ), x, result;

    if( DS_Models == 0 ) return print( "Loaded models: no rows detected" );

    for( new i, j = DS_Models; i != j;  ) {

        cache_get_value_name_int( i, "ID", result ), x = result, dsVariable[ x ][ dsID ] = x;

        cache_get_value_name_int( i, "Model", dsVariable[ x ][ dsModel ] );

        cache_get_value_name_int( i, "Stock", dsVariable[ x ][ dsStock ] );

        cache_get_value_name_int( i, "Speed", dsVariable[ x ][ dsSpeed ] );

        cache_get_value_name_int( i, "Price", dsVariable[ x ][ dsPrice ] );

        cache_get_value_name_int( i, "Type", dsVariable[ x ][ dsType ] );

        cache_get_value_name( i, "Car", dsVariable[ x ][ dsName ] );

    } printf( "Loaded models: %d", DS_Models );

return true; }

The 'ID'?starts at 1 and reaches over 100.

RE: Best way to load data from database (MySQL r41) - Pinch - 2020-12-18

Why do you have x and result?

RE: Best way to load data from database (MySQL r41) - shane adevaratu - 2020-12-18

Good question.. Is there something else I can do to improve it?

Code:
function loadDealerShip( ) {

? ? new DS_Models = cache_num_rows( );

? ? if( DS_Models == 0 ) return print( "Loaded models: no rows detected" );

? ? for( new i, j = DS_Models; i != j;  ) {

? ? ? ? cache_get_value_name_int( i, "ID", dsVariable[ i ][ dsID ] );

? ? ? ? cache_get_value_name_int( i, "Model", dsVariable[ i ][ dsModel ] );

? ? ? ? cache_get_value_name_int( i, "Stock", dsVariable[ i ][ dsStock ] );

? ? ? ? cache_get_value_name_int( i, "Speed", dsVariable[ i ][ dsSpeed ] );

? ? ? ? cache_get_value_name_int( i, "Price", dsVariable[ i ][ dsPrice ] );

? ? ? ? cache_get_value_name_int( i, "Type", dsVariable[ i ][ dsType ] );

? ? ? ? cache_get_value_name( i, "Car", dsVariable[ i ][ dsName ] );

? ? } printf( "Loaded models: %d", DS_Models );

return true; }

RE: Best way to load data from database (MySQL r41) - Pinch - 2020-12-18

Probably not, either way you shouldn't worry to much about 1-time reads, worry about the peak usage queries instead (like writing and reading user data).

RE: Best way to load data from database (MySQL r41) - Markski - 2020-12-19

For most intents and purposes that is probably fine. High frequency database operations is what you should worry about as stated by Pinch, and in those cases the best way to optimize is by decreasing frequency (caching).

RE: Best way to load data from database (MySQL r41) - shane adevaratu - 2020-12-20

It is more efficient to use this method for most rescues

Code:
//global

new gQuery[ 256 ];

//local

gQuery[ 0 ] = EOS;

mysql_format( SQL, gQuery, sizeof gQuery, "UPDATE `players` SET `Hours`='%0.3f' WHERE `ID`='%d';", playerVariable[ playerid ][ pHours ], playerVariable[ playerid ][ pSQL ] );

mysql_tquery( SQL, gQuery, "", "" );

compared to this?

Code:
//local

new Query[ 70 ];

mysql_format( SQL, Query, sizeof Query, "UPDATE `players` SET `Hours`='%0.3f' WHERE `ID`='%d';", playerVariable[ playerid ][ pHours ], playerVariable[ playerid ][ pSQL ] );

mysql_tquery( SQL, Query, "", "" );

RE: Best way to load data from database (MySQL r41) - Pinch - 2020-12-20

Local, always