Code Wars: ORM or Raw SQL? – The Interpreter at the Table

In 2023 I wrote my own ORM on this blog (in Turkish). A year later, in the N+1 article, I put JOINs first as "the most fundamental way" to fix it. This week I sat both of them down at the same table and measured. Both articles turned out to be a little too sure of themselves.

This round of Code Wars settles Eloquent vs raw SQL with a counter instead of a side. In one corner, Laravel's ORM; in the other, plain SQL through PDO. Same MariaDB, same data, four scenarios.

The metaphor is an interpreter. The swords are sheathed and two parties are at the negotiating table: your PHP code, which speaks in objects, and the database, which speaks in tables. The ORM is the interpreter between them. Writing raw SQL means speaking the other side's language yourself. The short version of the results: you lose if you use the interpreter badly, and you lose if you speak the language badly.

Setting the Table#

All measurements ran in my own sandbox: PHP 8.3.6 CLI, MariaDB 10.11.14, Eloquent 13.38.0 (standalone through Capsule, without the rest of Laravel), connected over a Unix socket. The data is 2,000 customers and 100,000 orders. Identifiers are Turkish: musteriler is customers, siparisler is orders, tutar is amount, olusturma is created-at.

CREATE TABLE musteriler (
  id INT UNSIGNED PRIMARY KEY,
  ad VARCHAR(80) NOT NULL,
  sehir VARCHAR(40) NOT NULL
) ENGINE=InnoDB;
​
CREATE TABLE siparisler (
  id INT UNSIGNED PRIMARY KEY,
  musteri_id INT UNSIGNED NOT NULL,
  durum VARCHAR(20) NOT NULL,
  tutar DECIMAL(10,2) NOT NULL,
  olusturma DATETIME NOT NULL,
  KEY (musteri_id),
  KEY (olusturma),
  FOREIGN KEY (musteri_id) REFERENCES musteriler(id)
) ENGINE=InnoDB;

Every scenario ran 5 to 30 times after a warm-up pass; times in the tables are medians. The milliseconds moved a little across three separate runs, which is why some cells show a range. Query counts come from Eloquent's query log and from the counter further down.

Technical detail: over a local socket, the network cost of a query is close to zero. If your application and database live on different machines, every query adds a round trip. The N+1 numbers here are the optimistic version.

Round One: Latest 50 Orders With the Customer Name#

The most ordinary list screen there is.

When the interpreter translates one sentence at a time#

The first row is the story everyone knows. $siparis->musteri->ad looks innocent, but for each order in the loop the interpreter builds a separate sentence: the list query plus 50 customer queries. with() brings the same work down to two queries, one for orders and one for customers with where id in (...).

Hard truth: in the N+1 article we wrote that with() "fetches the data in a single query". The counter says two. Eager loading is not a JOIN; the second query runs with the keys the first one returned.

Turning lazy loading off in development is one line in Eloquent:

Model::preventLazyLoading();

I tried it. On a two-order list, $s->musteri threw Attempted to lazy load [musteri] on model [Siparis] but lazy loading is disabled. On a query returning a single record, nothing was thrown. The source explains why: Builder::hydrate() only sets the flag when more than one row comes back. Fair enough, a single record cannot cause N+1. But if your test fixture has exactly one row, "the tests passed" proves nothing.

When you speak the language badly#

The real surprise is row three. The JOIN I wrote by hand, on the theory that one query always beats two, came out more than 50 times slower than the ORM's two queries. ANALYZE FORMAT=JSON shows what happened: MariaDB read all 2,000 customers first, went down into orders for each of them, put all 100,000 orders into a temporary table and sorted it. Pages touched: 204,137. Meanwhile the olusturma index was sitting right there, and reading the last 50 rows through it costs 118 pages.

Eloquent's join() fell into the same plan. No surprise, it generates the same SQL. Hand the interpreter the wrong sentence and it will translate the wrong sentence perfectly.

The fix is to let orders be picked through their own index first and attach customers afterwards:

SELECT s.id, s.tutar, m.ad
FROM (
  SELECT id, musteri_id, tutar, olusturma
  FROM siparisler
  ORDER BY olusturma DESC
  LIMIT 50
) s
JOIN musteriler m ON m.id = s.musteri_id
ORDER BY s.olusturma DESC;

0.32 ms; 118 pages on orders, 100 on customers. Forcing the join order with STRAIGHT_JOIN produced the same plan, but that hint ties the optimizer's hands permanently; when the data distribution shifts, today's right order can be tomorrow's wrong one. This plan came out of MariaDB 10.11.14 on this data; another version or another distribution may pick a different path. The lesson does not depend on the version. As in the index article, "a single query" is not a virtue until you have read its plan.

Round Two: Loading 10,000 Rows Into Memory#

This is where the interpreter sends the invoice. Building 10,000 model objects from the same rows took more than ten times as long as fetching plain arrays and used three times the memory. toBase() runs the same query without building models and stays close to PDO. lazy() saves memory, not time: 11 queries in chunks of 1,000, never more than a thousand models in memory at once. The Laravel documentation describes cursor() as a single query with one model in memory, and notes in the same place that it cannot eager load relationships.

Fact: the interpreter is not expensive for one sentence. It is expensive for ten thousand.

Round Three: The Reporting Table#

Revenue per city for the third quarter, 31,795 orders in the period.

// Her şeyi tercümana çevirtip hesabı PHP'de yapmak
$ciro = Siparis::with('musteri')
    ->whereBetween('olusturma', ['2026-07-01', '2026-09-30 23:59:59'])
    ->get()
    ->groupBy(fn ($s) => $s->musteri->sehir)
    ->map(fn ($grup) => $grup->sum('tutar'));
