My goal to transfer data from some third party db located on remote server into my SQL Server database. Total about 50 tables, once they stored on disk it takes ~3G. In current setup I only can use Python to get data from source, b'z of security setup for given vendor. Below is pseudo code construction I'm using. The problem that to_sql upload part takes too long. I tried to work with the single df in the loop, individual df1, df2... serially to minimize download time. I'm able to get all source into df* in 3 min (~ 3G) but load takes 3-4 hours. There are no any PK/Indexes. On SQL side memory and cpu usage don't exceed 40%, so I think this is not the bottle neck. For target engine_mssql played with chunk optoin without any difference. Does Python has any other tools/connectors/method to make it faster? Appreciate your feedback. import pandas as pd import sqlalchemy ... df = pd.read_sql('Select * from TxHistory1', engine_mariadb) # source df.to_sql('TxTarget1', engine_mssql, schema='dbo', if_exists='replace') # target .. Continue reading...
