Accessing database speed

Question

I have a simple application(Telegram bot) that about 2000-3000 people are using right now.

So I want to increase the balance of users every "N" seconds. It depends on their current status. The code works fine, but the function might not work after some amount of time. I started with 30 seconds, but after the first issue, I thought that 30 seconds is not enough to go through all rows and execute it. So right now I'm running it with 1200 seconds but anyway it stops growing after a while.

So is it just because of it or I'm doing something wrong in the code itself?

P.S. I'm using Python3 and SQLite and the bot is running constantly on a cheap, weak VPS server.

def balance_growth():
    try:
        cursor = connMembers.cursor()
        sql = "SELECT * FROM members"
        cursor.execute(sql)
        data = cursor.fetchall()
        for single_data in data:
            if single_data[5] == "Basic":
                sql = "UPDATE members SET balance = {B} + 1 WHERE chat_id = {I}".format(B=single_data[1], I=single_data[0])
                cursor.execute(sql)
            elif single_data[5] == "Bronze":
                sql = "UPDATE members SET balance = {B} + 2 WHERE chat_id = {I}".format(B=single_data[1], I=single_data[0])
                cursor.execute(sql)
            elif single_data[5] == "Silver":
                sql = "UPDATE members SET balance = {B} + 12 WHERE chat_id = {I}".format(B=single_data[1], I=single_data[0])
                cursor.execute(sql)
            elif single_data[5] == "Gold":
                sql = "UPDATE members SET balance = {B} + 121 WHERE chat_id = {I}".format(B=single_data[1], I=single_data[0])
                cursor.execute(sql)
            elif single_data[5] == "Platinum":
                sql = "UPDATE members SET balance = {B} + 1501 WHERE chat_id = {I}".format(B=single_data[1], I=single_data[0])
                cursor.execute(sql)

                cursor.execute(sql)
            connMembers.commit()
        cursor.close()

        t = threading.Timer(120, balance_growth).start()
    except Exception as err:
        print(err)

Shawn · Accepted Answer · 2018-10-14 20:29:20Z

1

Why not just do it all in a single update statement instead of one per row? Something like

UPDATE members SET balance = balance + (CASE whatever_column
                                        WHEN "Platinum" THEN 1501
                                        WHEN "Gold" THEN 121
                                        WHEN "Silver" THEN 12
                                        WHEN "Bronze" THEN 2
                                        ELSE 1 END)

Edit:

Other suggestions:

Use integers instead of strings for the different levels, which will both be faster to compare and take up less space in the database.
Redesign your logic to not need an update every single tick. Maybe something like keeping track of the last time a row's balance was updated, and updating it according to the difference in time between then and now whenever you need to check the balance.

edited Oct 14, 2018 at 20:29

answered Oct 14, 2018 at 20:17

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

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

Comments

CL. · Accepted Answer · 2018-10-14 20:46:57Z

0

The problem is that you're calling commit() after every single UPDATE statement, which forces the database to write back all changes from its cache.

Do a single commit after you have finished everything.

answered Oct 14, 2018 at 20:46

CL.

182k18 gold badges241 silver badges282 bronze badges

Collectives™ on Stack Overflow

Accessing database speed

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related