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}")
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.