Raised This Month: $12 Target: $400
 3% 

[SQLite] Combining Rows like nVault


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
WAR3DM
Senior Member
Join Date: Mar 2016
Old 09-06-2016 , 21:17   [SQLite] Combining Rows like nVault
Reply With Quote #1

Hello! I'll keep things short & simple.

I'm stuck with 500k rows in 1 table. This causes extreme lag anytime someone connects to the server.

The Current Structure:



What I'm Trying to Accomplish:



Create:
PHP Code:
    "CREATE TABLE `skills` ( `player_id` int(8) NOT NULL default '0', `skill_id` int(8) NOT NULL default '0', `skill_level` int(8) default NULL, PRIMARY KEY  (`player_id`,`skill_id`) );"

Load:
PHP Code:
// Select the right Table
    
formatszQuerycharsmaxszQuery ), "SELECT `skill_id`, `skill_level` FROM `skills` WHERE ( `player_id` = '%d' );"p_data[id][PLAYER_UNIQUEID] );
    
query SQL_PrepareQueryg_DBConnszQuery );

// While we have a result!
    
new iSkillID 0;
    while ( 
SQL_MoreResultsquery ) )
    {
        
iSkillID SQL_ReadResultquery);
        
p_data_skill[id][iSkillID] = SQL_ReadResultquery);

        
SQL_NextRowquery );
    } 

Save:
PHP Code:
// Now we need to save the skill levels!
for ( new iSkillID 0iSkillID MAX_SKILLSiSkillID++ )
{
    
formatexszQuery511"REPLACE INTO `skills` ( `player_id` , `skill_id` , `skill_level` ) VALUES ( '%d', '%d', '%d' );"iUniqueIDiSkillIDp_data_skill[id][iSkillID] );
    
query SQL_PrepareQueryg_DBConnszQuery );


How would I loop all skill_levels from a player_id into 1 field like shown in the 2nd image? Any help is GREATLY appreciated!

Last edited by WAR3DM; 09-07-2016 at 19:57.
WAR3DM is offline
PlayStation
Junior Member
Join Date: Nov 2015
Old 09-07-2016 , 19:37   Re: [SQLite] Extreme Lag after 50,000 players!
Reply With Quote #2

I would not recommend to mess up with your database and besides it's against normalization.
You could switch to nVault or separate players in different tables somehow.
PlayStation is offline
WAR3DM
Senior Member
Join Date: Mar 2016
Old 09-07-2016 , 19:54   Re: [SQLite] Extreme Lag after 50,000 players!
Reply With Quote #3

Quote:
Originally Posted by PlayStation View Post
I would not recommend to mess up with your database and besides it's against normalization.
You could switch to nVault or separate players in different tables somehow.
Thanks for the response!

I'd like to switch to nVault but it would probably be difficult.

If I were to go against normalization, would you know how it's done?
WAR3DM is offline
PlayStation
Junior Member
Join Date: Nov 2015
Old 09-07-2016 , 20:15   Re: [SQLite] Extreme Lag after 50,000 players!
Reply With Quote #4

Quote:
Originally Posted by WAR3DM View Post
If I were to go against normalization, would you know how it's done?
Well you will have to work with a string, means you just need to use always same skill order and to read data you probably need something like strbreak
PlayStation is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 09-07-2016 , 22:26   Re: [SQLite] Combining Rows like nVault
Reply With Quote #5

Why do you have so many rows? Each row should represent each unique player. Based on 500k rows, I doubt this is the case unless you have an awesome server. Can you explain how data is being stored for each player in your database? What is uniqueID? You should be storing all player data using steamid as the primary key. This would mean there would be only 1 row for each player in the database and your row count should never reach a point where it causes lag. If it does, you should timestamp all data and purge it frequently to delete stuff that is X days old.

I've been a big fan of nVault since I started AMX-X scripting but after learning SQL I've been a huge fan of it. nVault is great if you need a simple key -> value lookup. But if you need to do any type of querying (top 15, rank, etc), SQL is the way to go.

