Discussion :

[AdriLRS]

Bonjour à tous,

Je travaille sur une application pour laquelle nous avons une entité "view", une entité "column", une entité "row" et une entité "cell". "cell" est associé à "column" et "row" qui sont toutes deux associées à "view". Le but est de modéliser un tableau.

Dans ce contexte, nous avons parfois des insertions "massives" de "row" et "cell" et je cherche à A - optimiser le process / B - exécuter les requêtes en asynchrone.

Pour la partie A, j'ai réduit au maximum le code et passe par du dbal avec les lignes suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
// Pass an array structured as followed :
// Warning put a comma on each row except last one
// Array (
//     "(cell_row_id,column_row_id,'value')",
//     "(cell_row_id,column_row_id,'value')",
//     "(cell_row_id,column_row_id,'value')",
// )
public function insertRawRows($row,Connection $connection)
{
        $connection->query("
        INSERT INTO cell (id,value,cell_row_id,cell_column_id)
        VALUES
            " . implode(',', $row) . ";
        ");
}

C'est pas mal mais ça reste trop bloquant, des suggestions d'amélioration ?

La partie B est complémentaire, y a t-il un moyen d'exécuter de manière asynchrone cette fonction? J'ai vu le système de message, mais cela me semble être uniquement pour des opérations "simples". Je me trompe ?

Un grand merci par avance

[Séb.]

C'est pas mal mais ça reste trop bloquant, des suggestions d'amélioration ?

Tu veux gagner en vitesse d'insertion ? Combien de lignes à insérer ?
Avec MySQL j'ai de bons résultats en validant une transaction tous les 500 à 3000 INSERT (il faut tester)
ou en générant des INSERT ... VALUES (row_1), (row_2), (row_3), ...

Mais généralement j'ai les meilleurs résultats avec LOAD DATA : https://dev.mysql.com/doc/refman/8.0/en/load-data.html


Pour l'exécution asynchrone, il y a sûrement moyen, mais le serveur de bdd et ses verrous d'INSERT feront de toute manière goulot.

[AdriLRS]

40 000 à 60 000 lignes...

Quand tu dis "validant une transaction", ça veut dire que tu utilises INSERT ... VALUES - avec 300 à 5000 row ici - c'est ça ?

LOAD DATA - Si je comprends bien tu écris tes paramètres dans un fichier externe que tu insères ensuite dans ta DB avec LOAD DATA c'est ça ?

Merci !

[Séb.]

Quand tu dis "validant une transaction", ça veut dire que tu utilises INSERT ... VALUES - avec 300 à 5000 row ici - c'est ça ?

Non. J'ai 2 méthodes distinctes : 1. les transactions, le 2. INSERT multi-rows

1. Les transactions

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
$sql = 'INSERT INTO ta_table (col_1, col_2, col_3) VALUES (:val_1, :val_2, :val_3)';
$insert = $pdo->prepare($sql);
$bulk = 5_000; // Soumet la transaction toutes les $bulk lignes
$pdo->beginTransaction();
foreach ($data as $row_count => $record) { // Parcours des données à insérer
    $insert->execute(['val_1' => $record['val_1'], ...]); // INSERT unitaire sur requête préparée
    if ((($row_count + 1) % $bulk) === 0) {
        $pdo->commit(); // Soumission
        $pdo->beginTransaction();
    }
}
$pdo->commit(); // Pour le reliquat

2. Le INSERT multi-rows

Qui consiste à construire une requête SQL du type :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
INSERT INTO ta_table (col_1, col_2, col_3)
VALUES
    (val_11, val_12, val_ 13), 
    (val_21, val_22, val_ 23), 
    (val_31, val_32, val_ 33); // Ici 3 lignes, à faire sur X milliers de lignes

Ces 2 méthodes donnaient de bonnes perfs assez proches (mais mes tests commencent à dater sérieusement )
Aujourd'hui, toutes les principales DB supportant les transactions, il vaut mieux utiliser la 1re méthode avec les transactions.

LOAD DATA - Si je comprends bien tu écris tes paramètres dans un fichier externe que tu insères ensuite dans ta DB avec LOAD DATA c'est ça ?

Oui, tu construis un fichier dans un des formats attendus par LOAD DATA (ex. TSV ou CSV).
C'est la méthode donnant (largement) les meilleures perfs, mais il faut générer le fichier, et enfin il faut que le serveur MySQL aient les droits nécessaires pour y accéder.