dongxieting9623 2015-05-24 08: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 09: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条)

报告相同问题?

悬赏问题

  • ¥15 prism提示我reinstall prism 如何解决
  • ¥15 asp.core 权限控制怎么做,需要控制到每个方法
  • ¥20 while循环中OLED显示中断中的数据不正确
  • ¥15 这个视频里的stm32f4代码是怎么写的
  • ¥15 JNA调用DLL报堆栈溢出错误(0xC00000FD)
  • ¥15 请教SGeMs软件的使用
  • ¥15 自己用vb.net编写了一个dll文件,如何只给授权的用户使用这个dll文件进行打包编译,未授权用户不能进行打包编译操作?
  • ¥50 深度学习运行代码直接中断
  • ¥20 需要完整的共散射点成像代码
  • ¥15 编写vba代码实现数据录入工作