I can help you get things fixed, but I need to understand better what you are trying to accomplish. Please don't say 'I want to save abc 123 sss 555 fff for each player'. Explain what you want your end result to be and I will provide an efficient way to do it.
__________________

Last edited by Bugsy; 09-07-2016 at 22:36.
Bugsy is offline
WAR3DM
Senior Member
Join Date: Mar 2016
Old 09-07-2016 , 22:50   Re: [SQLite] Combining Rows like nVault
Reply With Quote #6

Each player (player_id) gets 40 separate skills (skill_id), 1 skill for each row... so the number of rows skyrocket into the hundreds of thousands.


My end result would be:
  • 1 row per player (player_id)
  • Under (skill_level), the 40 skills should be listed horizontally. (separated by a space)
  • Each skill_level number counts as the next (skill_id) in the list. (0, 1, 2, 3.. 39)


For example:


skill_id 0 = 11
skill_id 1 = 22
skill_id 2 = 33

So when I query for the 3rd skill under (skill_level) using strbreak, it will select 33.

Last edited by WAR3DM; 09-07-2016 at 23:17.
WAR3DM is offline
Bugsy
AMX Mod X Moderator
Join Date: Feb 2005
Location: NJ, USA
Old 09-07-2016 , 23:16   Re: [SQLite] Combining Rows like nVault
Reply With Quote #7

There are 40 skills that each player can have and you need to store data for? Can you explain your plugin?
__________________
Bugsy is offline
WAR3DM
Senior Member
Join Date: Mar 2016
Old 09-07-2016 , 23:24   Re: [SQLite] Combining Rows like nVault
Reply With Quote #8

Quote:
Originally Posted by Bugsy View Post
There are 40 skills that each player can have and you need to store data for? Can you explain your plugin?
The plugin is a heavily edited UWC3 Next Generation (RPG Mod)



Here is the SQLite Code: (You can ignore everything not related to Skills)
PHP Code:
// Uwc3ng SQLITE functions

#define TOTAL_SQLITE_TABLES 4

new const szTablesSQLite[TOTAL_SQLITE_TABLES][] = 
{
    
"CREATE TABLE `uwc3ng_player` ( `player_id` INTEGER PRIMARY KEY AUTOINCREMENT, `player_steamid` varchar(25) NOT NULL default '', `player_ip` varchar(20) NOT NULL default '', `player_name` varchar(35) NOT NULL default '', `player_xp` int(16) default NULL );",
    
"CREATE TABLE `uwc3ng_skill` ( `player_id` int(8) NOT NULL default '0', `skill_id` int(8) NOT NULL default '0', `skill_level` int(8) default NULL, PRIMARY KEY  (`player_id`,`skill_id`) );",
    
"CREATE TABLE `uwc3ng_attrib` ( `player_id` int(8) NOT NULL default '0', `attrib_id` int(8) NOT NULL default '0', `attrib_level` int(8) NOT NULL default '0', PRIMARY KEY  (`player_id`,`attrib_id`) );",
    
"CREATE TABLE `uwc3ng_resist` ( `player_id` int(8) NOT NULL default '0', `resist_id` int(8) NOT NULL default '0', `resist_level` int(8) NOT NULL default '0', PRIMARY KEY  (`player_id`,`resist_id`) );"
};

new const 
szTableNames[TOTAL_SQLITE_TABLES][] = 
{
    
"uwc3ng_player",
    
"uwc3ng_skill",
    
"uwc3ng_attrib",
    
"uwc3ng_resist"
};

// Initiate the connection to the SQLite database
public SQLITE_Init()
{
    new 
szError[256], iErrNum;

    
// Set up the tuple that will be used for threading
    
g_DBTuple SQL_MakeDbTuple"""""""uwc3ng" );

    
// Attempt to connect
    
