Skip to content

Latest commit

 

History

History
153 lines (118 loc) · 2.85 KB

bulk-operations.md

File metadata and controls

153 lines (118 loc) · 2.85 KB

Bulk Operations

Process large volumes of data efficiently with PDOdb.

Multi-Row Inserts

insertMulti()

Insert multiple rows in a single query:

$users = [
    ['name' => 'Alice', 'email' => 'alice@example.com', 'age' => 30],
    ['name' => 'Bob', 'email' => 'bob@example.com', 'age' => 25],
    ['name' => 'Carol', 'email' => 'carol@example.com', 'age' => 28]
];

$count = $db->find()->table('users')->insertMulti($users);
echo "Inserted {$count} users\n";

Generated SQL

INSERT INTO users (name, email, age) VALUES
    (:name_0, :email_0, :age_0),
    (:name_1, :email_1, :age_1),
    (:name_2, :email_2, :age_2)

Bulk Updates

Update Multiple Rows

// Update in batch
foreach ($users as $user) {
    $db->find()
        ->table('users')
        ->where('id', $user['id'])
        ->update(['last_login' => Db::now()]);
}

Batch Update with Transaction

$db->startTransaction();
try {
    foreach ($userIds as $userId) {
        $db->find()
            ->table('users')
            ->where('id', $userId)
            ->update(['status' => 'active']);
    }
    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
    throw $e;
}

CSV Import

Basic CSV Load

$db->find()->table('users')->loadCsv('/path/to/users.csv');

With Options

$db->find()->table('products')->loadCsv('/path/to/products.csv', [
    'fieldChar' => ',',
    'fieldEnclosure' => '"',
    'fields' => ['id', 'name', 'price'],
    'linesToIgnore' => 1,  // Skip header
    'local' => true
]);

XML Import

Basic XML Load

$db->find()->table('users')->loadXml('/path/to/users.xml', [
    'rowTag' => '<user>',
    'linesToIgnore' => 0
]);

Performance Comparison

Single Inserts (Slow)

// ❌ Slow: Multiple round trips
foreach ($users as $user) {
    $db->find()->table('users')->insert($user);
}
// 1000 users = 1000 queries

Bulk Insert (Fast)

// ✅ Fast: Single query
$db->find()->table('users')->insertMulti($users);
// 1000 users = 1 query

Best Practices

Use Transactions

$db->startTransaction();
try {
    $db->find()->table('users')->insertMulti($users);
    $db->find()->table('profiles')->insertMulti($profiles);
    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
    throw $e;
}

Batch Large Inserts

// Insert in batches of 1000
$batches = array_chunk($largeDataset, 1000);

foreach ($batches as $batch) {
    $db->startTransaction();
    try {
        $db->find()->table('users')->insertMulti($batch);
        $db->commit();
    } catch (\Exception $e) {
        $db->rollback();
        throw $e;
    }
}

Next Steps