discord py - how can i boost the speed of my code up?

Question

i have a problem. I want to make a task for my giveaway-bot for checking if a giveaway ended. So I created a task for doing it and it runs many lines of code and everything is working. But I noticed that my code is pretty slow. Can anyone help me and say what I can improve and how to speed this up?

I use aiomysql for connecting to my mariadb database and time.time() for checking the code speed.

Sorry if I'm making something wrong, I'm new on this site, feel free to comment if you need anything from me. :)

My Discord-py task:

@tasks.loop(minutes=5.5)
    async def end_check(self):
        await self.client.wait_until_ready()
        start = time.time()

        mydb = await getConnection()
        mycursor = await mydb.cursor()
        current = datetime.now().timestamp()
        current = str(current).split(".")
        # get the main giveaway-data
        await mycursor.execute("SELECT guild_id, channel_id, message_id, gw_req FROM guild_giveaways WHERE end_date < %s", (current[0],))
        in_database = mycursor.fetchall()

        for entry in in_database:
            guild = self.client.get_guild(int(entry[0]))
            channel = guild.get_channel(int(entry[1]))
            message = await channel.fetch_message(int(entry[2]))

            emb = message.embeds[0].description.split("**")
            creator_id = emb[7].replace("<@", "").replace(">", "").replace("!", "")

            count = 0
            gwrole = None
            users = []
            async for user in message.reactions[0].users():

                if guild.get_member(int(user.id)) is None:
                    continue

                if user.bot:
                    continue

                # check if a user has the role/s from the database
                bypass_status = False
                await mycursor.execute("SELECT bypass_role_id FROM guild_role_settings WHERE guild_id = %s AND bypass_role_id IS NOT NULL", (guild.id,))
                role_exist = await mycursor.fetchone()
                if role_exist:
                    rolelist = role_exist[0].split(" ")
                    for role1 in rolelist:
                        role = guild.get_role(int(role1))
                        if role in user.roles:
                            bypass_status = True
                            break

                if "no_nitro" in entry[3].lower():
                    if user.avatar_url is not None and bypass_status is False:
                        if "gif" in str(user.avatar_url):
                            continue

                    if user.premium_since is not None and bypass_status is False:
                        continue

                elif "msg" in entry[3].lower():
                    msg = entry[3].replace("MSG: ", "")
                    # get the required message count to participate
                    await mycursor.execute("SELECT message_count FROM guild_message_count WHERE guild_id = %s AND user_id = %s", (guild.id, user.id))
                    data = await mycursor.fetchone()
                    if data:
                        if int(data[0]) < int(msg) and bypass_status is False:
                            continue
                    else:
                        if bypass_status is False:
                            continue

                elif "voicetime" in entry[3].lower():
                    seconds = entry[3].replace("VOICETIME: ", "")
                    # get the right voice_time to participate
                    await mycursor.execute("SELECT voice_time FROM guild_voice_time WHERE guild_id = %s AND user_id = %s", (guild.id, user.id))
                    data = await mycursor.fetchone()
                    if data:
                        if int(data[0]) < int(seconds) and bypass_status is False:
                            continue
                    else:
                        if bypass_status is False:
                            continue

                elif "role_id" in entry[3].lower():
                    roleid = entry[3].replace("ROLE_ID: ", "")
                    role = guild.get_role(int(roleid))
                    if role not in user.roles and bypass_status is False:
                        continue

                elif "mitglied" in entry[3].lower():
                    reqtime = entry[3].replace("MITGLIED:", "")
                    if time.time() - user.joined_at.timestamp() < int(reqtime) and bypass_status is False:
                        continue

                if int(user.id) == int(creator_id):
                    continue

                await mycursor.execute("SELECT ignore_role_id FROM guild_role_settings WHERE guild_id = %s", (guild.id,))
                find_data = await mycursor.fetchone()
                if find_data:
                    if find_data[0] is not None and len(find_data[0]) >= 3:
                        rolelist = find_data[0].split(" ")
                        for role1 in rolelist:
                            role = guild.get_role(int(role1))
                            if role in user.roles:
                                continue
                users.append(user)
                count += 1

            if int(count) < int(emb[5]):
                winners = random.sample(users, k=int(count))
                if count <= 0:
                    await mycursor.close()
                    mydb.close()
                    return await message.reply(f"`` › **Zu wenig Teilnehmer:** Ich konnte nur `{count}` Gewinner ziehen, {emb[7]}! <:AmongUs:774306215848181760>")
                zuwenig = True

            else:
                zuwenig = False
                winners = random.sample(users, k=int(emb[5]))

            status = True
            # check if server bot private messages are enabled
            await mycursor.execute("SELECT dm_status FROM guild_misc_settings WHERE guild_id = %s", (entry[0],))
            myresult = await mycursor.fetchone()
            if myresult:
                if myresult[0] == "False":
                    status = False

            role_status = False
            # check if the winner should receive a role
            await mycursor.execute("SELECT win_role_id FROM guild_role_settings WHERE guild_id = %s", (entry[0],))
            myresult = await mycursor.fetchone()
            if myresult:
                if myresult[0] is not None:
                    gwrole = guild.get_role(int(myresult[0]))
                    if gwrole is not None:
                        role_status = True

            for winner in winners:
                if status is True:
                    try:
                        done = discord.Embed(title="<a:COOL:805075050368598036> › **GEWINNSPIEL GEWONNEN!** <a:COOL:805075050368598036>",
                                             description="`` › Lade den Bot **[hier](https://bl4cklist.de/invites/gift-bot)** ein.\n\n"
                                                         f"<a:gift:843914342835421185> › Du hast bei dem Gewinnspiel auf **[{guild.name}]({message.jump_url})** gewonnen!\n"
                                                         f"<a:love:855117868256198767> › Ein Teammitglied wird sich **demnächst** bei dir melden.",
                                             color=0x778beb)
                        done.set_image(url="https://i.imgur.com/fBsIE3R.png")
                        await winner.send(content="Du hast bei einem Gewinnspiel **GEWONNEN!!** <a:blobbeers:862780904112128051>", embed=done)
                    except discord.Forbidden:
                        pass

                if role_status is True:
                    try:
                        await winner.add_roles(gwrole, reason="Gewinnspiel gewonnen!")
                    except discord.Forbidden:
                        pass

            database_winners = " ".join([str(winner.id) for winner in winners])
            winners = ", ".join([winner.mention for winner in winners])

            if winners.count(",") >= 1:
                winnersdesc = f"{winners} haben **{message.embeds[0].title}** gewonnen! <a:love:855117868256198767>"
            else:
                winnersdesc = f"{winners} hat **{message.embeds[0].title}** gewonnen! <a:love:855117868256198767>"

            embed = discord.Embed(title=message.embeds[0].title,
                                  description="`` › Lade den Bot **[hier](https://bl4cklist.de/invites/gift-bot)** ein.\n\n"
                                              "<a:trophy:867917461377404949> **__Gewinnspiel - Gewinner__**\n"
                                              f"<:arrow2:868989719319564359> Gewinner: {winners}\n"
                                              f"<:arrow2:868989719319564359> Erstellt von {emb[7]}\n⠀⠀",
                                  color=0xff4d4d)
            embed.set_footer(text=f"{self.client.user.name} - Bot", icon_url=str(self.client.user.avatar_url))
            embed.timestamp = datetime.now()
            embed.set_thumbnail(url=message.embeds[0].thumbnail.url)

            if zuwenig is False:
                await message.edit(content=":name_badge: **GEWINNSPIEL VORBEI!** :name_badge:", embed=embed)
            else:
                await message.edit(content=f"`` › **Zu wenig Teilnehmer:** Ich konnte nur `{count}` Gewinner ziehen! <:whut:848347703217487912>", embed=embed)

            await mycursor.execute("INSERT INTO guild_finished_giveaways (guild_id, channel_id, message_id, winner_id) VALUES (%s, %s, %s, %s)", (entry[0], entry[1], entry[2], database_winners))
            await mycursor.execute("SELECT COUNT(*) FROM guild_finished_giveaways WHERE guild_id = %s", (entry[0],))
            gcount = await mycursor.fetchone()
            count2 = '{:,}'.format(int(gcount[0])).replace(",", ".")
            count1 = '{:,}'.format(int(count)).replace(',', '.')

            await message.reply(content=f"<a:blobbeers:862780904112128051> **Herzlichen Glückwunsch**, {winnersdesc}\n"
                                        f"› Es gab `{count1}` **gültige** Teilnehmer. Dieses Gewinnspiel war das `{count2}`. auf dem Server. <a:PETTHEPEEPO:772189322392371201>")

            if status is True:
                try:
                    done = discord.Embed(
                        title="<a:Info:810178313733013504> › **GEWINNSPIEL VORBEI!**",
                        description="`` › Lade den Bot **[hier](https://bl4cklist.de/invites/gift-bot)** ein.\n\n"
                                    f"`✅` › Das Gewinnspiel auf **[{guild.name}]({message.jump_url})** ist vorbei!\n"
                                    f"`` › Da du das Event gestartet hast, habe ich **dich informiert.**\n\n"
                                    f"`` › **Zahle das Gewinnspiel** selbst aus, oder kümmere dich\n"
                                    f"`` › darum, dass es die zuständige Person erledigt.",
                        color=0xffa502)
                    done.set_image(url="https://i.imgur.com/fBsIE3R.png")

                    creator = guild.get_member(int(creator_id))
                    await creator.send(content="Ein Gewinnspiel ist **VORBEI!**", embed=done)
                except discord.Forbidden:
                    pass

            await mycursor.execute("DELETE FROM guild_giveaways WHERE guild_id = %s AND channel_id = %s AND message_id = %s", (entry[0], entry[1], entry[2]))

            await mydb.commit()
            mydb.close()
            await mycursor.close()

        print(f"EndTask: {time.time() - start}")

