从数据库或公共变量的大数据中选择

Question

I have table content with 100,000 records I call function getNameProcess around 200 times

PHP Code with select from database

function getNameProcess($id)
{    
    $time1=microtime(true);
    $baseClass = new MsDatabase();

    $query = "select CON_VALUE,CON_ID,CON_CATEGORY from content where CON_ID=$id and CON_VALUE<>'' and CON_CATEGORY='PRO_TITLE'";
    $res= $baseClass->query($query,WF_WORKFLOW_DB_NAME);

    $time2=microtime(true);
    $timeTotal=($time2-$time1);
    echo $timeTotal;  
    return $res[0]["CON_VALUE"];        
}

PHP Code with select from public variable

$contentTable=array();
function getNameProcess($id)
{ 
    $time1=microtime(true);
    $baseClass = new MsDatabase();
    if(empty($GLOBALS['contentTable']))
    { 
        $query = "select CON_VALUE,CON_ID,CON_CATEGORY from content ";      
        $GLOBALS['contentTable']= $baseClass->query($query,WF_WORKFLOW_DB_NAME_MARKAZE);        
    }       
    foreach($GLOBALS['contentTable'] as $R)
    {
        if($R['CON_ID']==$id && $R['CON_VALUE']!='' && $R['CON_CATEGORY']=='PRO_TITLE' )
        {
            $time2=microtime(true);
            $timeTotal=($time2-$time1);
            echo $timeTotal;  

        return $R["CON_VALUE"];
       }
    }
     return 0;  
}

When using database for get process name $totalTime is 1.2 second and when use public variable totalTime is 3.5 second?

Why I use public variable $totalTime is greater than when use database?

How to reduce $totalTime?

thanks

Answer 1

To speed things up:

1. Create and index for CON_CATEGORY + CON_ID (or CON_ID + CON_CATEGORY depending on other queries)

2. Change your lookup code:

New code:

$contentTable=array();
function getNameProcess($id)
{
    $time1=microtime(true);
    if(empty($GLOBALS['contentTable']))
    {
        $baseClass = new MsDatabase();
        $query = "select CON_VALUE,CON_ID from content WHERE CON_CATEGORY = 'PRO_TITLE'";
        $result= $baseClass->query($query,WF_WORKFLOW_DB_NAME_MARKAZE);

        $GLOBALS['contentTable'] = array();
        foreach($result as $R) {
            if ($R['CON_VALUE'] != '') $GLOBALS['contentTable'][$R['CON_ID']] = $R['CON_VALUE'];
        }
    }

    $retval = 0;
    if (isset($GLOBALS['contentTable'][$id])) {
        $retval = $GLOBALS['contentTable'][$id];
    }

    $time2=microtime(true);
    $timeTotal=($time2-$time1);
    echo $timeTotal;

    return $retval;
}

1. A third way to optimize is combining the 2 methods. First it is important to have some statistics: How many times is the same id needed? If for example an ID is requested 20 times on average, you would only need 10 queries instead of 200.

Like:

$contentTable=array();
function getNameProcess($id)
{
    $time1=microtime(true);

    if (isset($GLOBALS['contentTable'][$id])) {
        $retval = $GLOBALS['contentTable'][$id];
    }
    else {
        $baseClass = new MsDatabase();
        $query = "select CON_VALUE,CON_ID,CON_CATEGORY from content where CON_ID=$id and CON_CATEGORY='PRO_TITLE'";
        $res= $baseClass->query($query,WF_WORKFLOW_DB_NAME);

        $retval = ($res[0]['CON_VALUE'] == '' ? 0 : $res[0]['CON_VALUE']);
        $GLOBALS['contentTable'][$res[0]['CON_ID']] = $retval;
    }

    $time2=microtime(true);
    $timeTotal=($time2-$time1);
    echo $timeTotal;

    return $retval;
}