CODEWITHSUNDEEP
pythonsqlite
Alex
Alex

Reputation: 19

Accessing database speed

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)

Upvotes: 0

Views: 29

Answers (2)

CL.
CL.

Reputation: 180070

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.

Upvotes: 0

Shawn
Shawn

Reputation: 52374

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.

Upvotes: 1

Related Questions