SilverStripe create / insert bulk records

Question

I am developing a website using SilverStripe. In my project, I am saving collections of DataObjects into the database. I am looping through the collection and saving each object in the database as below.

foreach ($collection as $item) {
    $dbItem = Item::create();
    //assign data to $dbItem
    $dbItem->write();
}

The problem with the above code is that when I have hundreds of records, it is slow in terms of performance.

In SilverStripe is there a way to bulk insert or create like in Laravel for better performance?

Take a look at the `BulkLoader` base class - there's only one default implementation in framework, which is `CsvBulkLoader`, but you could write your own — scrowler, Jul 23 '19 at 09:46

3dgoo · Accepted Answer · 2019-07-25T03:01:47.480

As mentioned by WMK we can use SQLInsert to insert all the data into the database through a single database insert call.

Here is an example of how to use SQLInsert in SilverStripe 4.

The following inserts a $collection array into the database:

$insert = SilverStripe\ORM\Queries\SQLInsert::create('Item');
$insert->addRows($collection);
$insert->execute();

The following shows an example collection of items to insert into the database. There is an added foreach loop that adds default Created and LastEdited values. These are not essential but may be useful. If not added the Created and LastEdited values will be null in the database.

$collection = [
    [
        'Title' => 'Item 1',
        'Content' => '<p>Content 1</p>',
    ],
    [
        'Title' => 'Item 2',
        'Content' => '<p>Content 2</p>',
    ],
    [
        'Title' => 'Item 3',
        'Content' => '<p>Content 3</p>',
    ],
];

$currentDate = date('Y-m-d H:i:s');

foreach ($collection as &$item) {
    $item['Created'] = $currentDate;
    $item['LastEdited'] = $currentDate;
}

Thanks 3dgoo. This is what I am after. – Wai Yan Hein Jul 25 '19 at 09:56 — Wai Yan Hein, Jul 25 '19 at 09:56

score 1 · Answer 2 · answered Jul 23 '19 at 07:48

It depends. Of course you can insert the data directly to DB or use soemthing like SQLInsert, as DataObjects rely on the related database data. This only works well if you don't have any magic in your dataobject that e.g. checks for duplicate URLs, versioning etc. If you need to add data to more tables (e.g. for a subclass of your dataobject), it becomes much more complicated.

I only once had problems with the ORM import on my server, when I had to import a lot of geospacial data. Normally some houndred objects shouldn't matter that much.

SilverStripe create / insert bulk records

2 Answers2