The process inserting data from the dataframe into the oracle database is work very slow

Question

The process inserting data from the dataframe into the oracle database is work very slow (more than one hour)

Advise me please, how I can to improve the process of inserting data into the database?

count column = 50, raw count = > 250000

I have a lot of such files

Connecting to the database in this way

dialect = 'oracle'
sql_driver = 'cx_oracle'

to_engine: str = dialect + '+' + sql_driver + '://' + user + ':' + password + '@' + host + ':' + str(port) + '/?service_name=' + service_name

connection = create_engine(to_engine)

to_sql method look like this

 df.to_sql(table_name_in_db, con=connection, schema='', if_exists='append', index=False, chunksize = 1000,  dtype=None)

Thanks a lot!

I'd use SQL Loader. It is extremely fast.

Littlefoot
– Littlefoot

2022-03-03 08:08:20 +00:00
Commented Mar 3, 2022 at 8:08 — Littlefoot
– Littlefoot, Commented Mar 3, 2022 at 8:08

Asli · Accepted Answer · 2022-03-03 10:25:35Z

1

This trick help me

Speed up to_sql() when writing Pandas DataFrame to Oracle database using SqlAlchemy and cx_Oracle Conclusion: use the following trick in order to explicitly specify dtype for all DF columns of object dtype when saving DataFrames to Oracle DB. Otherwise it'll be saved as CLOB data type, which requires special treatment and makes it very slow

 dtyp = {c:types.VARCHAR(df[c].str.len().max())
        for c in df.columns[df.dtypes == 'object'].tolist()}

 df.to_sql(..., dtype=dtyp)here

answered Mar 3, 2022 at 10:25

Asli

316 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Christopher Jones Over a year ago

Further down on that other SO question, chunksize was mentioned. You may also want to tune this.

Asli Over a year ago

Thanks, "The ideal chunksize depends on your table dimensions. A table with a lot of columns needs a smaller chunk-size than a table that has only 3." I dont know, this is true or not I find this method here towardsdatascience.com/… I had a lot of such files where are different numbers of columns and rows

Christopher Jones Over a year ago

You can run some tests to tune chunksize for your data and network. But if you want best performance, then use cx_Oracle directly without the Pandas & SQLAlchemy layers. See the cx_Oracle doc on batch loading.

Collectives™ on Stack Overflow

The process inserting data from the dataframe into the oracle database is work very slow

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related