Checking the number of parameters of the "outputtypehandler" function can be very slow.

[abdeljalilchehaibou]

_get_output_type_handler of class BaseCursorImpl uses the inspect.signature introspection method to check the number of parameters of the outputtypehandler.

This inspect.signature method is CPU intensive, taking up to 50% of cursor.execute execution time.

I suggest using "co_varnames" which is more CPU-friendly than inspect.signature:

def f(a,b,*x, d=1):pass

f.__code__.co_varnames
('a', 'b', 'd', 'x')

Gives:

>>> timeit.timeit("sig = inspect.signature(f); len(sig.parameters)", number=1000000, globals=globals())
6.5049042999744415								   
>>> timeit.timeit("len(f.__code__.co_varnames)", number=1000000, globals=globals())
0.06620880000991747

Here is a screenshot of a profiline showing the inspect.signature fingerprint on cursor.execute:

I propose this patch for python-oracledb for oracledb/impl/base/cursor.pyx:

index 409d960d4..6f5b192d3 100644
--- a/python-oracledb-2.5.0/src/oracledb/impl/base/cursor.pyx
+++ b/python-oracledb-2.5.0/src/oracledb/impl/base/cursor.pyx
@@ -317,9 +317,8 @@ cdef class BaseCursorImpl:
             type_handler = conn_impl.outputtypehandler
         if type_handler is not None:
             try:
-                sig = inspect.signature(type_handler)
-                uses_fetch_info[0] = (len(sig.parameters) == 2)
-            except (ValueError, TypeError):
+                uses_fetch_info[0] = (len(type_handler.__code__.co_varnames) == 2)
+            except (ValueError, TypeError, AttributeError):
                 uses_fetch_info[0] = False
         return type_handler

[cjbj]

Thanks for sharing.

[anthony-tuininga]

Unfortunately this does not work for cases where the method is implemented with __call__() as is shown in test 3808. We may consider a flag that specifies whether or not to simply assume that the method accepts 2 arguments without checking -- in order to avoid this overhead.

[abdeljalilchehaibou]

The solution of using an indicator that the method accepts 2 arguments without checking is a good compromise, which allows to guarantee backward compatibility and to avoid this overhead.
While waiting for the implementation, we continue to patch python-oracledb before deployment.

Thank you!