Answer 1

Your most significant problem is that you are using a blocking method -- cursor.execute() -- in your asynchronous code. This is something you shouldn't do, because then your program cannot do anything else while it waits on the results of the query.

In async code, the idea is that every long operation is abstracted away as an awaitable. The function executes until it hits an await keyword. When that happens, execution of this function is suspended until the result of the awaitable becomes available -- but crucially, it frees up the "event loop" to work on something else in the meantime.

For your program, that would mean that while waiting for the result from your database, you could do useful things like listen for new messages. Or send heartbeat packets, because if you don't do it, then Discord will disconnect your bot, and you'll have to wait until you get connected back.

To avoid this kind of blocking, you need to use something that turns a blocking call into an asynchronous call. Some of these are asyncio.to_thread and asyncio.loop.run_in_executor, the latter of which is considered low-level. Here's an example of the former:

import asyncio

async def slow_insert(value):
    """ Bad example, do not do this! """
    # these methods are fast, so they are not an issue
    mydb = getConnection()
    mycursor = mydb.cursor()

    # but this hangs the event loop, and prevents the program from doing anything else
    result = mycursor.execute("INSERT INTO data VALUES (%s)", value)
    return

async def async_insert(value):
    """ Do this instead """
    # these methods are fast, so they can be done directly
    mydb = getConnection()
    mycursor = mydb.cursor()

    # then we create a function that does what we need
    # note that this does not execute the blocking function, it just wraps it into another function
    blocking_insert = lambda: mycursor.execute("INSERT INTO data VALUES (%s)", value)

    # we schedule this function to run on a thread, and only come back here once that thread has completed
    result = await asyncio.to_thread(blocking_insert)
    return result

