dongxieting9623 2015-05-24 00:27
浏览 36
已采纳

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

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

展开全部

  • 写回答

4条回答 默认 最新

  • dpjs2005 2015-06-01 01:52
    关注

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

    展开全部

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)
编辑
预览

报告相同问题?

手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部