Passing multiple parameters to the database takes more than 20 seconds

[demetsude]

Passing multiple parameters to the database takes more than 20 seconds and makes the execution of the statement more than the order of magnitude (> 40 times) slower.

Environment:

• Python: 3.6
• Database: Exasol
• Dialect: EXADialect_turbodbc
• Parameters passed: 24 x int (in clause), 2 x date, 3 x Boolean, example: 2, 2, false, false, false, date(2020, 1, 1), date(2020, 2, 1), 1, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14,15, 16, 16, 18, 19, 20, 21
• Statement: merge statement

Time for executing statement with passing parameters (SQLAlchemy merge statement object passed to the session.execute): 00:23,90 s.
Time for compiling statement (statement.compile(dialect=session.connection().dialect)), replacing parameters “manually in code” and executing the statement as string: 00:00,48 s
Further investigation of the problem leads to the class Cursor(object) and the execute method.

Timing each of the statements in it gives following:

@translate_exceptions
   def execute(self, sql, parameters=None):
       """
       Execute an SQL command or query
       :param sql: A (unicode) string that contains the SQL command or query. If you would like to
              use parameters, please use a question mark ``?`` at the location where the
              parameter shall be inserted.
       :param parameters: An iterable of parameter values. The number of values must match
              the number of parameters in the SQL string.
       :return: The ``Cursor`` object to allow chaining of operations.
       """
       self.rowcount = -1
       self._assert_valid()     # 00:00,000
       self.impl.prepare(sql)    # 00:00,047
       if parameters:
           buffer = make_parameter_set(self.impl)   # 00:00,000
           buffer.add_set(parameters)                # 00:00,000
           buffer.flush()                             # 00:23,850
       return self._execute()                 # 00:00,000

Workaround: compile statement (statement.compile(dialect=session.connection().dialect)), replace parameters “manually in code” and execute the statement as string.