Hi, i wrote that post.
I had to carry out a project where I had to read from a section of the panel a sqlite database of 35,000 medical authorizations.
Like you, it took about 30 seconds to load the panel section, even though it was paginated every 10 pages.
1.first use last version of php: with php7.4
2. Order directly from the database and not from the bluprint. I directly remove the part code that allows ordering from the blueprint.
3. i wrote a custom section plugin with some optimizations & features. with cache (best result usign apcu driver), custom query directly from section php code, dont query all columns only query waht u use, sort directly from de query.
4. i use sqlite, because working from file system on such big data the “file I/O” collapse my server
<?php
use Exception;
use Kirby\Cms\Pages;
use Kirby\Cms\Site;
use Kirby\Toolkit\A;
use Kirby\Toolkit\Query;
$pagesSection = require kirby()->root('kirby').'/config/sections/pages.php';
return A::merge($pagesSection, [
'props' => [
'query' => function($query = null) {
return $query;
},
'search' => function ($search = true) {
return $search;
},
],
'computed' => [
'pages' => function () {
$authCache = kirby()->cache('af');
$authData = $authCache->get('authdata');
if($authData === null){
$authData = [];
$dba = Db::select('datos', ['fecha','idc','nota_interna', 'observaciones' ,'localidad', 'especialidad', 'profesional','plantilla', 'slug', 'title', 'status', 'nro_afiliado', 'prestador', 'codigos_manuales', 'codigos_nn'], null, 'fecha DESC, slug DESC');
foreach ($dba as $autorizacion) {
$authData[] = [
'slug' => $autorizacion->slug(),
'num' => $autorizacion->status() === 'listed' ? 0 : null,
'template' => $autorizacion->plantilla(),
'model' => $autorizacion->plantilla(),
'content' => [
'title' => empty($autorizacion->title()) ? $autorizacion->idc() : $autorizacion->title() ,
'status' => is_null($autorizacion->status()) ? 'unlisted' : $autorizacion->status(),
'fecha' => $autorizacion->fecha(),
'nro_afiliado' => $autorizacion->nro_afiliado(),
// 'fecha_emision' => $autorizacion->fecha_emision(),
// 'fecha_recepcion' => $autorizacion->fecha_recepcion(),
'prestador' => $autorizacion->prestador(),
// 'coseguro' => $autorizacion->coseguro(),
'codigos_manuales' => $autorizacion->codigos_manuales(),
'codigos_nn' => $autorizacion->codigos_nn(),
// 'diagnostico' => $autorizacion->diagnostico(),
'observaciones' => $autorizacion->observaciones(),
// 'informe' => $autorizacion->informe(),
// 'creado' => $autorizacion->creado(),
'nota_interna' => $autorizacion->nota_interna(),
// 'mensajeria' => $autorizacion->mensajeria(),
// 'pdf' => $autorizacion->pdf(),
'localidad' => $autorizacion->localidad(),
'especialidad' => $autorizacion->especialidad(),
'profesional' => $autorizacion->profesional(),
// 'diagnostico' => $autorizacion->diagnostico(),
// 'memo' => $autorizacion->memo(),
]
];
}
$authCache->set('authdata', base64_encode(gzcompress(serialize($authData))), 15);
$authData = base64_encode(gzcompress(serialize($authData)));
}
$mo = pages('dbautorizaciones');
$pages = Pages::factory(unserialize(gzuncompress(base64_decode($authData))), $mo);
// search
if ($query = get('q')) {
if(strpos($query, '+') !== FALSE){
$query_arr = explode('+', $query);
foreach($query_arr as $query){
$pages = $pages->search($query);
}
} else {
$pages = $pages->search($query);
}
}
// // loop for the best performance
// foreach ($pages->data as $id => $page) {
// // remove all protected pages
// if ($page->isReadable() === false) {
// unset($pages->data[$id]);
// continue;
// }
// // filter by all set templates
// if ($this->templates && in_array($page->intendedTemplate()->name(), $this->templates) === false) {
// unset($pages->data[$id]);
// continue;
// }
// }
// // sort
// if ($this->sortBy) {
// $pages = $pages->sortBy(...$pages::sortArgs($this->sortBy));
// }
// // flip
// if ($this->flip === true) {
// $pages = $pages->flip();
// }
// pagination
$pages = $pages->paginate([
'page' => $this->page,
'limit' => $this->limit
]);
return $pages;
},
'add' => function () {
if ($this->query) {
return false;
}
if ($this->create === false) {
return false;
}
if (in_array($this->status, ['draft', 'all']) === false) {
return false;
}
if ($this->isFull() === true) {
return false;
}
return true;
},
],
'toArray' => function () {
return [
'data' => $this->data,
'errors' => $this->errors,
'options' => [
'add' => $this->add,
'empty' => $this->empty,
'headline' => $this->headline,
'help' => $this->help,
'layout' => $this->layout,
'link' => $this->link,
'max' => $this->max,
'min' => $this->min,
'search' => $this->search,
'size' => $this->size,
'sortable' => $this->sortable
],
'pagination' => $this->pagination,
];
}
]);
PD:
I’m going to check the code well to see other optimizations made because it was a while ago and I don’t have it in mind. i will write later with more info.
