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

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

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

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

There are two issues:

1. The first version only transfers the selected rows from the database to PHP, the second one transfers all the rows. Copying all that data takes time.

2. The first version can make use of indexes to speed up finding the selected rows. The second version reads through all the rows.

Also, second version need to iterate (foreach) through all elements. Which takes more time.

add CON_CATEGORY column to index of table