Benchmark performance improvement

[maxime-rainville]

Doesn't need to be done in time for the CMS5.1 beta, but it would be nice to have those numbers for StripeCon.

Those numbers will be somewhat arbitrary and will very based on your environment. Let's include all those caveats

Acceptance criteria

• We have made a best effort to measure performance improvements for the recent ORM change we've implemented.
• The measurement use typical set ups.
• The performance improvement is documented in the CMS 5.1 changelog.
• The CMS 5.1 changelog include some sensible disclosure about the uncertainty of this kind of exercise.

Related card

• Optimise filtering ID: foreignIDFilter in relation lists silverstripe-framework#10887
• Optimise filtering ID columns silverstripe-framework#10860
• Eager loading for DataObject relationships silverstripe-framework#8426
• Optimise SiteTree search when elemental is installed silverstripe-elemental#1067
• Identifiying potential usecase for eager loading .github#68

Notes

• We already claim a 9% improvement for SiteTree search, but we might need to double check this.

PRs

• DOC Performance improvements for changelog #333

[emteknetnz]

Test setup for eager loading

Results:

• hasOne - 3200% faster (0.0078s vs 0.2595s)
• hasMany - 25% faster (0.1453s vs 0.1819s)
• manyMany - 25% faster (0.1664s vs 0.2083s)
• manyManyThrough - 16% faster (0.6586s vs 0.7681s)

MyDataObject.php

<?php

use SilverStripe\ORM\DataObject;

class MyDataObject extends DataObject
{
    private static $table_name = 'MyDataObject';

    private static $db = [
        'Title' => 'Varchar'
    ];

    private static $has_one = [
        'MyOneDataObject' => MyOneDataObject::class
    ];

    private static $has_many = [
        'MySubDataObjects' => MySubDataObject::class
    ];

    private static $many_many = [
        'MyManyDataObjects' => MyManyDataObject::class,
        'MyManyThroughDataObjects' => MyManyThroughDataObject::class
    ];
}

MySubDataObject.php

<?php

use SilverStripe\ORM\DataObject;

class MySubDataObject extends DataObject
{
    private static $table_name = 'MySubDataObject';

    private static $db = [
        'Title' => 'Varchar'
    ];

    private static $has_one = [
        'MyDataObject' => MyDataObject::class
    ];
}

MyManyDataObject

<?php

use SilverStripe\ORM\DataObject;

class MyManyDataObject extends DataObject
{
    private static $table_name = 'MyManyDataObject';

    private static $db = [
        'Title' => 'Varchar'
    ];

    private static $belongs_many_many = [
        'MyDataObject' => MyDataObject::class
    ];
}

MyManyThroughDataObject

<?php

use SilverStripe\ORM\DataObject;

class MyManyThroughDataObject extends DataObject
{
    private static $table_name = 'MyManyThroughDataObject';

    private static $db = [
        'Title' => 'Varchar'
    ];
}

MyOneDataObject.php

<?php

use SilverStripe\ORM\DataObject;

class MyOneDataObject extends DataObject
{
    private static $table_name = 'MyOneDataObject';

    private static $db = [
        'Title' => 'Varchar'
    ];
}

MySubDataObject.php

<?php

use SilverStripe\ORM\DataObject;

class MySubDataObject extends DataObject
{
    private static $table_name = 'MySubDataObject';

    private static $db = [
        'Title' => 'Varchar'
    ];

    private static $has_one = [
        'MyDataObject' => MyDataObject::class
    ];
}

MyThroughDataObject.php

<?php

use SilverStripe\ORM\DataObject;

class MyThroughDataObject extends DataObject
{
    private static $table_name = 'MyThroughDataObject';

    private static $has_one = [
        'MyDataObject' => MyDataObject::class,
        'MyManyThroughDataObject' => MyManyThroughDataObject::class,
    ];
}

PageController.php

<?php

use SilverStripe\Versioned\Versioned;
use SilverStripe\ORM\DB;
use SilverStripe\CMS\Controllers\ContentController;

