CODEWITHSUNDEEP
pythonmysqlsql-updatequery-optimizationbulkupdate
ElSergio00
ElSergio00

Reputation: 23

How can I speed up updating MySQL

How can I speed up updating data in my database? Do I need to change the loop or build the update in a different way?

try:
    g = 1
    for i in gate_io().values.tolist():
        with connection.cursor() as cursor:
            if i[1] != 0 and i[1] != '':
                insert_quarry = "UPDATE gate SET symbol = %s, bidPX = %s, askPx = %s WHERE id = %s"
                currency = [i[0], i[1], i[2]]
                cursor.execute(insert_quarry, (currency[0], currency[1], currency[2], g))
                connection.commit()
                g = g + 1
            else:
                continue
finally:
    connection.close()

Is it possible to use NumPy for this? Or are there other options?

Upvotes: 2

Views: 438

Answers (1)

O. Jones
O. Jones

Reputation: 108676

Don't commit after every UPDATE. Instead commit after each batch of 100 or so. Most of the actual work of updating happens at commit.

Don't forget to commit the last batch.

Upvotes: 3

Related Questions