how to speed up query

Yii 1.1 General Discussion
1

I have sintaks in model like this





/**

 * This is the model class for table "{{regional}}".

 *

 * The followings are the available columns in table '{{regional}}':

 * @property integer $id

 * @property string $regional_cd

 * @property string $regional_nm

 * @property string $pro_cd

 * @property string $kab_cd

 * @property string $kec_cd

 * @property string $kel_cd

 * @property string $status_cd

 * @property string $old_nm

 * @property integer $active

 */


public function getFulladdress()

	{

		$address='';

		$prop = substr($this->regional_cd,0,2)."00000000";

		$kab = substr($this->regional_cd,0,4)."000000";

		$kec = substr($this->regional_cd,0,7)."000";

		

		if((int)$this->kel_cd > 0 )

		{

			$address.='Kel. '.$this->regional_nm;

		}

		

		if((int)$this->kec_cd > 0)

		{

			$models=$this->model()->findAll(array(

				'condition'=>'regional_cd=:propCd',

				'params'=>array(':propCd'=>$kec),

			));

				

			foreach($models as $model)

			{

				if((int)$this->kel_cd > 0)

					$address.=', Kec. '.$model->regional_nm;

				else

					$address.='Kec. '.$model->regional_nm;

			}

		}

		

		if((int)$this->kab_cd > 0)

		{

			$models=$this->model()->findAll(array(

				'condition'=>'regional_cd=:propCd',

				'params'=>array(':propCd'=>$kab),

			));

				

			foreach($models as $model)

			{

				if((int)$this->kec_cd > 0)

					$address.=', Kab. '.$model->regional_nm;

				else

					$address.='Kab. '.$model->regional_nm;

			}

		}

		

		if((int)$this->pro_cd > 0)

		{

			

			$models=$this->model()->findAll(array(

				'condition'=>'regional_cd=:propCd',

				'params'=>array(':propCd'=>$prop),

			));

				

			foreach($models as $model)

			{

				if((int)$this->kab_cd > 0)

					$address.=', Prop. '.$model->regional_nm;

				else

					$address.='Prop. '.$model->regional_nm;

			}

		}

		

		return $address;


	}

the problem that code become slow execute in browser, please help me to speed up to execute that code …

2 

foreach($models as $model)

{

   if((int)$this->kel_cd > 0)

      $address.=', Kec. '.$model->regional_nm;

   else

      $address.='Kec. '.$model->regional_nm;

}

The value of $this->kel_cd is fixed… so why are you checking it for every record in a foreach loop?

It’s better to check the value and then do the foreach loop…

3

How many database entries do you have? Do you have an appropriate index on the condition columns?

4

i am loop every record to get fulladdress, i will to try with foreach loop … thank about this solutions

5

the entries of record is 74851 recordd …

:)

6

Do you have an index on column regional_cd

7

Do it this way; 1 comparision instead of n comparisions:




if((int)$this->kel_cd > 0)

{

    foreach($models as $model)

    {

        $address.=', Kec. '.$model->regional_nm;

    }

}

else

{

    foreach($models as $model)

    {

        $address.='Kec. '.$model->regional_nm;

    }

}
8

i don’t have index in regional_cd, how to create index in regional_cd??

9

What do "kel", "kec", "kab", and "prop" stand for? It seems weird that you would have to make so many findAll() calls on one table and in the same function to get the "full address".

I’m thinking that redesigning the table/database would be better than finding a better query.

10

thanks for the advice you have provided, I get the table structure from the old developer, I just menlanjutkan existing code.