New Server Slow response to server querywith Editor

wkeull

I have recently upgraded to a new server and suspect that the issue is with configuration, the query size is only about 3-5k items however it is routinely taking about 10-15sec to render the below: When debugging the sql, mysql is performing the query very quickly so I believe that this issue is related to the rendering of the data. Server Side processing is enabled for this as well. Any suggestions?

            Editor::inst($this->editorDb, 'work_view')
                    ->fields(
                            Field::inst('work_view.id')
                            ->validator('Validate::notEmpty'), Field::inst('work_view.company')
                            ->setValue($this->session->userdata('companyid')), Field::inst('work_view.date_ord')
                            ->validator(Validate::dateFormat(
                                            'j M Y H:i', ValidateOptions::inst()
                                            ->allowEmpty(false)
                            ))
                            ->getFormatter(Format::datetime(
                                            'Y-m-d H:i:s', 'j M Y H:i'
                            ))
                            ->setFormatter(Format::datetime(
                                            'j M Y H:i', 'Y-m-d H:i:s'
                            )), Field::inst('work_view.date_start')
                            ->validator(Validate::dateFormat(
                                            'j M Y H:i', ValidateOptions::inst()
                                            ->allowEmpty(false)
                            ))
                            ->getFormatter(Format::datetime(
                                            'Y-m-d H:i:s', 'j M Y H:i'
                            ))
                            ->setFormatter(Format::datetime(
                                            'j M Y H:i', 'Y-m-d H:i:s'
                            )), Field::inst('work_view.date_due')
                            ->validator(Validate::dateFormat(
                                            'j M Y H:i', ValidateOptions::inst()
                                            ->allowEmpty(false)
                            ))
                            ->getFormatter(Format::datetime(
                                            'Y-m-d H:i:s', 'j M Y H:i'
                            ))
                            ->setFormatter(Format::datetime(
                                            'j M Y H:i', 'Y-m-d H:i:s'
                            )), Field::inst('work_view.date_del')
                            ->validator(Validate::dateFormat(
                                            'j M Y H:i', ValidateOptions::inst()
                                            ->allowEmpty(false)
                            ))
                            ->getFormatter(Format::datetime(
                                            'Y-m-d H:i:s', 'j M Y H:i'
                            ))
                            ->setFormatter(Format::datetime(
                                            'j M Y H:i', 'Y-m-d H:i:s'
                            ))
                            ->validator('Validate::notEmpty'), 
                            Field::inst('work_view.customer')
                            ->validator('Validate::notEmpty'), 
                            Field::inst('work_view.desc')
                            ->validator('Validate::notEmpty'), 
                            Field::inst('work_view.role_name')
                            ->validator('Validate::notEmpty'), 
                            Field::inst('work_view.approved')
                            ->validator('Validate::notEmpty'), 
                            Field::inst('work_view.note'),
                            Field::inst('work_view.name')
                            ->options(Options::inst()
                                    ->table('customer')
                                    ->value('id')
                                    ->label(array('cname', 'fname', 'lname'))
                                    ->render(function ( $row ) {
                                        if ($row['cname'] != null) {
                                            return $row['cname'];
                                        } else {
                                            return $row['fname'] . ' ' . $row['lname'];
                                        }
                                    })
                                    ->where(function ($q) {
                                        $q->where('customer.company', $this->session->userdata('companyid'));
                                    })
                            )
                            ->validator('Validate::notEmpty'), Field::inst('customer.cname')
                            ->validator('Validate::notEmpty'), Field::inst('customer.fname')
                            ->validator('Validate::notEmpty'), Field::inst('customer.lname')
                            ->validator('Validate::notEmpty')
                    )
                    ->leftJoin('customer', 'customer.id', '=', 'work_view.name')
                    ->where('work_view.company', $this->session->userdata('companyid'))
                    //->debug(true)
                    ->process($post)
                    ->json();
            break;

kthorngren

If you have server side processing enabled then the server script should only be returning the number of rows to be displayed on one page (10 by default). If its returning 3-5k rows then its not following the Server Side Processing protocol. How much data is returned by the server script when loading the Datatable?

Kevin

wkeull

Kevin,
Thanks for the reply. The server was replying with the correct row qty. I have just resolved the issue, for some reason when I imported my dB, my indexing did not transfer properly. On re-import I was able to restore indexing and this has resolved the issue. The interesting thing with this is how many querying without the indexing was still very quick, however the same query via the editor was delayed. In anycase this is resolved. thanks again.

allan

Must have been something to do with how the query planner was interpreting the SQL that each command was building. Either way, good to hear you got it back to normal now. Not exactly to plan when you put new hardware in and it gets slower!

Allan