PDO

Overview: The Challenge of the Million-Row Import Importing data is a deceptive task. It starts simple: read a file, loop through the rows, and save them to a database. But as the row count climbs from hundreds to millions, the "simple" way becomes a catastrophic failure. Memory limits are breached, database connections timeout, and processing times stretch from seconds into hours. This guide explores how to scale a Laravel import from a basic collection-based approach to high-performance methods using PDO and MySQL native features. We will operate under three strict constraints to simulate a real-world production environment: a memory limit of **256MB**, a refusal to use background queues/jobs for this specific exercise, and a focus on vanilla PHP and Laravel tools without external packages. The goal is to reach a sub-20-second import for one million records. Prerequisites Before diving in, you should have a solid grasp of the following: - **PHP 8.x**: Familiarity with closures, arrays, and file handles. - **Laravel 11**: Basic understanding of Eloquent and the Database Facade. - **MySQL**: Knowledge of tables and basic SQL syntax. - **CLI**: Comfort running Artisan commands. Key Libraries & Tools - **Laravel Framework**: Our primary environment, specifically using the new Concurrency facade. - **PDO (PHP Data Objects)**: Used for low-level database interactions to bypass Eloquent overhead. - **MySQL 8.x**: The target database engine. - **`fgetcsv()`**: A native PHP function that parses a line from a file pointer and checks for CSV fields. Code Walkthrough: Evolution of an Import Phase 1: The Collection Pitfall The most intuitive Laravel approach is loading a file into a collection. While readable, it is the least efficient. ```python $rows = file($filePath); $data = collect($rows)->skip(1)->map(function ($line) { $row = str_getcsv($line); return [ 'name' => $row[1], 'email' => $row[2], // ... more fields ]; }); foreach ($data as $customer) { Customer::create($customer); } ``` **Why it fails:** The `file()` function reads the entire file into memory at once. At 100,000 rows, you hit the 256MB limit. Furthermore, `Customer::create()` triggers a separate SQL `INSERT` for every row, making it incredibly slow. Phase 2: Streamed File Pointers and PDO Chunks To scale, we must stop loading the whole file. Instead, we use `fopen()` and `fgetcsv()` to stream the file line-by-line. To improve database performance, we move away from Eloquent and use PDO prepared statements with manual chunking. ```python $handle = fopen($filePath, 'r'); fgetcsv($handle); // Skip header $pdo = DB::connection()->getPdo(); $stmt = $pdo->prepare("INSERT INTO customers (name, email, ...) VALUES (?, ?, ...)"); while (($row = fgetcsv($handle)) !== false) { $stmt->execute([$row[1], $row[2], ...]); } fclose($handle); ``` By executing 1,000,000 individual queries with a single prepared statement, the memory footprint drops to nearly zero. However, it still takes approximately 3 minutes. To go faster, we must bundle these into "multi-insert" statements. Phase 3: Parallel Processing with Laravel Concurrency Laravel 11 introduced a **Concurrency facade** that allows us to run tasks in parallel. We can divide the file processing across 10 different worker processes. We use a **Modulo Operator** strategy to ensure each process only handles its assigned lines (e.g., Process 1 handles lines 0, 10, 20...). ```python Concurrency::run([ function () use ($filePath, $i) { $handle = fopen($filePath, 'r'); $currentLine = 0; while (($line = fgets($handle)) !== false) { if ($currentLine % 10 !== $i) { $currentLine++; continue; } // Process and insert the line here... $currentLine++; } } ], 10); ``` **Performance Note:** This method slashed the 1,000,000-row import time down to **4 seconds**. Parallelization allows the CPU to utilize multiple cores to handle the parsing and I/O simultaneously. Syntax Notes: The Power of `fgetcsv` vs `str_getcsv` During high-volume processing, the choice of function matters. `fgetcsv()` is designed to work with file handles, making it perfect for streaming. On the other hand, `str_getcsv()` is better when you already have a string in memory (like when using `fgets()`). In the parallel approach, we use `fgets()` first to quickly skip rows we don't own, and only call `str_getcsv()` on the rows we intend to process. This saves significant CPU cycles. Practical Examples - **E-commerce Migration**: Moving millions of customer records or legacy orders from an old system to a new Laravel app. - **Financial Reporting**: Importing daily transaction logs for analysis where speed is critical to meet processing windows. - **Data Warehousing**: Seeding local development environments with massive datasets to test performance at scale. Tips & Gotchas - **The PDO Placeholder Limit**: MySQL via PDO has a limit on the number of placeholders (`?`) in a single query (usually 65,535). If you chunk 10,000 rows with 10 columns each, you will hit this limit. Always calculate your chunk size based on `Limit / Number of Columns`. - **MySQL `local-infile`**: The absolute fastest method is the native `LOAD DATA LOCAL INFILE` command. However, this is often disabled for security. If you use it, you must enable it on both the PDO client and the MySQL server (`SET GLOBAL local_infile = 1`). - **Database Reconnection**: When using the Concurrency facade, each process might need to explicitly reconnect to the database to avoid sharing a socket, which can cause intermittent connection errors. - **Date Formats**: Databases are picky. Ensure you format your CSV dates (e.g., `Y-m-d H:i:s`) before inserting, or use SQL functions like `NOW()` for timestamps to reduce PHP memory usage.