OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Optimizing Batch Inserts for Improved Efficiency in Flask SQLAlchemy

  • Thread starter Thread starter Luiza Souza Simões
  • Start date Start date
L

Luiza Souza Simões

Guest
I am trying to insert 60 to 70 rows at once in a SQL Server table. Currently, I have a function to do it but it is taking almost 15 seconds to complete the task. It would not be a problem if it was just that, but I have to do this up to 40 times. The whole process ends up taking around 6 to 12 minutes.

This is my current function (the one that takes 15 seconds each time it runs):

Code:
def delete_insert_data(df):
    months_to_erase = list(set(df['month']))
    year_to_erase = list(set(df['year']))[0]
    product_code = df.iloc[0,0]

    for month_to_erase in months_to_erase:
        db.session.execute(delete(finance_products).where(
            finance_products.product_code  == product_code ,
            finance_products.year == year_to_erase,
            finance_products.month == month_to_erase
        ))

    records_to_insert = df.to_dict(orient='records')

    db.session.execute(insert(finance_products), records_to_insert)
    db.session.commit()

Is there any way I can insert this data faster?

I have tried inserting the data row by row, with the db.session.add() and it was worse. The bulk_insert_mappings() was basically the same and I also tried "cleaning" the session after the commit with db.session.expunge_all(), but it was also the same.
<p>I am trying to insert 60 to 70 rows at once in a SQL Server table. Currently, I have a function to do it but it is taking almost 15 seconds to complete the task. It would not be a problem if it was just that, but I have to do this up to 40 times. The whole process ends up taking around 6 to 12 minutes.</p>
<p>This is my current function (the one that takes 15 seconds each time it runs):</p>
<pre><code>def delete_insert_data(df):
months_to_erase = list(set(df['month']))
year_to_erase = list(set(df['year']))[0]
product_code = df.iloc[0,0]

for month_to_erase in months_to_erase:
db.session.execute(delete(finance_products).where(
finance_products.product_code == product_code ,
finance_products.year == year_to_erase,
finance_products.month == month_to_erase
))

records_to_insert = df.to_dict(orient='records')

db.session.execute(insert(finance_products), records_to_insert)
db.session.commit()
</code></pre>
<p>Is there any way I can insert this data faster?</p>
<p>I have tried inserting the data row by row, with the db.session.add() and it was worse. The
bulk_insert_mappings() was basically the same and I also tried "cleaning" the session after the commit with db.session.expunge_all(), but it was also the same.</p>
 
Top