Complex Query Performance


I need to create a report using PIE (Google Charts) to see how many jobs were delivered but the problem is that my query is affecting a lot of the performance. I already added the index in the DB but still not enough.

I did a lot of research and people say to create a view in the DB but I don’t know how to do that. This is my code below, any suggestion? I don’t know what to do anymore.

I’m trying to show how many jobs were delivered and the total of jobs delivered by region.

public function actionGetregion() {

		$jobHistory = JobHistory::find()

            ->where(['status' => "Order Delivered"])

            ->andWhere(['>=', 'job_datetime', '2016-01-01'])

            ->andWhere(['<', 'job_datetime', '2016-01-31'])





		$myJobs = array();

		$regions = array();


		foreach($jobHistory as $currentJob) {

			array_push($myJobs, $currentJob['job_id']);

			array_push($regions, $currentJob['job']['delivery_region']);



		$uniqueArray = array_unique($regions);

		$rows = array();

		$table = array();


		$table['cols'] = array(

			array('label' => 'Region', 'type' => 'string'),

			array('label' => 'Percentage', 'type' => 'number')



		foreach ($uniqueArray as $singleRegion) {

			$jobTable = Job::find()

				->where(['id' => $myJobs])

				->andWhere(['delivery_region' => $singleRegion])





			$temp = array();

			$temp[] = array('v' => $jobTable[0]['deliveryRegion']['region_group']);

			// Values of each slice

			$temp[] = array('v' => count($jobTable)); 

			$rows[] = array('c' => $temp);



		$table['rows'] = $rows;

		$jsonTable = json_encode($table);


		return $jsonTable;


the rows and temp var is just to put in the PIE chart format for Google.

I will suggest using a plain sql instead.

Can help if you can’t do it.

I sent you a private message.