g_DBConn SQL_Connectg_DBTupleiErrNumszError255 );

    if ( !
g_DBConn )
    {
        
log_amx"[SQLITE] Database Connection Failed: [%d] %s"iErrNumszError );

        return;
    }


    
server_print"%s SQLite database connection successful"MOD_NAME );

    new 
Handle:query;

    
// Create the default tables if we need to
    
for ( new 0TOTAL_SQLITE_TABLESi++ )
    {
        
query SQL_PrepareQueryg_DBConnszTablesSQLite[i] );

        if ( !
sqlite_TableExistsg_DBConnszTableNames[i] ) )
        {
            if ( !
SQL_Executequery ) )
            {
                
SQLITE_ErrorqueryszTablesSQLite[i], );

                return;
            }
        }

        
SQL_FreeHandlequery );
    }

    
/*
        These probably should be subject to a CVAR
        Lets fine tune the database:
            "synchronous = NORMAL"    - Put back the 2.x behaviour (faster than the defalt
                          for 3.x)
            "synchronous = OFF"    - Way faster, but it might get corrupted data if a
                          server os system crash occurs
            "integrity_check"    - well it's what it says, we do have to check the
                          value it returns since it's important
        PRAGMA commands don't return anything so no need to check the result of the query
    */    

    
query SQL_PrepareQueryg_DBConn"PRAGMA integrity_check" );
    
    if ( !
SQL_Executequery ) )
    {
        
SQLITE_Errorquery"PRAGMA integrity_check");

        return;
    }
    
    
// Get the integrity check value
    
new szIntegrityCheck[64];
    if ( 
SQL_NumResultsquery ) > )
    {
        
SQL_ReadResultquery0szIntegrityCheckcharsmaxszIntegrityCheck ) );
    }

    
// Free the result
    
SQL_FreeHandlequery );

    
// Check to make sure the integrity check passed
    
if ( !equali(szIntegrityCheck"ok") )
    {
        
// Should we disable saving here?
        
log_amx"[SQLITE] SQL Lite integrity check failed, disabling saving XP." );

        
set_pcvar_numCVAR_uwc3ng_save_xp);

        return;
    }
    
    
// Do some synchronous crap
    
new szQuery[128];
    
formatszQuerycharsmaxszQuery ), "PRAGMA synchronous = %d"SQLITE_SYNC_OFF );
    
query SQL_PrepareQueryg_DBConnszQuery );

    if ( !
SQL_Executequery ) )
    {
        
SQLITE_ErrorqueryszQuery);

        return;
    }
    
    
// Free the last handle
    
SQL_FreeHandlequery );
}

// Close the SQLite connection
public SQLITE_Close()
{
    if ( 
g_DBTuple )
    {
        
SQL_FreeHandleg_DBTuple );
    }

    if ( 
g_DBConn )
    {
        
SQL_FreeHandleg_DBConn );
    }
}

public 
SQLITE_FetchUniqueIDid )
{
    
// Make sure our connection is working
    
if ( !SQLITE_Connection_Available() )
    {
        return;
    }

    new 
szKey[66], szKeyName[32];
    
Save_GetKeyidszKeycharsmaxszKey ) );
    
Save_GetKeyNameszKeyNamecharsmaxszKeyName ) );

    new 
szQuery[512];
    
formatszQuerycharsmaxszQuery ), "SELECT `player_id` FROM `uwc3ng_player` WHERE `%s` = '%s';"szKeyNameszKey );
    new 
Handle:query SQL_PrepareQueryg_DBConnszQuery );

    if ( !
SQL_Executequery ) )
    {
        
SQLITE_ErrorqueryszQuery);

        return;
    }
    
    
// If no rows we need to insert!
    
if ( SQL_NumResultsquery ) == )
    {
        
// Free the last handle!
        
SQL_FreeHandlequery );

        
// Insert this player!
        
formatszQuerycharsmaxszQuery ), "INSERT INTO `uwc3ng_player` ( `player_id` , `%s` ) VALUES ( NULL , '%s' );"szKeyNameszKey );
        
