Content from database and site load speed

Kirby 3 Questions 🤔
1

I am working on a Kirby powered shop, with the products being .txt files as usual. Now I have to get the content (products) from a Microsoft SQL Server database.

I followed the virtual pages from database tutorial, and managed to get the products and create the virtual pages.

The problem is that the site got much slower now. I am using a cloud service, cloudclusters.io to host the database. I am testing locally, with only 6 products, and the home page which only shows minimal info from these products, takes 4-5 seconds to load. The panel also became slower in navigating between pages. I notice that when executing the queries from Microsoft’s app for this kind of things (Microsoft SQL Server Management studio), the results are generated fast enough.

Any ideas?

2

How long does it take to query the data without turning it into virtual pages, so just querying the data in a Kirby template?

3

I created a test template that includes the database query and virtual page creation code. It takes around 0.9 seconds to render, no matter if it creates virtual pages or not.

The original home page that displays the products created from a ‘product’ model still takes above 3 seconds. It’s like the use of the model is making things slower.

4

I am attaching the relevant code:

// plugins/helpers/index.php

function connectDB() {
  try {
    $server = option('db.server');
    $database = option('db.database');
    $port = option('db.port');
    $uid = option('db.uid');
    $pwd = option('db.pwd');
    
    $conn = new PDO("sqlsrv:server=$server,$port;Database=$database;", $uid, $pwd);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    return $conn;
  } catch (PDOException $e) {
    die("Error connecting to SQL Server");
  }
}

// models/products.php

<?php

class ProductsPage extends Kirby\Cms\Page {
  public function children() {
    $products = [];

    $pdo = connectDB();

    $query = '
      SELECT
        product.barcode,
        title.el AS title_el,
        description.el AS desc_el
    
      FROM dbo.product product
      LEFT JOIN dbo.prdnames title ON product.barcode = title.id
      LEFT JOIN dbo.prdshortdescriptions description ON product.barcode = description.id
    ';

    try {
      $statement = $pdo->query($query);

      $db_products = $statement->fetchAll(PDO::FETCH_ASSOC);

      if ($db_products) {
        foreach ($db_products as $product) {
          $products[] = [
            'slug' => $product['barcode'],
            'num'      => 0,
            'template' => 'product',
            'model'    => 'product',
            'content' => [
              'title' => $product['title_el'],
              'text'  => $product['desc_el'],
            ]
          ];
        }
      }

      return Pages::factory($products, $this, $draft = false);
    } catch (\PDOException $e) {
      die($e->getMessage());
    }
  }
}

As soon as I get the product’s page virtual children with page('products')->children(); , page loading speed suffers.

5

This try catch block doesn’t make sense, because neither $pdo->query() nor fetchAll() throw an error, but return false on failure, and in any case, children() should always return a pages collection even if an empty one if the database query fails.

Rather, you should throw an error (instead of dying) from your connectDb function that you then catch when using this function in the children function.

try {
 $pdo = connectDB();
} catch (Exception $e) {
  return Pages::factory([], $this, $draft = false);
}

Also, you’d better check if children is already defined:

public function children() {
  if($this->children() !== null) {
    return $this->children;
  }
  // Rest of code
}

Usually, virtual pages shouldn’t be much slower than usual pages as long as you don’t use thousands of pages in a flat structure.

If the queries are slow in general, caching the results might be another option.

6

Thanks for the code fixes @texnixe.

I found something strange. I was using dump(page('products')->children()) to check the results of the queries on the page. I removed it (while still outputing some products to the page the normal way), and page loading got much faster, between 1-2 seconds. Is this still high though? I see that connections to databases are usually taking a few milliseconds.

I tried creating the connection to the database outside Kirby, and the time is similar, so I guess something is not working properly between my local machine and the remote database.

7

That’s because those are two queries, then. You can avoid such things be storing stuff in a variable and then use that variable.

That’s something that you should do in general if you use the same queries (no matter if database or Kirby API) multiple times.

8

hi, i ran into a similar problem with ~130 virtual pages and used GitHub - bnomei/kirby3-stopwatch: Profile your Kirby 3 CMS code with precision in milliseconds (or seconds) to hunt for the bottleneck. in my case it was the creation of the array with the page models to feed the factory that slowed stuff down. so i used GitHub - bnomei/kirby3-lapse: Cache any data until set expiration time from the same author to cache the results and the site got much faster. i am still struggling with enabling APC-cache on my provider (mittwald) though and felt the docs are quite sparse at this point. anyway, here’s the relevant code from my page model:

 public static function pagesforResult($result, $parent = null) 
    { 
        
        $key = crc32(implode($result->toArray(function ($v) { return $v->kirby_slug().$v->video_modified(); }))); // unique key: kirby-slug+modification-date of all db-results
        $videos = Lapse::io($key, function () use ($result) {
            $videos = [];
            $num = 1;
            if ($result) {
                $title = static::KIRBY_TITLE;
                foreach ($result as $video) {
                    $videos[] = [
                    'slug'     => $video->kirby_slug(),
                    'num'      => $video->kirby_status() === 'listed' ? $num : null,
                    'template' => 'video',
                    'model'    => 'video',
                        'content'  => [
                            'title' => $video->$title() ?? 'New Video',
                            'video_title' => $video->$title(),
                            'video_title_de' => $video->video_title_de(),
                            'kirby_status' => is_null($video->kirby_status()) ? 'draft' : $video->kirby_status(),
                            'video_description'  => $video->video_description(),
                            'video_description_de'  => $video->video_description_de(),
                            'video_description_alt'  => $video->video_description_alt(),
                            'video_description_alt_de'  => $video->video_description_alt_de(),
                            'video_year'  => $video->video_year(),
                            'festival_slug'  => static::slugForID('festivals', 'festival_id', $video->festival_id()), // kirby single-select does not support INT as keys due to PHP quirk: revert to festival_slug (=iso2)
                            'video_maincategory_slug'   => static::slugForID('categories', 'category_id', $video->video_maincategory_id()),
                            'video_categories' => static::hasManyForId('videos_to_categories', 'category_id', 'video_id', $video->video_id()),
                            'video_duration' => $video->video_duration(),
                            'video_crop' => $video->video_crop(),
                            'video_format' => $video->video_format(),
                            'video_language' => $video->video_language(),
                            'video_language_de' => $video->video_language_de(),
                            'video_attributes' => $video->video_attributes(),
                            'video_attributes_de' => $video->video_attributes_de(),
                            'video_courtesy' => $video->video_courtesy(),
                            'video_courtesy_de' => $video->video_courtesy_de(),
                            'video_award' => $video->video_award() === 'true' ? 1 : 0,
                            'video_artists' => static::hasManyForId('videos_to_artists', 'artist_id', 'video_id', $video->video_id()),
                            'video_countries' => static::hasManyForId('videos_to_countries', 'country_id', 'video_id', $video->video_id()),
                            'video_trailer_url'  => $video->video_trailer_url(),
                            'video_additional_videos'  => $video->video_additional_videos(),
                            'video_created'  => $video->video_created(),
                            'video_modified'  => $video->video_modified(),
                            'video_id' => $video->video_id() // darstellung im panel nur zur info 
                        ]
                    ];
                    $num++;
                }
            }
            return $videos;
        });
        
        return Pages::factory($videos, $parent);
    }
9

Thanks @Tilman. I will have a look at your code. Even though I am not sure yet, my situation seems to be related with the speed Windows Subsystem for Linux is connecting to a database hosted remotely. I installed an SQL server locally and the speed of connecting and rendering virtual pages is very fast.