mssql on azure - slow data upload

[fnagy]

Hello,

I am experiencing very slow data upload on MS SQL Server, both when i create a table or insert data to an existing one.

The table in question is 1.1 million rows, 250MB as a csv or 65MB as a duckdb. I have tried both uploading it in full and in batches. I had to terminate both because it was taking too long.

On azure there are 9GB of free space. Any idea of why is this happening?

Here's the code:

df = con_ddb.table("Q_Appointment") .to_polars(limit=None)
con_az_aia.create_table("Q_Appointment", df, overwrite=True) #upload to azure mssql

df = con_ddb.table("Q_Appointment")

def upload_to_sql_in_chunks(df, table_name, connection, chunk_size=5000):
    try:

        # Calculate number of chunks
        n_chunks = int(math.ceil(df.count().to_pandas() / chunk_size))

        print(n_chunks)

        # Upload each chunk
        for i in range(n_chunks):
            print(i)
            start_idx = i * chunk_size
            # end_idx = min((i + 1) * chunk_size, df.count().to_pandas())

            chunk = df.limit(chunk_size, offset = start_idx).to_polars(limit=None)

            # For first chunk, replace table if exists
            if i == 0:
                connection.create_table("Q_Appointment", chunk, overwrite=True)
            # For subsequent chunks, append to existing table
            else:
                connection.insert("Q_Appointment", chunk)

            print(f"Uploaded chunk {i+1}/{n_chunks} ({len(chunk)} records)")

        print(f"Successfully uploaded {len(df)} records to {table_name}")
        return True

    except Exception as e:
        print(f"Error uploading data: {str(e)}")
        return False

print("uploading")  
upload_to_sql_in_chunks(df, "Q_Appointment",con_az_aia, 1000)

[cpcloud]

Thanks for opening a discussion!

The reason this is likely slow is all the conversion that's happening.

1. You're converting to polars, which, all else being equal, is cheap when done with DuckDB.
2. Then the polars dataframe has to be converted to something that is compatible with the pyodbc driver which is the absolute worst possible representation for fast bulk inserts: a sequence of tuples.
3. Those tuples are sent over a network, so some of the cost is probably in IO as well, depending on available bandwidth.

[fnagy]

So... how can i speed things up? Because i remember trying with memtables but it had resulted in error (different connection environments i suppose)