L'appel d'une procédure stockée est beaucoup plus lent que l'appel d'une insertion, et les insertions en masse sont fondamentalement les mêmes, pourquoi ?

Question

J'ai une table et une procédure stockée comme indiqué ci-dessous,

CREATE TABLE `inspect_call` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `task_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `cc_number` varchar(63) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `created_at` bigint(20) unsigned NOT NULL DEFAULT '0',
  `updated_at` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `task_id` (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=234031 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 

CREATE PROCEDURE inspect_proc(IN task bigint,IN number varchar(63))
INSERT INTO inspect_call(task_id,cc_number) values (task, number)

J'avais supposé que l'appel de la procédure stockée serait beaucoup plus rapide que le simple appel de l'insertion. Mais à ma grande surprise, ce n'est pas le cas. Lorsque j'insère 10 000 lignes d'enregistrements, la commande d'insertion prend environ 4 minutes et la procédure stockée prend environ 15 minutes.

J'ai effectué le test plusieurs fois pour le confirmer. Le serveur MySQL n'est pas un serveur haut de gamme mais je ne comprends pas pourquoi l'appel de procédures stockées est tellement plus lent.

#using mysql-connector-python 8.0.31
command = ("INSERT INTO inspect_call (task_id,cc_number)"
           "VALUES (%s, %s)")
for i in range(rows): 
    cursor.execute(command, (task_id,f"{cc}{i}"))
    # cursor.callproc("inspect_proc", (task_id,f"{cc}{i}"))
cnx.commit()

BTW, j'ai lu certains articles disant que je pouvais régler innodb_flush_log_at_trx_commit = 2 pour augmenter la vitesse d'insertion, mais je ne vais pas le faire.

--- Mise à jour ---

Sur la base des réponses que j'ai obtenues, j'ai essayé l'insertion par lots (executemany) pour voir s'il y avait une amélioration, mais à ma grande surprise il n'y en avait pas .

cursor = cnx.cursor(buffered=True)
for i in range(int(rows/1000)):
    data = []
    for j in range(1000):
        data.append((task_id,f"{cc}{i*1000+j}"))
    cursor.executemany(command,data)
 cnx.commit()

 # no improvement compared to 

 cursor = cnx.cursor()
 for i in range(rows):
    cursor.execute(command, (task_id,f"{cc}{i}"))

Je l'ai essayé plusieurs fois (j'ai également essayé executemany 100 disques en 1 coup) et j'ai constaté que leurs performances étaient fondamentalement les mêmes.

Pourquoi est-ce ?

--- Mise à jour 2 ---

Je comprends enfin pourquoi l'insertion est si lente ! Parce que j'exécute le script depuis mon ordinateur portable et que j'accède à la base de données à partir de son nom d'hôte externe. Une fois que j'ai téléchargé le script sur le serveur et accédé à la base de données depuis l'intranet, cela a été beaucoup plus rapide. L'insertion de 10 000 enregistrements prend environ 3 à 4 secondes ; l'insertion de 100 000 enregistrements prend environ 36 secondes. Mon manque d’Internet ferait une telle différence !

Mais executemany n'a pas amélioré les performances dans mon cas.

Answer 1

Votre exemple ne crédite pas les procédures stockées car il ne profite d'aucun de leurs avantages.

Les principaux avantages des procédures stockées sont :

• Compilé
• Ça économise les échanges réseau (car le calcul se fait côté serveur)

Supposons que vous ayez une logique suffisamment complexe pour qu'elle ne puisse pas être manipulée via UPDATE et que vous souhaitiez le faire, par exemple en Python, cela nécessite :

• Sélectionnez la ligne -> Trafic réseau [Serveur -> Client]
• Mise à jour des lignes -> Assez lente : Python est interprété, si vous utilisez un ORM comme SQLAlchemy (les objets doivent être créés en mémoire) cela peut être encore plus lent
• Renvoyer les lignes mises à jour -> Trafic réseau [Client -> Serveur]

Imaginez le même exemple implémenté à l'aide de procédures stockées. Dans ce genre d’exemple, il y a de fortes chances que la procédure stockée fasse réellement la différence.

Dans votre exemple, vous n'avez aucune logique, vous insérez simplement des lignes. Il s'agit d'un cas d'utilisation lié aux E/S. Il y a peu ou pas d'avantages à avoir un programme compilé. Vous aurez autant d'échanges réseau qu'avec INSERT. Dans tous les cas, la ligne doit être envoyée au serveur. Le trafic réseau n’a pas non plus augmenté.

Dans votre exemple, peut-être que 批量插入 peut aider à obtenir les meilleures performances.