executemany(operation, seq_of_parameters) appears to run slower than it should [PYFB70]

[firebird-automations]

Submitted by: Dominik Psenner (dpsenner)

We are facing performance issues with executemany, meaning that the .net implementation of the firebird driver runs way faster when running prepared statements against a set of parameters than the python implementation does. We are talking about a speedup factor beyond 10, i.e. on the .net driver the same statement runs within 100ms where the python implementation takes longer than a second. Therefore we investigated whether the implementation is correct. Unfortunately to us it looks like executemany still prepares statements on every second item in the seq of parameters. See comments below, prefixed with #⁠. Please note that we have not stepped through this with a debugger. Further, this issue relates to PYFB60.

def executemany\(self, operation, seq\_of\_parameters\):
    #⁠ prepare once, which is correct and was added to fix [PYFB60](https://github.com/FirebirdSQL/fdb/issues?q=PYFB60+in%3Atitle)
    if not isinstance\(operation,PreparedStatement\):
        operation = self\.prep\(operation\)
    for parameters in seq\_of\_parameters:
        #⁠ invoked multiple times for each item in the seq of parameters
        self\.execute\(operation, parameters\)

def execute\(self, operation, parameters=None\):
    #⁠ on the first item in the seq of parameters, this if is skipped because self\.\_ps is None
    #⁠ on the second item in the seq of parameters, this if is executed because self\.\_ps is not None and thus the prepared statement resources are cleaned up
    if self\.\_ps \!= None:
        self\.\_ps\.close\(\)
    if not self\.\_transaction\.active:
        self\.\_transaction\.begin\(\)
    if isinstance\(operation, PreparedStatement\):
        if operation\.cursor is not self:
            raise ValueError\("PreparedStatement was created by different Cursor\."\)
        self\.\_ps = weakref\.proxy\(operation, \_weakref\_callback\(self\.\_\_ps\_deleted\)\)
    else:
        self\.\_ps = PreparedStatement\(operation, self, True\)
    #⁠ on the first item in the seq of parameters this invoke uses the prepared statement
    #⁠ on the second item in the seq of parameters this invoke will cause another prepare of the prepared statement because it was closed earlier
    self\.\_ps\.\_execute\(parameters\)

Commits: FirebirdSQL/fdb@9f1ac804dd485cd943fd9507ebeceec696d64872 FirebirdSQL/fbt-repository@5dbe71cd5c226ed1761b259432a70403a1c444fd

[firebird-automations]

Modified by: @pcisar

status: Open \[ 1 \] =\> Resolved \[ 5 \] resolution: Fixed \[ 1 \] Fix Version: 1\.7 \[ 10803 \]

[firebird-automations]

Modified by: @pcisar

status: Resolved \[ 5 \] =\> Closed \[ 6 \]