Trimming down an expensive query

I have an query that takes almost 3 minutes to give results. It pins one of the CPU cores at 100% the whole time. I'm trying to figure out a way to get the same result without the expensive query. The results are correct, so I have no complaints about that.

App\Cladding.php(Model)

<?php

namespace App;

use App\BusinessParameters;
use Illuminate\Database\Eloquent\Model;

class Cladding extends Model
{
    protected $fillable = [
        'section_id',
        'profile_id',
        'width',
        'length',
        'guage_id',
        'color_id',
        'fastenertype_id',
        'screw_spacing',
        'orientation',
        'rounding',
        'vbar_coverage_per_day',
        'vbar_num_of_men',
        'insu_coverage_per_day',
        'insu_num_of_men',
        'subg_coverage_per_day',
        'subg_num_of_men',
        'mclosure',
        'fclosure',
        'custom_width',
    ];
...
    public function getSequencingResultsAttribute()
    {
        // Count previous records including itself
        $count = $this->section->claddings->where('id', '<=', $this->id)->count();
        // Extract widths for those records
        $SumOfWidths = $this->section->claddings->take($count)->sum('width');

        // Count previous records not including itself
        $count = $this->section->claddings->where('id', '<', $this->id)->count();
        // Extract Results for those records
        $PreviousResults = $this->section->claddings->take($count)->sum('SequencingResults');


        // If rounding Down is selected
        if ($this->rounding == 3) {
            return floor($SumOfWidths / $this->SheetWidth - $PreviousResults);
        }
        // else round up
        else {
            return  ceil($SumOfWidths / $this->SheetWidth - $PreviousResults);
        }
    }
...

the expensive part of this query is:

        $PreviousResults = $this->section->claddings->take($count)->sum('SequencingResults');

If remove this line the calculation completes very quickly but of course it's incorrect.

Can anyone see the best way to trim this query down?

bugsysha

5 years ago

Level 61

Why from the cladding model you are getting a relationship then accessing that same cladding model? And on top of that, you have a calculation of SequencingResults property in which you recursively do the sum of that same property?

Not sure what you are trying to do, but it looks bad. Or maybe I do not understand what is going on there.

Tray2

5 years ago

Level 73

Need some more info on this one to be able to help you.

What does your tables look like?
What does your data look like?
What is the expected result?

fbc

5 years ago

Level 2

Many claddings belong to a section. In order to calculate this correctly , it needs to sum the 'SequencingResults' of the previous cladding areas.

fbc

5 years ago

Best Answer

Level 2

Since each cladding area calculates itself in series, I'm playing with writing it value to a session variable so the next cladding only needs to add it's result to that session variable. So far I've been able to make it work..

    public function getSequencingResultsAttribute()
    {
        // Count previous records including itself
        $count = $this->section->claddings->where('id', '<=', $this->id)->count();
        // Extract widths for those records
        $SumOfWidths = $this->section->claddings->take($count)->sum('width');

        // Count previous records not including itself
        $count = $this->section->claddings->where('id', '<', $this->id)->count();
        // Extract Results for those records
        // $PreviousResults = $this->section->claddings->take($count)->sum('SequencingResults');
        // session(['PreviousResults' => floor($SumOfWidths / $this->SheetWidth - session('PreviousResults'))]);

        // If rounding Down is selected
        if ($this->rounding == 3) {
            return floor($SumOfWidths / $this->SheetWidth - session('PreviousResults'));
            session(['PreviousResults' => session('PreviousResults') + floor($SumOfWidths / $this->SheetWidth - session('PreviousResults'))]);
        }
        // else round up
        else {
            return  ceil($SumOfWidths / $this->SheetWidth - session('PreviousResults'));
            session(['PreviousResults' => session('PreviousResults') + ceil($SumOfWidths / $this->SheetWidth - session('PreviousResults'))]);
        }

    }

fbc

5 years ago

Level 2

I figured it out:

    public function getSequencingResultsAttribute()
    {
        // Count previous records including itself
        $count = $this->section->claddings->where('id', '<=', $this->id)->count();
        // Extract widths for those records
        $SumOfWidths = $this->section->claddings->take($count)->sum('width');

        if ($this->rounding == 3) {
            $x = floor($SumOfWidths / $this->SheetWidth - Session::get('PreviousResults'));
            Session::put('PreviousResults', Session::get('PreviousResults') + floor($SumOfWidths / $this->SheetWidth - Session::get('PreviousResults')));
            return $x;
        }
        // else round up
        else {
            $x = ceil($SumOfWidths / $this->SheetWidth - Session::get('PreviousResults'));
            Session::put('PreviousResults', Session::get('PreviousResults') + ceil($SumOfWidths / $this->SheetWidth - Session::get('PreviousResults')));
            return $x;
        }
    }

davidifranco

5 years ago

Level 10

Perhaps instead of getting the count of records your can select the id's and query against them.

	$ids = $this->section->claddings()
			->select('id')
			->where('id', '<=', $this->id)
			->get();
       
	 // Extract widths for those records
        $SumOfWidths = $this->section->claddings()->whereIn('id', $ids)->sum('width');

        // Count previous records not including itself
        $ids = $this->section->claddings()->select('id')->whereNotIn('id', $this->id)->count();

        // Extract Results for those records
        $PreviousResults = $this->section->claddings()->whereIn('id', $ids)->sum('SequencingResults');


        // If rounding Down is selected
        if ($this->rounding == 3) {
            return floor($SumOfWidths / $this->SheetWidth - $PreviousResults);
        }
        // else round up
        else {
            return  ceil($SumOfWidths / $this->SheetWidth - $PreviousResults);
        }

Please or to participate in this conversation.