query SQL_PrepareQueryg_DBConnszQuery );

        if ( !
SQL_Executequery ) )
        {
            
SQLITE_ErrorqueryszQuery);

            return;
        }

        
p_data[id][PLAYER_UNIQUEID] = SQL_GetInsertIdquery );
    }

    
// They have been here before - store their ID
    
else
    {
        
p_data[id][PLAYER_UNIQUEID] = SQL_ReadResultquery);
    }

    
// Free the last handle!
    
SQL_FreeHandlequery );
}

public 
SQLITE_Saveid )
{
    
// Make sure our connection is working
    
if ( !SQLITE_Connection_Available() )
    {
        return;
    }
    
    
// Get the UniqueID
    
new iUniqueID SAVE_GetUniqueIDid );
    
    new 
szKey[66], szKeyName[32];
    
Save_GetKeyidszKeycharsmaxszKey ) );
    
Save_GetKeyNameszKeyNamecharsmaxszKeyName ) );

    
// Save the user's XP!
    
new szQuery[512];
    
formatexszQuerycharsmaxszQuery ), "REPLACE INTO `uwc3ng_player` ( `player_id` , `%s` , `player_xp` ) VALUES ( '%d', '%s', '%d');"szKeyNameiUniqueIDszKeyp_data[id][PLAYER_XP] );
    new 
Handle:query SQL_PrepareQueryg_DBConnszQuery );

    if ( !
SQL_Executequery ) )
    {
        
SQLITE_ErrorqueryszQuery);

        return;
    }

    
// Now we need to save the skill levels!
    
for ( new iSkillID 0iSkillID MAX_SKILLSiSkillID++ )
    {
        
formatexszQuery511"REPLACE INTO `uwc3ng_skill` ( `player_id` , `skill_id` , `skill_level` ) VALUES ( '%d', '%d', '%d' );"iUniqueIDiSkillIDp_data_skill[id][iSkillID] );
        
query SQL_PrepareQueryg_DBConnszQuery );

        if ( !
SQL_Executequery ) )
        {
            
SQLITE_ErrorqueryszQuery);

            return;
        }
    }
    
    
// Now we need to save the attrib levels!
    
for ( new iAttribID 0iAttribID MAX_ATTRIBSiAttribID++ )
    {
        
formatexszQuery511"REPLACE INTO `uwc3ng_attrib` ( `player_id` , `attrib_id` , `attrib_level` ) VALUES ( '%d', '%d', '%d' );"iUniqueIDiAttribIDp_data_attrib[id][iAttribID] );
        
query SQL_PrepareQueryg_DBConnszQuery );

        if ( !
SQL_Executequery ) )
        {
            
SQLITE_ErrorqueryszQuery);

            return;
        }
    }
    
    
// Now we need to save the resist levels!
    
for ( new iResistID 0iResistID MAX_RESISTiResistID++ )
    {
        
formatexszQuery511"REPLACE INTO `uwc3ng_resist` ( `player_id` , `resist_id` , `resist_level` ) VALUES ( '%d', '%d', '%d' );"iUniqueIDiResistIDp_data_resist[id][iResistID] );
        
query SQL_PrepareQueryg_DBConnszQuery );

        if ( !
SQL_Executequery ) )
        {
            
SQLITE_ErrorqueryszQuery);

            return;
        }
    }
    
    return;
}

public 
SQLITE_GetAllXPid )
{
    
// Make sure our connection is working
    
if ( !SQLITE_Connection_Available() )
    {
        return;
    }

    
// Then we have a problem and cannot retreive the user's XP
    
if ( p_data[id][PLAYER_UNIQUEID] <= )
    {
        
client_printidprint_chat"%s Unable to retreive your XP from the database"MOD_NAME );

        
log_amx"[ERROR] Unable to retreive user's Unique ID" );

        return;
    }

    new 
szQuery[256];
    