class PageController extends ContentController
{
    protected function init()
    {
        parent::init();
        //
        // DB::query('truncate MyDataObject');
        // DB::query('truncate MySubDataObject');
        // DB::query('truncate MyManyDataObject');
        // DB::query('truncate MyDataObject_MyManyDataObjects');
        // DB::query('truncate MyManyThroughDataObject');
        // DB::query('truncate MyThroughDataObject');
        // $b = [];
        // $c = [];
        // $d = [];
        // $e = [];
        // $f = [];
        // $g = [];
        // for ($i = 1; $i <= 100; $i++) {
        //     $a[] = "('Title $i', '$i')";
        //     $g[] = "('Title $i')";
        //     for ($j = 1; $j <= 100; $j++) {
        //         $jj = ($i - 1) * 100 + $j;
        //         $b[] = "('$i', 'Title $j')";
        //         $c[] = "('Title $j')";
        //         $d[] = "('$i', '$jj')";
        //         $e[] = "('Title $j')";
        //         $f[] = "('$i', '$jj')";
        //     }
        // }
        // $as = implode(',', $a);
        // $bs = implode(',', $b);
        // $cs = implode(',', $c);
        // $ds = implode(',', $d);
        // $es = implode(',', $e);
        // $fs = implode(',', $f);
        // $gs = implode(',', $g);
        // DB::query("insert into MyDataObject (Title, MyOneDataObjectID) values $as");
        // DB::query("insert into MyOneDataObject (Title) values $gs");
        // DB::query("insert into MySubDataObject (MyDataObjectID, Title) values $bs");
        // DB::query("insert into MyManyDataObject (Title) values $cs");
        // DB::query("insert into MyDataObject_MyManyDataObjects (MyDataObjectID, MyManyDataObjectID) values $ds");
        // DB::query("insert into MyManyThroughDataObject (Title) values $es");
        // DB::query("insert into MyThroughDataObject (MyDataObjectID, MyManyThroughDataObjectID) values $fs");
        //
        Versioned::set_reading_mode('Stage.Stage');

        $s = microtime(true);
        // 0.0365, 0.400, 0.342 - 0.2595s
        // foreach (MyDataObject::get() as $do) {
        //     $do->MyOneDataObject()->Title;
        // }
        // 0.1839, 0.1768, 0.1849 - 0.1819s
        // foreach (MyDataObject::get() as $do) {
        //     $do->MySubDataObjects()->toArray();
        // }
        // 0.1909, 0.2186, 0.2155 - 0.2083s
        // foreach (MyDataObject::get() as $do) {
        //     $do->MyManyDataObjects()->toArray();
        // }
        // 0.7648, 0.7779, 0.7615 - 0.7681s
        // foreach (MyDataObject::get() as $do) {
        //     // echo '<hr>';
        //     // echo "<h1>{$do->ID}</h1>";
        //     // print_r($do->MyManyThroughDataObjects()->column('ID'));
        //     $do->MyManyThroughDataObjects()->toArray();
        // }
        // 0.0060, 0.0102, 0.0071 - 0.0078s
        // foreach (MyDataObject::get()->eagerLoad('MyOneDataObject') as $do) {
        //     $do->MyOneDataObject()->Title;
        // }
        // 0.1484, 0.1345, 0.1529 - 0.1453s
        // foreach (MyDataObject::get()->eagerLoad('MySubDataObjects') as $do) {
        //     $do->MySubDataObjects()->toArray();
        // }
        // 0.1761, 0.1600, 0.1630 - 0.1664s
        // foreach (MyDataObject::get()->eagerLoad('MyManyDataObjects') as $do) {
        //     $do->MyManyDataObjects()->toArray();
        // }
        // 0.6653, 0.6500, 0.6605 - 0.6586s
        foreach (MyDataObject::get()->eagerLoad('MyManyThroughDataObjects') as $do) {
            $do->MyManyThroughDataObjects()->toArray();
        }
        $t = microtime(true) - $s;
        printf('%0.4f', $t);
        die;
    }
}

[maxime-rainville]

Doc has been updated.