Optimising data loading from DB with a big amount of entries

I’m currently evaluating if Kirby is a good fit for a project. It will be a site that features more than 40000 products. These products are already stored in an existing database and I’ll mostly want to use Kirby to display them (so no editing required, except for adding a photo to each product).

I followed the guide for getting data from a DB (https://getkirby.com/docs/guide/virtual-pages/content-from-database) and merging content sources to add the image (https://getkirby.com/docs/guide/virtual-pages/merging-virtual-and-local-content). This all worked fine, except for performance.

This naive way of implementing a DB as a data source clearly is not meant for working with 40k+ elements. Every time anything is done with the products, ALL the products are loaded (even for example when only displaying the page that shows one product.

I hacked around a bit and managed to optimise the rendering for the actual site. The panel however is another topic: Kirby just seems to really want to load all products, regardless how many are displayed in the end. I understand that it doesn’t do anything smart with data from a DB by default, but I don’t understand where I’m able to hook into to improve the naive implementation.

The main issue is that at some point the children() method will be called in my ProductsPage class. And there are no arguments passed… So I have no idea what will happen afterwards with it (e.g. if it’s going to get paginated to only show 100 entries, or if a search is performed on the content). I would like to hook in somewhere where this information is available so I can optimise my DB query. Is there such a place? The only option I found so far is to read again the query/params of the request so I can myself make sense if a pagination or search is performed. But it seems silly to redo that work that is surely already done somewhere in the code.

Am I trying to do something here that is simply not meant to be done with Kirby or did I just not understand it well enough yet?

Note that I read many posts on the forum. Many are for Kirby2 and many are about a huge amount of pages as files, not in the DB. I need a DB because I will implement complex search possibilities on the entries that I doubt could be easily done when it’s file-based.

Thanks for helping!

Hi. I had a similar issue with a 60000 medical records + 40000 clients.

Your problem is with the front end or the panel?

It’s more with the panel. Although if you have some help with the frontend, that would also be great to know. I did something there that is quite hacky to get it to work (for a paginated view of all the products, I just read the request params in the Page class to know which slice of the data to load).

A shameless plug for my suggestion on feedback.getkirby.com.

I’ll leave this here: https://kirby.nolt.io/131

@diezmilseres: If you have some hints how you did this, that would be great!

@rasteiner: So if I understand your suggestion, this means there isn’t really what I’m looking for. But I like your idea, I might extend the Collection class to work more like you describe and then return that from the call to children()

Basically yes. I’ve tried it once, but it was too much work for me. Also because it seems like it’s much more work if you try to do it without altering the kirby source.


The gif goes back to february 2019, so the code is of no use today. Also it only “kinda worked”.

If you don’t need to edit the data in the panel, maybe it’s easier to just not extend “children” and instead create some other method you can call from your templates, but which the panel won’t load. Then create some panel view yourself to manage the “image uploading”

Maybe it could be an idea to not use children at all. But I also need the data in other parts of the panel, not just for the image… I for example want to be able to create lists that contain a number of products. So in the panel you should be able to search through and list products to then multi-select.

I’m still unsure in general if Kirby is a good fit here or if I’m not better off with another CMS or rather a framework like Symfony where I just code this myself…

How did you do this that is shown in the gif? With a custom Collection class?

Extended Page (both for the parent and the children) and Pages classes.

But the Pages class needs to pretty much override everything, not just its own methods, but also those in Kirby\Cms\Collection and Kirby\Toolkit\Collection. It goes all the way up to the “Iterator” interface. That’s the reason I feel such a change would have to come from kirby core: doing it “at the roots” would be “simpler”.

Then maybe I’m not seeing an obvious solution, in that case I’d be very interested in learning about that :slight_smile:

Hi, i resolved the speed on the panel with to things:

  1. coding a custom page section taht access directly to the database and then generate the pages
  2. change the way taht page model generate ID (this slow the query more than other things)

request loading time on a shared 5usd server

image

pagemodel

public function id(): string
  {
      return $this->id = 'dbafiliados/'. $this->nro_afiliado() . '/' . $this->uid();
  }

custom page section controller php

<?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 () {

         $authData = [];

         if ($query = get('q')) {

           if(strpos($query, '+') !== FALSE){

             $query_arr = explode('+', $query);
             $query_a = $query_arr[0];
             $query_b = $query_arr[1];

               $dba = Db::select('datos', ['fecha','idc','nota_interna', 'observaciones' ,'localidad', 'especialidad', 'profesional','plantilla', 'slug', 'title',  'status', 'nro_afiliado', 'prestador', 'codigos_manuales', 'codigos_nn'], "(nota_interna like '%$query_a%' or observaciones like '%$query_a%' or localidad like '%$query_a%' or especialidad like '%$query_a%' or profesional like '%$query_a%' or nro_afiliado like '%$query_a%' or prestador like '%$query_a%' or codigos_manuales like '%$query_a%' or codigos_nn like '%$query_a%') AND (nota_interna like '%$query_b%' or observaciones like '%$query_b%' or localidad like '%$query_b%' or especialidad like '%$query_b%' or profesional like '%$query_b%' or nro_afiliado like '%$query_b%' or prestador like '%$query_b%' or codigos_manuales like '%$query_b%' or codigos_nn like '%$query_b%')", 'fecha DESC, slug DESC');

          } else {

            $dba = Db::select('datos', ['fecha','idc','nota_interna', 'observaciones' ,'localidad', 'especialidad', 'profesional','plantilla', 'slug', 'title',  'status', 'nro_afiliado', 'prestador', 'codigos_manuales', 'codigos_nn'], "nota_interna like '%$query%' or observaciones like '%$query%' or localidad like '%$query%' or especialidad like '%$query%' or profesional like '%$query%' or nro_afiliado like '%$query%' or prestador like '%$query%' or codigos_manuales like '%$query%' or codigos_nn like '%$query%'", 'fecha DESC, slug DESC');

          }

         } else {
           $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'    => 'dbautorizacionlist',
                '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(),


                ]
            ];
          }




        $mo = pages('dbautorizaciones');

        $pages = Pages::factory($authData, $mo);



            // search
            // if ($query = get('q')) {
            //
            //     if(strpos($query, '+') !== FALSE){
            //         $query_arr = explode('+', $query);
            //         foreach($query_arr as $query){
            //             $pages = $pages->search($query)->sortBy('fecha', 'desc', 'slug', 'desc');
            //         }
            //     } else {
            //       $pages = $pages->search($query)->sortBy('fecha', 'desc', 'slug', 'desc');
            //     }
            // }

            // // 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,
        ];
    }
]);
1 Like

Hi, thanks for all your inputs!

I now managed to hack something together, extending Kirby\Cms\Pages, overwriting a few methods (get, search, paginate and count). In the children() method I then return an empty instance of this class and only load the data from the DB in get() and in paginate().

This works for what I’ve tested it with so far (listing in the panel and selecting a set of the products in another page). I’m sure there’s many cases where I broke things and I’ll still have to overwrite other methods.

I’m not super happy with the solution as it’s very hacky, but it might do the job…

Btw @rasteiner: I upvoted your suggestion :slight_smile: