BUG#27426532: Reduce callproc roundtrip time

nmariz · nmariz · commit 093d559d5019 · 2022-10-25T19:19:22.000+01:00
The cursors (both pure and c-ext versions) uses a single SELECT query to
retrieve procedure result parameters after a procedure call. However, to
set the input parameters, one SET call is used per parameter.
For any procedures with more than one parameter this results in more
queries than necessary and ultimately the callproc call takes longer.

With this patch just a single SET call is made.

Thank you for the contribution.

Change-Id: Icac72d24638bd465e336033185058577740ca7d6
diff --git a/CHANGES.txt b/CHANGES.txt
@@ -22,6 +22,7 @@ v8.0.32
 - BUG#32625155: Tests fail against group replication cluster
 - BUG#30089671: Fix decoding VARBINARY columns when using a prepared cursor
 - BUG#28020811: Fix multiple reference leaks in the C extension
+- BUG#27426532: Reduce callproc roundtrip time
 - BUG#24364556: Improve warning behavior
 
 v8.0.31
diff --git a/lib/mysql/connector/cursor.py b/lib/mysql/connector/cursor.py
@@ -802,15 +802,19 @@ def callproc(
             # by '.' and grab the procedure name from procname.
             procname_abs = procname.split(".")[-1]
             if args:
+                argvalues = []
                 for idx, arg in enumerate(args):
                     argname = argfmt.format(name=procname_abs, index=idx + 1)
                     argnames.append(argname)
                     if isinstance(arg, tuple):
                         argtypes.append(f" CAST({argname} AS {arg[1]})")
-                        self.execute(f"SET {argname}=%s", (arg[0],))
+                        argvalues.append(arg[0])
                     else:
                         argtypes.append(argname)
-                        self.execute(f"SET {argname}=%s", (arg,))
+                        argvalues.append(arg)
+
+                placeholders = ",".join(f"{arg}=%s" for arg in argnames)
+                self.execute(f"SET {placeholders}", argvalues)
 
             call = f"CALL {procname}({','.join(argnames)})"
 
diff --git a/lib/mysql/connector/cursor_cext.py b/lib/mysql/connector/cursor_cext.py
@@ -519,15 +519,19 @@ def callproc(
             # by '.' and grab the procedure name from procname.
             procname_abs = procname.split(".")[-1]
             if args:
+                argvalues = []
                 for idx, arg in enumerate(args):
                     argname = argfmt.format(name=procname_abs, index=idx + 1)
                     argnames.append(argname)
                     if isinstance(arg, tuple):
                         argtypes.append(f" CAST({argname} AS {arg[1]})")
-                        self.execute(f"SET {argname}=%s", (arg[0],))
+                        argvalues.append(arg[0])
                     else:
                         argtypes.append(argname)
-                        self.execute(f"SET {argname}=%s", (arg,))
+                        argvalues.append(arg)
+
+                placeholders = ",".join(f"{arg}=%s" for arg in argnames)
+                self.execute(f"SET {placeholders}", argvalues)
 
             call = f"CALL {procname}({','.join(argnames)})"
 
