Does performance improve by limiting the columns fetched?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Does performance improve by limiting the columns fetched?

Post by novice4eva »

Hi there friends, well this is not a problem or anything but just an information that i am keen on knowing. In our current working environment, we make a single function to list all the data from a table. Now few of these tables have over 100 columns while some have blog data in them. Currently If i have to fetch data from just few columns from a particular table, i just use the function to retrieve all the column values. Now it seems obvious that fetching just say 2 column values must be faster than fetching all, but some of my fellow mates think that the overhead is really really really LESS... none the less i made a function (with regex and explode and well it's all messy) which accepts an array which contains column names or aliases to recreate a SQL which fetches only the desired columns. The thing is I need your opinion on the function i made too, rather than going through explodes and regex would it be better option to just put the actual query inside another select?? I mean like this say: i have a SQL like this
SELECT cloumn1,columns2,columns3........,columnN FROM some_table WHERE condition
Now is it better to for my function to do:
SELECT column1,columns3 FROM (SELECT cloumn1,columns2,columns3........,columnN FROM some_table WHERE condition)
Or is it better for my function to go through explodes and regex to generate SQL like this:
SELECT cloumn1,columns3........,columnN FROM some_table WHERE condition

I would be thankful for any suggestions .. really..long live devnetwork :D
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Does performance improve by limiting the columns fetched?

Post by Christopher »

I am confused. Why not just do:

Code: Select all

SELECT cloumn1,columns3 FROM some_table WHERE condition
(#10850)
User avatar
novice4eva
Forum Contributor
Posts: 327
Joined: Thu Mar 29, 2007 3:48 am
Location: Nepal

Re: Does performance improve by limiting the columns fetched?

Post by novice4eva »

Well our working standard is like this. Say i have a table hr_employee which contains all the information of an employee, then we would have a function like this:
/*
$filterArray = array('emp_code','first_name');
*/
function listHrEmployee($filterArray)
{
$Sql = "
SELECT
emp_code
,first_name
,middle_name
,.......
,columnN
FROM
hr_employee/* and some other tables too with join */
WHERE
condition";
$Sql = $this->parseSqlString($Sql,$filterArray);
return $this->dbQueryReturn($Sql);
}
My parser function is this(this is not a perfect function .... you wouldn't like to go through the code i guess :mrgreen: but then also):

Code: Select all

 
    function parseSqlString($queryStr=NULL,$filter=NULL)
    {
        if(!(is_array($filter) && count($filter)>0))
            return $queryStr;
        $returnSql = 'SELECT ';
        $selectColumns = array();
        $queryStr = trim(strtoupper($queryStr));
        $selectPos = substr($queryStr,0,6)=='SELECT'?true:false;
        if ($selectPos) 
        {
            $pattern = "/[[:space:]]FROM[[:space:]]/";
            $fromPos = strripos(preg_replace($pattern,'****FROM****',$queryStr,1),'****FROM****');
            $afterFrom = substr($queryStr,$fromPos);
 
            if (!($fromPos === false)) 
            {
                $columnStr = trim(substr($queryStr,6,$fromPos-5));
                $columns = explode(",",$columnStr);
                foreach($filter as $shouldSelect)
                {
                    foreach($columns as $chkSelect)
                    {
                        //CHECK ON ALIASES 
                        $tmpChkSelect = explode(' ',$chkSelect);
                        $counter = count($tmpChkSelect);
                        for($i=$counter;$i>=0;$i--)
                        {
                            if(trim($tmpChkSelect[$i])!='')
                            {
                                $chkValue = $tmpChkSelect[$i];  
                                break;
                            }
                        }
                        
                        $chkPos = strripos($chkValue,$shouldSelect);
                        if(!($chkPos === false))//IF SEARCH SUCCESS ON ALIAS
                        {
                            $chkPos = strripos($chkSelect,$shouldSelect);
                            if(!($chkPos === false))
                            {
                
                                $beforeCharacter = $chkSelect[$chkPos-1];
                                $afterCharacter = $chkSelect[$chkPos+strlen($shouldSelect)];
                                //echo $chkSelect.','.$shouldSelect.'---'.$chkPos.'---'.$beforeCharacter.'---'.$afterCharacter.'<br>';
                                if((!isset($beforeCharacter) || trim($beforeCharacter)=='' || strcmp($beforeCharacter,'.')==0) && (trim($afterCharacter)=='' || !isset($afterCharacter)))
                                {   
                                    array_push($selectColumns,$chkSelect);
                                    break;
                                }
                            }
                
                        }
                    }
                }
                $selectColumnsCount = count($selectColumns);
                if($selectColumnsCount>0)
                {
                    foreach($selectColumns as $key=>$val)
                    {
                        $returnSql.=$val;
                        if($key!=$selectColumnsCount-1) 
                            $returnSql.=',';
                            
                    }
                    $returnSql.=$afterFrom;
                    return  $returnSql;
                }
                else
                    return $queryStr;
            }
            else
                return $queryStr;
        }
        else
            return $queryStr;
    }
 
 
Now i am thinking that function "parseSqlString" will be simpler if i just did something like this(which i have not yet done though):

Code: Select all

 
function parseSqlString($queryStr=NULL,$filter=NULL)
{
      $Sql =" SELECT ";
      foreach($filter as $val)
      {
             $Sql.=$val;
             //CONCATENATE COMMA AT THE END
      }
      $Sql.=" FROM (".$queryStr.")";
      return $Sql;
}
 
All this is done so that whenever i have to fetch data from any table i would have to use just a single function. Your suggestions will be highly appreciated :)
Post Reply