@Marko Herkaliuk, will ask the dba to monitor while running the queries.

Hi @g.suijker!

It sounds like your kubernetes cluster and docker container may have different resource constraints for execution

Would you mind sharing your Flow’s config for both cases? (the Run Configs, Results, Executor, etc)

What also struck me was that after we switched to the new payment plan with unlimited concurrency, the performance on Cloud for this specific run deteriorated (running times increased by 2x). I do not understand how that could happen, since it is just one flow without mapping or dependent tasks.

Depending on where/when your Flow Runs are being executed in K8s, the increased concurrency may have put additional resource strain on your cluster. Your Flow’s config for the docker vs. k8s setups should help us debug 😄

Here’s some background k8s documentation https://kubernetes.io/docs/tasks/configure-pod-container/assign-cpu-resource/

And our Kubernetes Run basics: https://docs.prefect.io/orchestration/flow_config/run_configs.html#examples-4

If you find that specifying resources doesn’t solve your issue, please let us know

and feel free to follow up with any questions you have 😄

and are you running an insertion per task or many insertions in a single task?

We finally found the issue. The bad performance was due to closing the database connection after each insert query (this flow is doing a lot of insert query's within a loop). On my Windows laptop closing the connection did not result in a lower insert speed, but within a Docker container it did. So when running the flow with flow.run() there was no performance issue, but running the flow via Cloud or Server with Docker storage and a Docker/Kubernetes Agent there was. The query execution function first looked like:

def sqlServerExecute(query, params=None):
    """Execute query with values provided to sqlServer"""

    dwh_connection_string = connection_string

    connection = pyodbc.connect(dwh_connection_string)

    try:
        cursor = connection.cursor()
        cursor.fast_executemany = True
        if params:
            cursor.executemany(query, params)
        else:
            cursor.execute(query)
        connection.commit()

    except (Exception, pyodbc.DatabaseError):
        connection.rollback()
        raise

    finally:
        cursor.close()
        connection.close()

Which we changed to, where the connection was made before the insert loop and closed after :

def sqlServerExecute(query, connection, params=None):
    """Execute query with values provided to sqlServer"""
    try:
        cursor = connection.cursor()
        cursor.fast_executemany = True
        if params:
            cursor.executemany(query, params)
        else:
            cursor.execute(query)
        connection.commit()
    except (Exception, pyodbc.DatabaseError):
        connection.rollback()
        raise
    finally:
        cursor.close()

This resulted in equal insert performance locally (flow.run()) and Cloud with Docker storage and Kubernetes Agent.

When I look at the source code for the SqlServerExecute tasks https://github.com/PrefectHQ/prefect/blob/5de58efaba956b431335d99acab07eaf6a362e1b/src/prefect/tasks/sql_server/sql_server.py#L103, the connection is getting closed after each query as well. I guess more people might encounter the same issue then.

To be more precise, the issue appeared due to Connection Pooling available on my Windows machine, but not within the Docker Container. Any ideas on how I can enable Connection Pooling within the Docker Container?