[Python] How to speed up reading SQL Server data into dataframe?

I am trying to read data from some source tables in Microsoft SQL Server, into a dataframe and write them to a target table in Snowflake, using Python and SQL. I'm currently utilizing OFFSET and FETCH NEXT to do this in batches but hoping for suggestions on a more performant way. Most of these tables don't have clustered or un-clustered indexes on them and I cannot modify the underlying tables at this point in time.

Here is an example of what I have so far:

import polars as pl
from snowflake.connector.pandas_tools import write_pandas

# function to read data in from SQL Server in Batches
def extract_batch(source_table, offset, limit):

session = mssql_conn_session()
try:
sql = f"""
SELECT *
FROM {source_table}
ORDER BY
practicecode,
monthyear,
nsaccountid,
providertype,
vpgo
OFFSET {offset} ROWS
FETCH NEXT {limit} ROWS ONLY
"""
# reading into polars dataframe which is faster than pandas.read_sql
df = pl.read_database(sql, session)

except Exception as E:
raise Exception(f"Failed while fetching data from {source_table} due to {E}")
finally:
session.close()

return df


# function that takes above df and writes to snowflake
def write_to_snowflake(df, target_table):

connection = session.connection().connection
try:
# write_pandas() will chunk + parallelize under the hood
success, nchunks, nrows, _ = write_pandas(
conn=connection,
df=df.to_pandas(),
table_name=target_table,
chunk_size=1_000_000,
parallel=8,
quote_identifiers=False,
use_logical_type=True
)
if not success:
raise RuntimeError(f"Failed while inserting data to {target_table}")
connection.commit()
utils.logger.info(
f"Successfully inserted {nrows} rows to {target_table} in {nchunks} chunks."
)
except Exception as E:
connection.rollback()
raise Exception(E)
finally:
connection.close()


I run this in a loop based on table size and how many batches are required. Writing to Snowflake runs very fast using Snowflake's write_pandas() method which leverages Snowflake's copy into command under the hood. But reading in from SQL Server is extremely slow by comparison.

Is there any way I can speed up reading data from SQL Server side? Your help is much appreciated.

Continue reading...