format(szQuerycharsmaxszQuery ), "SELECT `player_xp` FROM `uwc3ng_player` WHERE ( `player_id` = '%d' );"p_data[id][PLAYER_UNIQUEID] );
    new 
Handle:query SQL_PrepareQueryg_DBConnszQuery );

    if ( !
SQL_Executequery ) )
    {
        
SQLITE_ErrorqueryszQuery);

        return;
    }

    
// Set last saved XP to 0
    
p_data[id][PLAYER_XP] = 0;

    
// Read the XPs from the table
    
p_data[id][PLAYER_XP] = SQL_ReadResultquery);
        
    
// Free the handle
    
SQL_FreeHandlequery );
    
    
// Select the right Table
    
formatszQuerycharsmaxszQuery ), "SELECT `skill_id`, `skill_level` FROM `uwc3ng_skill` WHERE ( `player_id` = '%d' );"p_data[id][PLAYER_UNIQUEID] );
    
query SQL_PrepareQueryg_DBConnszQuery );

    
// Check if the DB ready
    
if ( !SQL_Executequery ) )
    {
        
SQLITE_Error queryszQuery ,);

        return;
    }
    
    
// While we have a result!
    
new iSkillID 0;
    while ( 
SQL_MoreResultsquery ) )
    {
        
iSkillID SQL_ReadResultquery);
        
p_data_skill[id][iSkillID] = SQL_ReadResultquery);

        
SQL_NextRowquery );
    }
    
    
// Close the connection
    
SQL_FreeHandlequery );
    
    
// Select the right Table
    
formatszQuerycharsmaxszQuery ), "SELECT `attrib_id`, `attrib_level` FROM `uwc3ng_attrib` WHERE ( `player_id` = '%d' );"p_data[id][PLAYER_UNIQUEID] );
    
query SQL_PrepareQueryg_DBConnszQuery );

    
// Check if the DB ready
    
if ( !SQL_Executequery ) )
    {
        
SQLITE_Error queryszQuery);

        return;
    }
    
    
// While we have a result!
    
new iAttribID 0;
    while ( 
SQL_MoreResultsquery ) )
    {
        
iAttribID SQL_ReadResultquery);
        
p_data_attrib[id][iAttribID] = SQL_ReadResultquery);

        
SQL_NextRowquery );
    }
    
    
// Close the connection
    
SQL_FreeHandlequery );
    
    
// Select the right Table
    
formatszQuerycharsmaxszQuery ), "SELECT `resist_id`, `resist_level` FROM `uwc3ng_resist` WHERE ( `player_id` = '%d' );"p_data[id][PLAYER_UNIQUEID] );
    
query SQL_PrepareQueryg_DBConnszQuery );

    
// Check if the DB ready
    
if ( !SQL_Executequery ) )
    {
        
SQLITE_Error queryszQuery);

        return;
    }
    
    
// While we have a result!
    
new iResistID 0;
    while ( 
SQL_MoreResultsquery ) )
    {
        
iResistID SQL_ReadResultquery);
        
p_data_resist[id][iResistID] = SQL_ReadResultquery);

        
SQL_NextRowquery );
    }
    
    
// Close the connection
    
SQL_FreeHandlequery );

    return;
}

// Verifies that the database connection is ok
bool:SQLITE_Connection_Available()
{
    if ( !
g_DBConn )
    {
        return 
false;
    }

    return 
true;
}

// The id should be a unique number, so we know what function called it (useful for debugging)
SQLITE_ErrorHandle:queryszQuery[], id )
{
    new 
szError[256];
    new 
iErrNum SQL_QueryErrorqueryszError255 );

    
log_amx"[SQLITE] Error in querying database, location: %d"id );
    
log_amx"[SQLITE] Message: %s (%d)"szErroriErrNum );
    
log_amx"[SQLITE] Query statement: %s "szQuery );

    
// Free the handle
    