Ideally, of course, you would use a library that allows you to access your database in an async way, rather than wrapping every call like this. There is in fact such a library for MySQL/MariaDB called aiomysql.

None of this would make your program run faster on its own, but it will make it so that any slowness that happens doesn't cause Discord to drop the connection, and it doesn't prevent your bot from responding to messages. To actually make it faster, you have to optimize the queries.

---

In general, fewer, complicated queries are better than more, simpler queries -- the database server is smart enough to be able to optimize more complicated queries. In your code, there are a lot of simple queries like this:

SELECT ignore_role_id FROM guild_role_settings WHERE guild_id = %s
SELECT bypass_role_id FROM guild_role_settings WHERE guild_id = %s AND bypass_role_id IS NOT NULL
SELECT dm_status FROM guild_misc_settings WHERE guild_id = %s
SELECT win_role_id FROM guild_role_settings WHERE guild_id = %s

Notice how all these queries only depend on group_id. This means that they don't even have to be inside the loop at all, and they can be moved outside it. Perhaps more importantly, you can join them all into one query:

-- SETUP
CREATE TABLE IF NOT EXISTS guild_role_settings (guild_id INTEGER, ignore_role_id INTEGER, bypass_role_id INTEGER, win_role_id INTEGER);
CREATE TABLE IF NOT EXISTS guild_misc_settings (guild_id INTEGER, dm_status BOOLEAN);
CREATE TABLE IF NOT EXISTS some_new_table (guild_id INTEGER, some_new_property INTEGER);
INSERT INTO guild_role_settings VALUES (1, 2, 3, 4);
INSERT INTO guild_role_settings VALUES (2, 0, 0, 0);