​
// Hesabı veritabanına yaptırmak
$ciro = DB::select("
    SELECT m.sehir, SUM(s.tutar) AS ciro
    FROM siparisler s
    JOIN musteriler m ON m.id = s.musteri_id
    WHERE s.olusturma >= '2026-07-01' AND s.olusturma < '2026-10-01'
    GROUP BY m.sehir
");

The first one (everything through the interpreter, totals in PHP): 1,263 ms and 64.2 MiB of extra memory. The second (totals in the database): 146 ms and 0.04 MiB. Both can add numbers up; in one, 31,795 rows cross the wire and become objects, in the other, five rows do.

Verdict: dropping out of the ORM for a report is not a defeat. Eloquent leaves that door open itself with DB::select and selectRaw.

Round Four: Bulk Update#

Moving 1,217 orders created between 1 and 14 January and still sitting in yeni (new) to hazirlaniyor (preparing). Each run was rolled back afterwards.

// Her modeli tek tek kaydetmek
foreach ($hedef->get() as $s) {
    $s->durum = 'hazirlaniyor';
    $s->save();
}
​
// Tek UPDATE
$hedef->update(['durum' => 'hazirlaniyor']);

Saving each model: 383 ms and 1,218 queries. A single UPDATE: 16 ms and one query. Speed is not the only difference. An updating listener I attached to the model ran 1,217 times in the first and not once in the second. The Laravel docs say it plainly: with a mass update, the saving, saved, updating and updated events are not fired, because the models are never retrieved. If your audit log or cache invalidation hangs off those events, the fast path skips it without a word.

Doctrine ORM's documentation is even more candid. The first warning on its batch processing page says an ORM tool is not primarily well-suited for mass inserts, updates or deletions, and that every RDBMS has its own, most effective way of dealing with them. The interpreter's own business card says "don't give me this job".

Two Kinds of Interpreter: Active Record and Data Mapper#

Eloquent and my 2023 Model class are from the same family: Active Record. The object carries the data and saves itself ($musteri->save()). Doctrine ORM is a Data Mapper: entity classes know nothing about the database, a Unit of Work inside the EntityManager collects the changes and writes them on flush(), and an Identity Map hands you the same object when you ask for the same record twice.

I did not install Doctrine for this measurement; every number in the tables belongs to Eloquent.

There is one thing the interpreter really does protect you from: parameter binding. Laravel's query builder documentation says values are bound through PDO, and that it cannot guarantee protection against SQL injection for raw expressions such as DB::raw and whereRaw. My own ORM's find() method pasted its orderby value straight into the query; if that value comes from a user, you do not have an interpreter, you have a guard who left the door open. The rule is the same in raw SQL: values go in through ?, column names and sort direction come from an allow-list.

For the Raw SQL Camp: Put a Counter on the Table#

Writing framework-free PHP does not exempt you from N+1; it just means nobody tells you. When I wrote the same list screen with plain PDO inside a loop, I got 51 queries again. Giving PDO your own statement class and counting every execute() takes forty-odd lines:

<?php
declare(strict_types=1);
​
/** Her execute() çağrısını sayan PDOStatement. */
final class SayacliIfade extends PDOStatement
{
    protected function __construct(private SorguSayaci $sayac) {}
​
    public function execute(?array $params = null): bool
    {
        $this->sayac->kaydet($this->queryString);
        return parent::execute($params);
    }
}
​
/** İstek boyunca çalışan sorguları sayar; aynı kalıp eşiği aşarsa işaretler. */
final class SorguSayaci
{
    /** @var array<string, int> */
    private array $kaliplar = [];
​
    public function __construct(private int $esik = 10) {}
​
    public function bagla(PDO $pdo): void
    {
        $pdo->setAttribute(PDO::ATTR_STATEMENT_CLASS, [SayacliIfade::class, [$this]]);
    }
​
    public function kaydet(string $sql): void
    {
        // Sayıları ve tırnak içini at: "id = 7" ile "id = 8" aynı kalıp sayılsın.
        $kalip = preg_replace(["/'[^']*'/", '/\b\d+\b/'], ['?', '?'], $sql) ?? $sql;
        $this->kaliplar[$kalip] = ($this->kaliplar[$kalip] ?? 0) + 1;
    }
​
    /** @return array<string, int> eşiği aşan kalıplar */
    public function supheliler(): array
    {
        return array_filter($this->kaliplar, fn (int $n): bool => $n > $this->esik);
    }
​
    public function toplam(): int
    {
        return array_sum($this->kaliplar);
    }
}

Usage (SorguSayaci is the query counter, supheliler() returns the suspicious patterns):

$sayac = new SorguSayaci(esik: 10);
$sayac->bagla($pdo);
​
// ... sayfa çalışır ...
​
foreach ($sayac->supheliler() as $kalip => $adet) {
    error_log("$adet kez: $kalip");
}

On the list screen the output was a single line: 50 kez: SELECT ad FROM musteriler WHERE id = ? ("50 times"). Two limits. It only counts statements that are prepared with prepare() and then executed; PDO::query() and exec() do not reach it (I checked). And according to the PHP manual, ATTR_STATEMENT_CLASS cannot be used with persistent connections.

Which Job Goes to Which Table?#

Leaving the Table#

In the Boolean or 1/0 round, the data itself picked the winner. This round, neither the ORM nor SQL did. The fastest row was a single query written after reading its plan; the slowest row was also a query written by hand.

An interpreter translates well, but it does not choose what you say. Knowing the other side's language does not make you fluent on its own either; you still have to hear what you are actually saying.

Sheathe the swords, put the counter on the table.