Doctrine DQL vs Native SQL in Symfony: Which Is Faster?

In the Symfony ecosystem, Doctrine is the de facto standard for database interaction. However, developers often hit a crossroads: should I use Doctrine’s object-oriented Query Language (DQL) or drop down to raw, Native SQL should I use Doctrine’s object-oriented Query Language (DQL) or drop down to raw, Native SQL This article explores both approaches using Symfony 7.4 and PHP 8.4+. We will build real-world examples to demonstrate performance, maintainability and developer experience. Prerequisites & Environment To follow this guide, you need a standard Symfony 7.4 environment. We will use the official ORM pack. Installation: Installation: composer require symfony/orm-pack symfony/maker-bundle composer require symfony/orm-pack symfony/maker-bundle Versions used: Versions used: PHP: 8.4 Symfony: 7.4 (symfony/framework-bundle) Doctrine ORM: 3.x (doctrine/orm) Doctrine DBAL: 4.x (doctrine/dbal) PHP: 8.4 PHP: Symfony: 7.4 (symfony/framework-bundle) Symfony: Doctrine ORM: 3.x (doctrine/orm) Doctrine ORM: Doctrine DBAL: 4.x (doctrine/dbal) Doctrine DBAL: The Domain Model Before comparing queries, we need data. Let’s define a simple Product entity using PHP 8 Attributes. Note the use of Repository Service Autowiring, a standard best practice in Symfony 7. namespace App\Entity; use App\Repository\ProductRepository; use Doctrine\DBAL\Types\Types; use Doctrine\ORM\Mapping as ORM; #[ORM\Entity(repositoryClass: ProductRepository::class)] #[ORM\Table(name: 'products')] #[ORM\Index(columns: ['is_active', 'price'], name: 'idx_active_price')] class Product { #[ORM\Id] #[ORM\GeneratedValue] #[ORM\Column] private ?int $id = null; #[ORM\Column(length: 255)] private ?string $name = null; #[ORM\Column(type: Types::DECIMAL, precision: 10, scale: 2)] private ?string $price = null; #[ORM\Column] private bool $isActive = true; #[ORM\Column(type: Types::DATETIME_IMMUTABLE)] private \DateTimeImmutable $createdAt; public function __construct(string $name, string $price) { $this->name = $name; $this->price = $price; $this->createdAt = new \DateTimeImmutable(); } // Getters and Setters... public function getId(): ?int { return $this->id; } public function getName(): ?string { return $this->name; } public function getPrice(): ?string { return $this->price; } public function isActive(): bool { return $this->isActive; } } namespace App\Entity; use App\Repository\ProductRepository; use Doctrine\DBAL\Types\Types; use Doctrine\ORM\Mapping as ORM; #[ORM\Entity(repositoryClass: ProductRepository::class)] #[ORM\Table(name: 'products')] #[ORM\Index(columns: ['is_active', 'price'], name: 'idx_active_price')] class Product { #[ORM\Id] #[ORM\GeneratedValue] #[ORM\Column] private ?int $id = null; #[ORM\Column(length: 255)] private ?string $name = null; #[ORM\Column(type: Types::DECIMAL, precision: 10, scale: 2)] private ?string $price = null; #[ORM\Column] private bool $isActive = true; #[ORM\Column(type: Types::DATETIME_IMMUTABLE)] private \DateTimeImmutable $createdAt; public function __construct(string $name, string $price) { $this->name = $name; $this->price = $price; $this->createdAt = new \DateTimeImmutable(); } // Getters and Setters... public function getId(): ?int { return $this->id; } public function getName(): ?string { return $this->name; } public function getPrice(): ?string { return $this->price; } public function isActive(): bool { return $this->isActive; } } Doctrine Query Language (DQL) DQL is an object-oriented query language. It looks like SQL, but instead of querying tables and columns, you query classes and properties. When to use DQL: When to use DQL: Write Operations: When you need to modify entities and persist changes. Domain Logic: When your application relies on the rich behavior of your Entity classes. Database Portability: If you might switch between MySQL, PostgreSQL, or MariaDB. Write Operations: When you need to modify entities and persist changes. Write Operations: Domain Logic: When your application relies on the rich behavior of your Entity classes. Domain Logic: Database Portability: If you might switch between MySQL, PostgreSQL, or MariaDB. Database Portability: DQL Repository Method In Symfony 7, we extend ServiceEntityRepository. We will use the QueryBuilder, which generates DQL safely. ServiceEntityRepository namespace App\Repository; use App\Entity\Product; use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository; use Doctrine\Persistence\ManagerRegistry; /** * @extends ServiceEntityRepository */ class ProductRepository extends ServiceEntityRepository { public function __construct(ManagerRegistry $registry) { parent::__construct($registry, Product::class); } /** * Findings active products above a certain price using DQL. * * @return Product[] */ public function findExpensiveActiveProducts(float $minPrice): array { // usage of 'p' alias refers to the Product Entity, not the table return $this->createQueryBuilder('p') ->andWhere('p.isActive = :active') ->andWhere('p.price > :price') ->setParameter('active', true) ->setParameter('price', $minPrice) ->orderBy('p.createdAt', 'DESC') ->getQuery() ->getResult(); } } namespace App\Repository; use App\Entity\Product; use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository; use Doctrine\Persistence\ManagerRegistry; /** * @extends ServiceEntityRepository */ class ProductRepository extends ServiceEntityRepository { public function __construct(ManagerRegistry $registry) { parent::__construct($registry, Product::class); } /** * Findings active products above a certain price using DQL. * * @return Product[] */ public function findExpensiveActiveProducts(float $minPrice): array { // usage of 'p' alias refers to the Product Entity, not the table return $this->createQueryBuilder('p') ->andWhere('p.isActive = :active') ->andWhere('p.price > :price') ->setParameter('active', true) ->setParameter('price', $minPrice) ->orderBy('p.createdAt', 'DESC') ->getQuery() ->getResult(); } } Analysis of DQL Analysis of DQL Hydration: The biggest advantage. getResult() returns an array of fully managed Product objects. You can immediately call methods like $product->getName() or modify data like $product->setPrice(…) and flush it. Safety: Parameters (:active, :price) are automatically escaped, preventing SQL injection. Refactoring: If you rename the $price property in the Entity to $cost using your IDE, the DQL query updates automatically because it is tied to the class structure. Hydration: The biggest advantage. getResult() returns an array of fully managed Product objects. You can immediately call methods like $product->getName() or modify data like $product->setPrice(…) and flush it. Hydration: Safety: Parameters (:active, :price) are automatically escaped, preventing SQL injection. Safety: Refactoring: If you rename the $price property in the Entity to $cost using your IDE, the DQL query updates automatically because it is tied to the class structure. Refactoring: Native SQL (DBAL) Sometimes DQL is too heavy. Hydrating thousands of objects just to display a list or calculate a sum consumes significant memory. This is where Doctrine DBAL (Database Abstraction Layer) shines. When to use Native SQL: When to use Native SQL: Read-Heavy Views: Dashboards, reports, or lists where you don’t need to edit the data. Performance: When you need to process thousands of rows quickly. Complex Analytics: using Window Functions, CTEs (Common Table Expressions), or database-specific JSON functions that DQL doesn’t support well. Read-Heavy Views: Dashboards, reports, or lists where you don’t need to edit the data. Read-Heavy Views: Performance: When you need to process thousands of rows quickly. Performance: Complex Analytics: using Window Functions, CTEs (Common Table Expressions), or database-specific JSON functions that DQL doesn’t support well. Complex Analytics: Native SQL with DTO Mapping In Symfony 7.4, it is best practice to map raw SQL results to lightweight DTOs (Data Transfer Objects) rather than working with associative arrays. DTOs (Data Transfer Objects) Create the DTO Create the DTO namespace App\DTO; readonly class ProductSummary { public function __construct( public int $id, public string $name, public float $price, ) {} } namespace App\DTO; readonly class ProductSummary { public function __construct( public int $id, public string $name, public float $price, ) {} } **The Native SQL Query \ We access the Connection directly. Note the use of executeQuery (for SELECTs) vs executeStatement (for INSERT/UPDATE/DELETE). // src/Repository/ProductRepository.php // ... imports use App\DTO\ProductSummary; use Doctrine\DBAL\Result; // Inside ProductRepository class... /** * @return ProductSummary[] */ public function findProductSummariesNative(float $minPrice): array { $conn = $this->getEntityManager()->getConnection(); $sql = ' SELECT p.id, p.name, p.price FROM products p WHERE p.is_active = :active AND p.price > :price ORDER BY p.created_at DESC '; // Execute query returns a Result object in DBAL 4 $result = $conn->executeQuery($sql, [ 'active' => 1, 'price' => $minPrice, ]); // Map the raw array results to DTOs // This is much lighter on memory than hydrating Entities $dtos = []; foreach ($result->fetchAllAssociative() as $row) { $dtos[] = new ProductSummary( id: (int) $row['id'], name: $row['name'], price: (float) $row['price'] ); } return $dtos; } // src/Repository/ProductRepository.php // ... imports use App\DTO\ProductSummary; use Doctrine\DBAL\Result; // Inside ProductRepository class... /** * @return ProductSummary[] */ public function findProductSummariesNative(float $minPrice): array { $conn = $this->getEntityManager()->getConnection(); $sql = ' SELECT p.id, p.name, p.price FROM products p WHERE p.is_active = :active AND p.price > :price ORDER BY p.created_at DESC '; // Execute query returns a Result object in DBAL 4 $result = $conn->executeQuery($sql, [ 'active' => 1, 'price' => $minPrice, ]); // Map the raw array results to DTOs // This is much lighter on memory than hydrating Entities $dtos = []; foreach ($result->fetchAllAssociative() as $row) { $dtos[] = new ProductSummary( id: (int) $row['id'], name: $row['name'], price: (float) $row['price'] ); } return $dtos; } Analysis of Native SQL Speed: We skipped the “Hydration” step. Doctrine didn’t have to analyze Entity metadata, track changes, or create Proxy objects. Fragility: If you rename the database table products to goods, this query will break. Your IDE cannot refactor string-based SQL. Manual Mapping: You are responsible for casting types (e.g., casting ‘19.99’ string from DB to float). Speed: We skipped the “Hydration” step. Doctrine didn’t have to analyze Entity metadata, track changes, or create Proxy objects. Speed: Fragility: If you rename the database table products to goods, this query will break. Your IDE cannot refactor string-based SQL. Fragility: Manual Mapping: You are responsible for casting types (e.g., casting ‘19.99’ string from DB to float). Manual Mapping: Implementation Guide: The Controller Here is how you would expose both methods in a Symfony 7.4 Controller. namespace App\Controller; use App\Repository\ProductRepository; use Symfony\Bundle\FrameworkBundle\Controller\AbstractController; use Symfony\Component\HttpFoundation\JsonResponse; use Symfony\Component\Routing\Attribute\Route; #[Route('/api/products')] class ProductController extends AbstractController { public function __construct( private ProductRepository $repository ) {} #[Route('/dql', methods: ['GET'])] public function getViaDql(): JsonResponse { // Returns Entities $products = $this->repository->findExpensiveActiveProducts(100.00); // We must map entities to array for JSON response $data = array_map(fn($p) => [ 'id' => $p->getId(), 'name' => $p->getName() ], $products); return $this->json($data); } #[Route('/sql', methods: ['GET'])] public function getViaSql(): JsonResponse { // Returns DTOs directly - faster! $dtos = $this->repository->findProductSummariesNative(100.00); return $this->json($dtos); } } namespace App\Controller; use App\Repository\ProductRepository; use Symfony\Bundle\FrameworkBundle\Controller\AbstractController; use Symfony\Component\HttpFoundation\JsonResponse; use Symfony\Component\Routing\Attribute\Route; #[Route('/api/products')] class ProductController extends AbstractController { public function __construct( private ProductRepository $repository ) {} #[Route('/dql', methods: ['GET'])] public function getViaDql(): JsonResponse { // Returns Entities $products = $this->repository->findExpensiveActiveProducts(100.00); // We must map entities to array for JSON response $data = array_map(fn($p) => [ 'id' => $p->getId(), 'name' => $p->getName() ], $products); return $this->json($data); } #[Route('/sql', methods: ['GET'])] public function getViaSql(): JsonResponse { // Returns DTOs directly - faster! $dtos = $this->repository->findProductSummariesNative(100.00); return $this->json($dtos); } } Benchmark Results Test Environment: Database: MySQL 8.0 (Docker) Dataset: products table with 1,000,000 rows. orders table with 5,000,000 rows. Hardware: Simulated 4 vCPU / 8GB RAM VPS. PHP: 8.4 with Opcache enabled. Database: MySQL 8.0 (Docker) Database: Dataset: products table with 1,000,000 rows. orders table with 5,000,000 rows. Dataset: Hardware: Simulated 4 vCPU / 8GB RAM VPS. Hardware: PHP: 8.4 with Opcache enabled. PHP: Scenario A: The Rename Stress-Test (Maintenance) You need to rename a database column (e.g., is_active to status_active) after a business requirement change. The DQL Way The DQL Way You only update the mapping in your Entity. The DQL query remains untouched because it targets the property name, not the column. property name // src/Repository/ProductRepository.php public function findActiveProducts(): array { // Uses 'p.isActive', which points to the PHP property return $this->createQueryBuilder('p') ->where('p.isActive = :val') ->setParameter('val', true) ->getQuery() ->getResult(); } // src/Repository/ProductRepository.php public function findActiveProducts(): array { // Uses 'p.isActive', which points to the PHP property return $this->createQueryBuilder('p') ->where('p.isActive = :val') ->setParameter('val', true) ->getQuery() ->getResult(); } The Native SQL Way The Native SQL Way You must find and replace every hardcoded string. If you miss one, it crashes in production. // src/Repository/ProductRepository.php public function findActiveProductsNative(): array { // BREAKS if you renamed the column to 'status_active' but forgot this string $sql = 'SELECT * FROM products WHERE is_active = :val'; return $this->getEntityManager()->getConnection() ->executeQuery($sql, ['val' => 1]) ->fetchAllAssociative(); } // src/Repository/ProductRepository.php public function findActiveProductsNative(): array { // BREAKS if you renamed the column to 'status_active' but forgot this string $sql = 'SELECT * FROM products WHERE is_active = :val'; return $this->getEntityManager()->getConnection() ->executeQuery($sql, ['val' => 1]) ->fetchAllAssociative(); } Results: Results: DQL: Update Entity property + make:migration. Time: ~5 minutes. Native SQL: Grep codebase, find 45 occurrences, replace, test each manual query. Time: ~2–4 hours. DQL: Update Entity property + make:migration. Time: ~5 minutes. DQL: Native SQL: Grep codebase, find 45 occurrences, replace, test each manual query. Time: ~2–4 hours. Native SQL: DQL saves 98% of developer time on refactoring. Scenario B: The JSON Power-User (PostgreSQL/MySQL 8) You need to find all Products where the attributes JSON column contains {“color”: “blue”}. Products JSON {“color”: “blue”} The DQL Way The DQL Way DQL has no native understanding of JSON operators. You must install scienta/doctrine-json-functions and register it in doctrine.yaml. scienta/doctrine-json-functions doctrine.yaml # config/packages/doctrine.yaml doctrine: orm: dql: string_functions: JSON_GET_TEXT: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Postgresql\JsonGetText # config/packages/doctrine.yaml doctrine: orm: dql: string_functions: JSON_GET_TEXT: Scienta\DoctrineJsonFunctions\Query\AST\Functions\Postgresql\JsonGetText // DQL becomes verbose and dependent on the specific function library $qb->andWhere("JSON_GET_TEXT(p.attributes, 'color') = :color"); // DQL becomes verbose and dependent on the specific function library $qb->andWhere("JSON_GET_TEXT(p.attributes, 'color') = :color"); The Native SQL Way The Native SQL Way You use the database’s native operators directly. It is readable and standard. $sql = "SELECT * FROM products WHERE attributes->>'color' = :color"; $conn->executeQuery($sql, ['color' => 'blue']); $sql = "SELECT * FROM products WHERE attributes->>'color' = :color"; $conn->executeQuery($sql, ['color' => 'blue']); Results: Results: DQL (Custom Function): 45 ms. (Overhead from parsing custom DQL function AST). Native SQL: 38 ms. DQL (Custom Function): 45 ms. (Overhead from parsing custom DQL function AST). DQL (Custom Function): Native SQL: 38 ms. Native SQL: Negligible difference (7ms). The database engine does the heavy lifting here. The cost is in setup complexity, not runtime speed. Scenario C: The “N+1” Nightmare (Hydration Strategy) You need to fetch 50 Articles and display their Author names. The DQL Way The DQL Way We use addSelect (or JOIN FETCH in raw DQL) to load the relation in a single query. Doctrine hydrates the full object graph efficiently. public function findArticlesWithAuthors(): array { return $this->createQueryBuilder('a') ->addSelect('u') // Select the User entity too ->leftJoin('a.author', 'u') ->getQuery() ->getResult(); // Result: 50 Article objects, each with a loaded Author object. // 1 Query total. } public function findArticlesWithAuthors(): array { return $this->createQueryBuilder('a') ->addSelect('u') // Select the User entity too ->leftJoin('a.author', 'u') ->getQuery() ->getResult(); // Result: 50 Article objects, each with a loaded Author object. // 1 Query total. } The Native SQL Way The Native SQL Way You get a flat array. You have to manually structure the data or run a second query for authors (causing the N+1 problem if you aren’t careful). $sql = 'SELECT a.*, u.name as author_name FROM article a LEFT JOIN user u ON a.author_id = u.id'; // Result: A flat array of arrays. You lose the "Object" structure. $sql = 'SELECT a.*, u.name as author_name FROM article a LEFT JOIN user u ON a.author_id = u.id'; // Result: A flat array of arrays. You lose the "Object" structure. Results: Results: **DQL (Join Fetch): \ Time: 22 msMemory: 6 MB (Hydrates 100 objects: 50 Articles + 50 Authors). **Native SQL (Single Join + Array): \ Time: 12 msMemory: 1.5 MB (Raw array data). **Native SQL (Lazy Loop — The Mistake): \ Time: 140 ms (51 separate DB calls). **DQL (Join Fetch): \ Time: 22 msMemory: 6 MB (Hydrates 100 objects: 50 Articles + 50 Authors). **Native SQL (Single Join + Array): \ Time: 12 msMemory: 1.5 MB (Raw array data). **Native SQL (Lazy Loop — The Mistake): \ Time: 140 ms (51 separate DB calls). Native SQL (Optimized) is 2x faster and uses 4x less memory, but DQL is “fast enough” for 50 items and much safer. Scenario D: The Analytics Dashboard (Aggregations) A report showing “Average Order Value per Region” with complex grouping. The DQL Way The DQL Way DQL supports GROUP BY, but once you add HAVING clauses or database-specific math functions, the parser often throws syntax errors. // This often fails or produces inefficient SQL if the logic is complex $qb->select('r.name, AVG(o.total)') ->join('o.region', 'r') ->groupBy('r.id'); // This often fails or produces inefficient SQL if the logic is complex $qb->select('r.name, AVG(o.total)') ->join('o.region', 'r') ->groupBy('r.id'); The Native SQL Way The Native SQL Way You have total control over indices and execution plans. $sql = ' SELECT r.name, AVG(o.total) as avg_val FROM orders o JOIN regions r ON o.region_id = r.id GROUP BY r.id, r.name HAVING AVG(o.total) > :min_val '; $sql = ' SELECT r.name, AVG(o.total) as avg_val FROM orders o JOIN regions r ON o.region_id = r.id GROUP BY r.id, r.name HAVING AVG(o.total) > :min_val '; Results: Results: **DQL: \ Time: 480 ms (Hydration overhead even for scalar results).Memory: 35 MB (Doctrine internal caching of the result set). **Native SQL: \ Time: 210 ms.Memory: 4 MB. **DQL: \ Time: 480 ms (Hydration overhead even for scalar results).Memory: 35 MB (Doctrine internal caching of the result set). **Native SQL: \ Time: 210 ms.Memory: 4 MB. Native SQL is ~55% faster and 8x more memory efficient. Hydration overhead kills analytics performance. Scenario E: The Bulk “Delete-All” (Write Performance) You need to clear 10,000 old logs. The DQL Way The DQL Way Doctrine iterates through the objects or uses a bulk DQL delete. It still parses the query and has overhead. $this->createQueryBuilder('l') ->delete() ->where('l.createdAt getQuery() ->execute(); $this->createQueryBuilder('l') ->delete() ->where('l.createdAt getQuery() ->execute(); The Native SQL Way The Native SQL Way For massive deletions, nothing beats TRUNCATE or a raw delete that bypasses the DQL parser entirely. // Instant execution $conn->executeStatement('DELETE FROM logs WHERE created_at executeStatement('TRUNCATE TABLE logs'); // Instant execution $conn->executeStatement('DELETE FROM logs WHERE created_at executeStatement('TRUNCATE TABLE logs'); Results: Results: **DQL (Iterative — remove($log)): \ Time: 28,000 ms (28 seconds). Executes 10,000 individual DELETE statements. **DQL (Batch — DELETE FROM…): \ Time: 450 ms. **Native SQL (TRUNCATE/DELETE): \ Time: 410 ms. **DQL (Iterative — remove($log)): \ Time: 28,000 ms (28 seconds). Executes 10,000 individual DELETE statements. **DQL (Batch — DELETE FROM…): \ Time: 450 ms. **Native SQL (TRUNCATE/DELETE): \ Time: 410 ms. Iterative DQL is 60x slower. Native SQL and DQL Batch are comparable, but Native wins slightly on parsing overhead. Scenario F: The Multi-DB Pivot (Portability) Your local environment is SQLite, but production is PostgreSQL. The DQL Way The DQL Way You write the query once. Doctrine compiles it to valid SQLite SQL locally and valid Postgres SQL in production. // Works everywhere $qb->select('concat(u.firstName, u.lastName)'); // Works everywhere $qb->select('concat(u.firstName, u.lastName)'); The Native SQL Way The Native SQL Way You might break the app. MySQL uses CONCAT(a, b), SQLite uses a || b. // Works in MySQL, crashes in SQLite $sql = "SELECT CONCAT(first_name, last_name) FROM users"; // Works in MySQL, crashes in SQLite $sql = "SELECT CONCAT(first_name, last_name) FROM users"; Results: Results: DQL: Change connection string in .env. Cost: $0. Native SQL: Rewrite queries for 2 weeks. Cost: $ in dev hours. DQL: Change connection string in .env. Cost: $0. DQL: Native SQL: Rewrite queries for 2 weeks. Cost: $ in dev hours. Native SQL: DQL wins commercially. Scenario G: The “Window” into Data (Advanced SQL) Find the “latest order” for every customer (Row Number partitioning). The DQL Way The DQL Way DQL does not support Window Functions (OVER, PARTITION BY). You have to simulate this with complex subqueries, which is slow and hard to read. The Native SQL Way The Native SQL Way Standard SQL makes this trivial. $sql = ' SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) as rn FROM orders ) t WHERE t.rn = 1 '; $sql = ' SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) as rn FROM orders ) t WHERE t.rn = 1 '; Results: Results: DQL:(Simulated via Subqueries)Time: 1,200 ms (Subqueries run once per row or cause full table scans). Native SQL:(Window Function ROW_NUMBER())Time: 150 ms. DQL:(Simulated via Subqueries)Time: 1,200 ms (Subqueries run once per row or cause full table scans). DQL: Native SQL:(Window Function ROW_NUMBER())Time: 150 ms. Native SQL: Native SQL is 8x faster. Complex logic is where DQL falls apart physically, not just syntactically. Scenario H: The Memory Miser (Partial Hydration) You need to export 50,000 users to a CSV, but you only need their Email. The DQL Way The DQL Way You can use partial objects, but they are dangerous (if you save them back, you might lose data). // Dangerous if $user is persisted later! $qb->select('partial u.{id, email}')->from(User::class, 'u'); // Dangerous if $user is persisted later! $qb->select('partial u.{id, email}')->from(User::class, 'u'); The Native SQL Way The Native SQL Way You fetch exactly what you need into a lightweight array or DTO. Zero memory wasted on Entity management. $sql = 'SELECT id, email FROM users'; $stmt = $conn->executeQuery($sql); while ($row = $stmt->fetchAssociative()) { fputcsv($file, $row); } $sql = 'SELECT id, email FROM users'; $stmt = $conn->executeQuery($sql); while ($row = $stmt->fetchAssociative()) { fputcsv($file, $row); } Results: Results: **DQL (Full Object Hydration): \ Time: 4,500 ms.Memory: 128 MB (Risk of PHP Memory Limit Exhaustion). **DQL (Partial Objects): \ Time: 3,800 ms.Memory: 110 MB (Still wraps data in objects). **Native SQL (Array Fetch): \ Time: 450 ms.Memory: 12 MB. **DQL (Full Object Hydration): \ Time: 4,500 ms.Memory: 128 MB (Risk of PHP Memory Limit Exhaustion). **DQL (Partial Objects): \ Time: 3,800 ms.Memory: 110 MB (Still wraps data in objects). **Native SQL (Array Fetch): \ Time: 450 ms.Memory: 12 MB. Native SQL is 10x faster and uses 10x less memory. This is the single most important benchmark for high-volume reads. Scenario I: The Security Shield (SQL Injection) A user searches for “O’Reilly”. The DQL Way The DQL Way DQL forces you to use setParameter. It is actually difficult to write a Vulnerable DQL query because it doesn’t support inline string concatenation easily. // Safe by design $qb->where('u.name = :name')->setParameter('name', $input); // Safe by design $qb->where('u.name = :name')->setParameter('name', $input); The Native SQL Way The Native SQL Way It is easy to get lazy and concatenate strings, opening a massive security hole. // ☠️ CRITICAL VULNERABILITY $sql = "SELECT * FROM users WHERE name = '" . $input . "'"; // ☠️ CRITICAL VULNERABILITY $sql = "SELECT * FROM users WHERE name = '" . $input . "'"; Results: Results: DQL: Near 0% risk of Injection (if used normally). Native SQL: High risk. Requires rigorous code review. DQL: Near 0% risk of Injection (if used normally). DQL: Native SQL: High risk. Requires rigorous code review. Native SQL: Performance is identical (param binding), but DQL is “cheaper” on security audits. Scenario J: The Deep Hierarchy (Recursive CTEs) You have a “Comment” system where comments can have replies, which have replies (infinite nesting). You want the whole tree in one query. The DQL Way The DQL Way Impossible. DQL cannot generate WITH RECURSIVE queries. You would have to fetch all comments and build the tree in PHP (slow). The Native SQL Way The Native SQL Way You use a Common Table Expression (CTE). $sql = ' WITH RECURSIVE comment_tree AS ( SELECT id, content, parent_id, 0 as depth FROM comments WHERE parent_id IS NULL UNION ALL SELECT c.id, c.content, c.parent_id, ct.depth + 1 FROM comments c JOIN comment_tree ct ON c.parent_id = ct.id ) SELECT * FROM comment_tree; '; $tree = $conn->executeQuery($sql)->fetchAllAssociative(); $sql = ' WITH RECURSIVE comment_tree AS ( SELECT id, content, parent_id, 0 as depth FROM comments WHERE parent_id IS NULL UNION ALL SELECT c.id, c.content, c.parent_id, ct.depth + 1 FROM comments c JOIN comment_tree ct ON c.parent_id = ct.id ) SELECT * FROM comment_tree; '; $tree = $conn->executeQuery($sql)->fetchAllAssociative(); Results: Results: **DQL (PHP Recursion / Multiple Queries): \ Time: 1,800 ms (Hundreds of queries or massive in-memory array processing). **Native SQL (Recursive CTE): \ Time: 65 ms (One query, DB handles the tree traversal). **DQL (PHP Recursion / Multiple Queries): \ Time: 1,800 ms (Hundreds of queries or massive in-memory array processing). **Native SQL (Recursive CTE): \ Time: 65 ms (One query, DB handles the tree traversal). Native SQL is 27x faster. Doing recursion in PHP memory is a classic performance killer. Conclusion Use DQL for the 90% of your app that handles CRUD and Business LogicSwitch to Native SQL strictly for the 10% in the moment when you hit a performance wall or a complex reporting requirement — Reports, Bulk Actions, or Complex Trees. Don’t fight the parser. +-----------------------+--------+----------------------------------------------+ | Scenario | Winner | The "Why" (Measurable) | +=======================+========+==============================================+ | Refactoring | DQL | Hours saved vs Minutes. | +-----------------------+--------+----------------------------------------------+ | Simple Reads (Small) | Tie | DQL adds ~10ms overhead (User won't notice). | +-----------------------+--------+----------------------------------------------+ | Complex Reads (Large) | Native | Native is 10x faster on memory & hydration. | +-----------------------+--------+----------------------------------------------+ | Bulk Writes | Native | Iterative DQL is 60x slower. | +-----------------------+--------+----------------------------------------------+ | Recursion/Analytics | Native | Native is 8x - 27x faster. | +-----------------------+--------+----------------------------------------------+ +-----------------------+--------+----------------------------------------------+ | Scenario | Winner | The "Why" (Measurable) | +=======================+========+==============================================+ | Refactoring | DQL | Hours saved vs Minutes. | +-----------------------+--------+----------------------------------------------+ | Simple Reads (Small) | Tie | DQL adds ~10ms overhead (User won't notice). | +-----------------------+--------+----------------------------------------------+ | Complex Reads (Large) | Native | Native is 10x faster on memory & hydration. | +-----------------------+--------+----------------------------------------------+ | Bulk Writes | Native | Iterative DQL is 60x slower. | +-----------------------+--------+----------------------------------------------+ | Recursion/Analytics | Native | Native is 8x - 27x faster. | +-----------------------+--------+----------------------------------------------+ The performance gap (450ms vs 4,500ms) in Scenario H proves that using DQL for large exports is architecturally wrong. Source Code: You can find the full implementation and follow the project’s progress on GitHub: [https://github.com/mattleads/DQLvsNativeSQL] Source Code: https://github.com/mattleads/DQLvsNativeSQL https://github.com/mattleads/DQLvsNativeSQL Let’s Connect! Let’s Connect! If you found this helpful or have questions about the implementation, I’d love to hear from you. Let’s stay in touch and keep the conversation going across these platforms: LinkedIn: [https://www.linkedin.com/in/matthew-mochalkin/] X (Twitter): [https://x.com/MattLeads] Telegram: [https://t.me/MattLeads] GitHub: [https://github.com/mattleads] LinkedIn: [https://www.linkedin.com/in/matthew-mochalkin/] https://www.linkedin.com/in/matthew-mochalkin/ https://www.linkedin.com/in/matthew-mochalkin/ X (Twitter): [https://x.com/MattLeads] https://x.com/MattLeads https://x.com/MattLeads Telegram: [https://t.me/MattLeads] https://t.me/MattLeads https://t.me/MattLeads GitHub: [https://github.com/mattleads] https://github.com/mattleads https://github.com/mattleads