How can we improve speed of Python reading from AS400 tables?

We're working on a data ingestion tool in Python and facing slowness while reading from (multiple) AS400 tables.

This is the core portion of the tool (specific to reading of AS400 tables). As you can see, it's not doing much; it merely reads the table from AS400 database, adds a timestamp column to it, and writes it to another target database, which, in this case, resides in MS SQL Server.

def process(record):
    engine_source = new_as400_connection()
    engine_destination = new_db_connection()
    ROWS = 100_000
    
    df_gen = pd.read_sql(record.query, engine_source, chunksize=ROWS)
    for df in df_gen:
        df['_ETLDate'] = pd.to_datetime('today').replace(microsecond=0)
        df.to_sql(
            record.target,
            engine_destination,
            if_exists='append',
            index=False,
        )
    
    engine_source.dispose()
    engine_destination.dispose()

Here's how we're calling this function using the built-in ThreadPoolExecutor:

if __name__ == '__main__':
    with concurrent.futures.ThreadPoolExecutor(16) as executor:
        __ = executor.map(process, records)

where records is a list of tables to be processed, which looks like this:

┌───────────┬───────────┬────────────────────────────────────────┐
│ source_id │  target   │                 query                  │
├───────────┼───────────┼────────────────────────────────────────┤
│ TABLE001  │ target_01 │ SELECT * FROM TABLE001                 │
│ TABLE002  │ target_02 │ SELECT * FROM TABLE002                 │
│ TABLE003  │ target_03 │ SELECT * FROM TABLE003 WHERE ABC > 100 │
└───────────┴───────────┴────────────────────────────────────────┘

The idea is, obviously, to copy data from AS400 to SQL Server in parallel. However, this approach is slower than equivalent jobs executed using SSIS packages. The difference is very significant: this Python code takes almost 3x-4x more time than SSIS packages do (even when the latter are executed sequentially).

Things we tried:

• Use multi-processing. Not surprised that it didn't help, as this is supposed to be an I/O intensive operation.
• Check AS400 documentation: extremely useless.
• Multiple threads within each thread - by trying to read, say, row #1 - #10K in one thread, next 10K rows in another, and so on. Not much of a difference.
• The SQL Server resides in Azure environment, while the AS400 databases reside in an on-premises server. The Python tool was initially being executed from the Azure VM, and we thought moving the code execution to on-premises server will help. Surprisingly, it took a little longer than the execution in the Azure VM server.
• Fetch all rows at once instead of batches of 100K. It didn't help, but occasionally, some large tables would cause memory errors and Python would crash.
• Run all tables sequentially rather than in parallel. Only took a bit longer than the multi-threaded approach (not surprised).
• We're also using another similar package in which, instead of using DataFrames, we're using pyodbc's cursor.fetchmany. That performs almost the same as this one. So, we believe this drastic slowness is not due to using pandas.read_sql.

A colleague suggested invoking the copying process of each table in a separate process in a subshell. So, we switched from the multi-threading approach to this child-process approach:

if __name__ == '__main__':
    args = parser.parse_args()
    if args.single_record:
        process(json.loads(record))
    else:
        cmd = './venv/Scripts/python -m sourcetostaging --single_record'
        for record in records:
            subprocess.Popen(
                [*cmd.split(), json.dumps(record)],
                cwd='./',
                stdout=subprocess.DEVNULL,
            )

It worked well: we saw an improvement of around 60%-70%. However, this code was still slower than the SSIS packages: this modified approach took twice as much time as the SSIS packages (even when the latter were executed sequentially).

We understand that since SSIS uses SQL, it'll always be faster than equivalent Python code. However, 2x more time seems to indicate that there must be something wrong I'm doing in the Python code.

Here are the connection strings we're using:

def new_as400_connection():
    cs = 'ibm_db_sa+pyodbc:///?odbc_connect=' + quote(
        'DRIVER={iSeries Access ODBC Driver};'
        + 'SYSTEM={system};UID={uid};PWD={pwd}'.format(**config_as400)
    )
    return sqlalchemy.create_engine(cs)


def new_db_connection():
    cs = 'mssql+pyodbc://{user}:{pwd}@{host}/{database}?driver={driver}'
    return sqlalchemy.create_engine(
        cs.format(**config_db),
        fast_executemany=True,
    )

Is there any other approach we're overlooking? We thought the multi-threaded approach is pretty common one.

Notes

• Both Python and SSIS are using the same set of configurations (Compression = True, etc.)
• We have tried profiling this code to see where the bottleneck is: not surprised that for df in df_gen: (or cursor.fetchmany in another package) is the most time consuming of all.