INSERT INTO guild_misc_settings VALUES (1, 1);
INSERT INTO guild_misc_settings VALUES (2, 0);
INSERT INTO some_new_table VALUES (1, 1337);
INSERT INTO some_new_table VALUES (2, 0);
-- END SETUP

SELECT
  guild_role_settings.ignore_role_id,
  guild_role_settings.bypass_role_id,
  guild_role_settings.win_role_id,
  guild_misc_settings.dm_status,
  some_new_table.some_new_property
FROM
  guild_misc_settings NATURAL JOIN guild_role_settings NATURAL JOIN some_new_table
WHERE guild_id=1;

Try it online! Also, if you're not clear on what NATURAL JOIN does, take a look at the Wikipedia page with examples. In short, this allows you to perform queries across different tables by considering them as a single table, and querying it as such.

In the case of the queries that include the member ID, you should also combine the two queries into one, but it would probably be better to restructure that part of the code entirely, by first fetching a list of all the members, then performing a query on all of them together, and then doing things with the result of that query:

# instead of this:
for entry in in_database:
  guild = self.client.get_guild(int(entry[0]))
  channel = guild.get_channel(int(entry[1]))
  message = await channel.fetch_message(int(entry[2]))
  async for user in message.reactions[0].users():
    await mycursor.execute("SELECT guild_message_count.message_count, guild_voice_time.voice_time FROM guild_message_count NATURAL JOIN guild_message_count WHERE guild_id = %s AND user_id = %s", (guild.id, user.id))
    msg_count, voice_time = await mycursor.fetchone()
    # do something...

# do this instead:
for entry in in_database:
  guild = self.client.get_guild(int(entry[0]))
  channel = guild.get_channel(int(entry[1]))
  message = await channel.fetch_message(int(entry[2]))

  user_objects = dict()
  user_ids = []
  async for user in message.reactions[0].users():
    user_ids.append(user.id)
    user_objects[user.id] = user

  # now we have the list of user IDs, as well as their corresponding objects

  # we will now form our query of the form
  # SELECT ... WHERE user_id IN (%s, %s, %s, %s)
  # to query all the user IDs at once
  # the idea comes from https://stackoverflow.com/a/283801/5936187
  
  # note that while manipulating SQL strings is usually dangerous
  # because of SQL injections, here we are only using the length
  # of the list as a parameter, so it is okay.

  query = "SELECT user_id, guild_message_count.message_count, guild_voice_time.voice_time FROM guild_message_count NATURAL JOIN guild_message_count WHERE guild_id = %s AND user_id IN "
  parameters = ["%s" for _ in user_ids]
  parameter_string = "(" + ( ", ".join(parameters) ) + ")"
  query += parameter_string

  await mycursor.execute(query, [guild.id] + user_ids)
  # now the cursor has the resultset of (user_id, msg_count, voice_time)
  async for user_id, msg_count, voice_time in mycursor:
    # do something...

This way, you're doing a large query at the start of your function, rather than doing small queries on every iteration.

There might be other optimizations you can do besides these, but so far it isn't very obvious what they could be. Even so, they would be SQL optimizations rather than Python code optimizations, so they would probably be better suited to be put into another question.

Answer 2

I see you have a lot of quarry's in the your loop that's the reason why its so slow. cant you make one big quarry? your mysql database will be quicker in sorting data of python.

maybe some hints to begin:

• i see you sort out non nitro account but i dont see it in your first quarry.
  
• i see you select all messages, maybe only take the messages after the start of the give away? so your script doesn't have to go through all the data.
  
• almost all if statements (about sorting data) can be written in a quarry's
  

im not that good whit databases myself, but if i can see your database and specifically know what you want from the query (i suspect a list of possible winners), maybe i can help. and how long takes this script right now?