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

shane adevaratu · (This post was last modified: 2020-12-18, 11:01 AM by shane adevaratu.)

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; ++i ) {

        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.

Pinch · 2020-12-18, 11:06 AM

Why do you have x and result?

shane adevaratu · 2020-12-18, 11:26 AM

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; ++i ) {

        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; }

Pinch · (This post was last modified: 2020-12-19, 09:48 AM by Pinch.)

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).

**Markski** · 2020-12-19, 03:08 AM

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).

shane adevaratu · 2020-12-20, 11:39 AM

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, "", "" );

Pinch · 2020-12-20, 01:49 PM

Local, always