Debounce Function
Delay function execution until the user stops triggering events, preventing excessive API calls and layout…
Batch Insert Pattern
Buffer database inserts and flush in batches for dramatically faster data loading.
Use this when:
You are loading thousands of records into a database and individual INSERTs are too slow.
The Pattern
BatchInserter.php
class BatchInserter
{
private array $buffer = [];
private int $batchSize;
private PDO $pdo;
public function __construct(PDO $pdo, int $batchSize = 100)
{
$this->pdo = $pdo;
$this->batchSize = $batchSize;
}
public function add(array $row): void
{
$this->buffer[] = $row;
if (count($this->buffer) >= $this->batchSize) {
$this->flush();
}
}
public function flush(): void
{
if (empty($this->buffer)) {
return;
}
$columns = array_keys($this->buffer[0]);
$placeholders = '(' . implode(',', array_fill(0, count($columns), '?')) . ')\;
$allPlaceholders = implode(',', array_fill(0, count($this->buffer), $placeholders));
$sql = sprintf(
'INSERT INTO records (%s) VALUES %s',
implode(',', $columns),
$allPlaceholders
);
$values = [];
foreach ($this->buffer as $row) {
foreach ($row as $value) {
$values[] = $value;
}
}
$stmt = $this->pdo->prepare($sql);
$stmt->execute($values);
$this->buffer = [];
}
}What Happens Under the Hood
Let us trace what happens when you insert 250 records with a batch size of 100:
execution-flow.txt
Record 1-99: Added to buffer → No database call
Record 100: Buffer full → Flush 100 records in ONE INSERT
Record 101-199: Added to buffer → No database call
Record 200: Buffer full → Flush 100 records in ONE INSERT
Record 201-250: Added to buffer → Call flush() manually at end
Final flush: Flush remaining 50 records in ONE INSERT
Total database calls: 3 (instead of 250)Why This Matters
Each database INSERT has overhead: network round-trip, query parsing, transaction handling. Batching reduces this overhead dramatically:
performance-comparison.txt
Single inserts: 250 records × 5ms overhead = 1,250ms
Batched inserts: 3 queries × 5ms overhead = 15ms + bulk insert time
Real-world improvement: Often 10-50x fasterUsage Example
usage.php
$inserter = new BatchInserter($pdo, 100);
foreach ($dataStream as $record) {
$inserter->add([
'name' => $record['name'],
'email' => $record['email'],
'created_at' => date('Y-m-d H:i:s'),
]);
}
// Do not forget the final flush for remaining records
$inserter->flush();The buffer pattern ensures efficient database operations regardless of how many records you process. Small datasets work fine. Large datasets stay efficient.