Optimizing ORM queries for D7 1C-Bitrix

D7 ORM Query Optimization in 1C-Bitrix

D7 ORM is the object-relational mapper of new Bitrix core. Convenient for development, but can generate inefficient queries if you don't understand its behavior. A query written in 5 minutes might do SELECT * with three unnecessary JOINs — on large catalog this is 500 ms instead of 10 ms.

How D7 ORM Builds SQL

ORM reads table descriptions from the entity class's getMap() method. Relations (references) are described there too. When you write:

\Bitrix\Iblock\ElementTable::getList([
    'select' => ['ID', 'NAME', 'IBLOCK.NAME'],
]);

ORM automatically adds LEFT JOIN b_iblock ON b_iblock.ID = b_iblock_element.IBLOCK_ID — because you requested field via dot notation. Convenient, but if you don't know IBLOCK_ID and it's always the same — JOIN is pointless.

View the resulting SQL via:

$query = \Bitrix\Iblock\ElementTable::query();
$query->setSelect(['ID', 'NAME']);
$query->setFilter(['IBLOCK_ID' => 5]);
echo $query->getQuery();  // outputs formed SQL

Main Problem Sources

Extra fields in select. If you don't pass select, ORM selects all fields from getMap(). For ElementTable this is 20+ fields including DETAIL_TEXT (type Text — potentially megabytes). On 100 elements, megabytes of data PHP receives from MySQL then discards.

Rule: always explicitly specify select:

$result = \Bitrix\Iblock\ElementTable::getList([
    'select' => ['ID', 'NAME', 'PREVIEW_PICTURE_ID', 'DETAIL_PAGE_URL'],
    'filter' => ['=IBLOCK_ID' => 5, '=ACTIVE' => 'Y'],
    'order'  => ['SORT' => 'ASC'],
    'limit'  => 20,
]);

Automatic JOINs via references. When select contains field via dot notation (SECTION.NAME, IBLOCK.SORT) — ORM adds JOIN. Check via getQuery() — possibly needed data already in main table or can get via separate query.

N+1 via fetchObject(). D7 supports object model (fetchObject()). On lazy relation access, object makes additional query on each access:

// This is N+1 — each ->getSection() makes new query
foreach ($elements->getIterator() as $element) {
    echo $element->getSection()->getName();  // query per element!
}

// Right — load sections immediately via select
$result = \Bitrix\Iblock\ElementTable::getList([
    'select' => ['ID', 'NAME', 'IBLOCK_SECTION_ID', 'SECTION_' => 'IBLOCK_SECTION.NAME'],
    'filter' => ['=IBLOCK_ID' => 5],
]);

ORM Query-Level Caching

D7 supports built-in cache via cache parameter:

$result = \Bitrix\Iblock\ElementTable::getList([
    'select' => ['ID', 'NAME', 'PREVIEW_PICTURE_ID'],
    'filter' => ['=IBLOCK_ID' => 5, '=ACTIVE' => 'Y'],
    'cache'  => [
        'ttl'   => 3600,   // lifetime in seconds
        'cache_joins' => true,  // cache with JOINs
    ],
]);

Cache stored in file system (or memcache/redis if configured). Auto-invalidates on data change via ORM — if data changes via direct SQL, cache doesn't reset.

Important: cache_joins => true needed if select has fields from related tables. Without it, cache works incorrectly with JOINs.

Runtime Fields and Subqueries

D7 allows adding computed fields to query via runtime:

use Bitrix\Main\Entity;

$result = \Bitrix\Iblock\ElementTable::getList([
    'select' => ['ID', 'NAME', 'PRICE_VALUE'],
    'runtime' => [
        new Entity\ReferenceField(
            'PRICE',
            \Bitrix\Catalog\PriceTable::class,
            ['=this.ID' => 'ref.PRODUCT_ID', '=ref.CATALOG_GROUP_ID' => new Entity\ExpressionField('PTYPE', '1')],
            ['join_type' => 'LEFT']
        ),
        new Entity\ExpressionField('PRICE_VALUE', '%s', ['PRICE.PRICE']),
    ],
    'filter' => ['=IBLOCK_ID' => 5],
]);

Via ExpressionField can compute values on MySQL side without PHP processing: ROUND(%s * 1.2, 2) for price with margin calculation.

Working with Large Selections

When processing many records (import, export, bulk update), don't load everything in memory:

// Bad: all 100,000 rows in PHP memory
$result = SomeTable::getList(['select' => ['ID', 'NAME']]);
$all = $result->fetchAll();

// Good: process in batches of 500
$offset = 0;
$limit  = 500;
do {
    $result = SomeTable::getList([
        'select' => ['ID', 'NAME'],
        'limit'  => $limit,
        'offset' => $offset,
        'order'  => ['ID' => 'ASC'],  // stable sort for pagination
    ]);
    $rows = $result->fetchAll();
    foreach ($rows as $row) {
        // processing
    }
    $offset += $limit;
} while (count($rows) === $limit);

Alternative — cursor pagination by ID: filter => ['>ID' => $lastId], more efficient than OFFSET at large values.

Diagnostics via SqlTracker

For analyzing ORM queries in real context:

$tracker = \Bitrix\Main\Diag\SqlTracker::getInstance();
$tracker->start();

// ... your code with ORM queries ...

$tracker->stop();
$queries = $tracker->getQueries();
usort($queries, fn($a, $b) => $b->getTime() <=> $a->getTime());

foreach (array_slice($queries, 0, 10) as $q) {
    echo round($q->getTime() * 1000, 1) . ' ms: ' . substr($q->getSql(), 0, 200) . PHP_EOL;
}

Work Timeline

D7 ORM is tool balancing development convenience with performance. For readable queries without critical speed requirements — good. For hot paths with thousands calls per minute — need to control every generated SQL.