Performance delta between execute and executemany

[HayDegha0917]

We noticed a huge performance delta between inserting using execute and executemany. Details are:

We are inserting 8900 rows into a table that has no indices and no constraints. After reading the data into a Python dictionary, we execute an insert in a loop using a statement of the form

INSERT (COL1, COL2, COL3...)  INTO T VALUES (:COL1, :COL2, :COL3...)

The simplified code is:

# Iterate through the data and execute the INSERT statement for each row
    for line_number, (index, row) in enumerate(data.iterrows()):
        # Generate values_dict for the current row
        values_dict = {}
        for col in columns:
            col_name = col['name']

            # Ensure the column is not virtual
            if col_name not in virtual_columns:
                values_dict[col_name] = row[col_name]

        try:
            # Execute the INSERT statement for the current row
            cursor.execute(insert_sql, values_dict)

        except oracledb.DatabaseError as e:
            # Log the error and rollback the transaction
            error, = e.args

            log.log_record_error(data_filepath, row.tolist(),
                                 str(error.code) + " " + error.message, line_number)
            load_stats["num_rejected_perm"] += 1

        # Commit the pending transactions every 10000 rows
        if line_number % 10000 == 0:
            conn.commit()

    # Commit any remaining records
    conn.commit()

Running this code on 8913 records took ~10 minutes.

We attempted to improve the performance by:

1. Call cursor.prepare(). This did not help
2. Replaced executemany() instead of execute() and pass in 1000 records at a time to executemany. This dropped the time from 10 minutes to 6 seconds.

We're preparing a test case to attach to an issue, but we can't figure out where the time is going. Our best guess is that the driver is doing round trips to the DB on every call of execute() but we can't figure out why. We didn't experience this kind of issue when using cx-oracle.

This is a general-purpose utility (i.e.,, the INSERT statement gets generated dynamically). Using setinputsizes would be kind of painful. It would be equally painful to use setinputsizes elsewhere in the code. It seems like this is something that the driver should do automatically on the first invocation and then cache with the cursor.

Or am I missing something?

[anthony-tuininga]

Both python-oracledb and cx_Oracle use the same approach, and yes, calling execute() 1000 times will take considerably longer than calling executemany() with 1000 rows. Each call to execute() requires a round trip whereas executemany() only requires one round trip. This has not changed! For performance reasons you should definitely use executemany()!

[HayDegha0917]

Is the round-trip simply due to actually executing the statement, or is there overhead due to getting metadata, etc.? If the former, then the numbers are a bit puzzling, because this code is being executed over a fast network.

[anthony-tuininga]

The round-trip is simply for executing the statement. You can set the environment variable PYO_DEBUG_PACKETS to any value and see the round trips and the length of time each takes. The time from the sending of the packet to the receiving of the response will tell you how long the database is taking to process that request (less the time required to traverse the network, of course). Considering that 1000 rows takes 6 seconds to process that suggests the database is taking about 6 ms to process each row -- which is a rather long time. For simple rows I can process tens of thousands of rows each second without any difficulty. That suggests the rows contain a lot of data or there is significant processing overhead on the database (triggers? indexes?).

[HayDegha0917]

Oh yay I didn't know about that variable. I will definitely try it out.

The table is pretty small, without anything exotic (no CLOBs, excessive indices, triggers or anything of the kind). It is an AWS RDS DB, but I sized it to be comparable to our on-prem DB. I will review the settings again. For sure the I/O subsystem is better than our on-prem DB, which is using Direct NFS.

Maybe PYO_DEBUG_PACKETS will shed some light on the problem.