SQL_FreeHandlequery );
}
/* AMXX-Studio Notes - DO NOT MODIFY BELOW HERE
*{\\ rtf1\\ ansi\\ deff0{\\ fonttbl{\\ f0\\ fnil Tahoma;}}\n\\ viewkind4\\ uc1\\ pard\\ lang1031\\ f0\\ fs16 \n\\ par }
*/ 

Last edited by WAR3DM; 09-08-2016 at 01:02.
WAR3DM is offline
PlayStation
Junior Member
Join Date: Nov 2015
Old 09-08-2016 , 04:04   Re: [SQLite] Combining Rows like nVault
Reply With Quote #9

you need to understand, that the lagging comes from querying process during gameplay, which affects fps and if every players queries 500k rows no wonder it lags

if you would separate players for example by nickname you will have 10-30k in each table maybe, also i don't think all players are active, you could speed up your SELECT'ion if you just check which players are active
PlayStation is offline
WAR3DM
Senior Member
Join Date: Mar 2016
Old 09-08-2016 , 04:26   Re: [SQLite] Combining Rows like nVault
Reply With Quote #10

Quote:
Originally Posted by PlayStation View Post
you need to understand, that the lagging comes from querying process during gameplay, which affects fps and if every players queries 500k rows no wonder it lags

if you would separate players for example by nickname you will have 10-30k in each table maybe, also i don't think all players are active, you could speed up your SELECT'ion if you just check which players are active
Yes, I know the lag is from querying 500k rows during gameplay.

Yes, I'm trying to separate rows by Steam_ID. (player_id)



Here is some nVault code that pulls it off
---------------------------------------------------------


Save:
PHP Code:
    formatex(vaultkeycharsmax(vaultkey), "%s-quests"SteamID)
    
    
iPos num_to_str(TotalQuests[id], vaultdatacharsmax(vaultdata))
    
vaultdata[iPos++] = ' '
    
    
for(new iiQuestCounti++) {
        
iPos += formatex(vaultdata[iPos], charsmax(vaultdata) - iPos"%i "iQuestLevel[id][i])
    }
    
nvault_set(gVaultvaultkeyvaultdata
Load:
PHP Code:
    formatex(vaultkeycharsmax(vaultkey), "%s-quests"SteamID
    
    
nvault_get(gVaultvaultkeyvaultdatacharsmax(vaultdata))
    
    
argbreak(vaultdataszValcharsmax(szVal), vaultdatacharsmax(vaultdata ))
    
    
TotalQuests[id] = str_to_num(szVal)
    
    for(new 
iiQuestCounti++) {
        
argbreak(vaultdataszVal charsmax(szVal) , vaultdata charsmax(vaultdata)) 
        
iQuestLevel[id][i] = str_to_num(szVal)
    } 


Now compare it to my current SQL
---------------------------------------------------------

Save:
PHP Code:
// Now we need to save the skill levels!
for ( new iSkillID 0iSkillID MAX_SKILLSiSkillID++ )
{
    
formatexszQuery511"REPLACE INTO `skills` ( `player_id` , `skill_id` , `skill_level` ) VALUES ( '%d', '%d', '%d' );"iUniqueIDiSkillIDp_data_skill[id][iSkillID] );
    
query SQL_PrepareQueryg_DBConnszQuery );

Load:
PHP Code:
// Select the right Table
    
formatszQuerycharsmaxszQuery ), "SELECT `skill_id`, `skill_level` FROM `skills` WHERE ( `player_id` = '%d' );"p_data[id][PLAYER_UNIQUEID] );
    
query SQL_PrepareQueryg_DBConnszQuery );

// While we have a result!
    
new iSkillID 0;
    while ( 
SQL_MoreResultsquery ) )
    {
        
iSkillID SQL_ReadResultquery);
        
p_data_skill[id][iSkillID] = SQL_ReadResultquery);

        
SQL_NextRowquery );
    } 

Last edited by WAR3DM; 09-08-2016 at 04:36.
WAR3DM is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 06:39.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode