#🔒 I have a big database, my script does thousands of queries, need them to be fast
nova crescent
amazing I have to write everyuthig again because of the bot
clever obsidian
My db is around 150gb, I'm using postgresql, I will share my db setup.
Initially I need to iterate over every event, then for each event, one more query regarding odds (lets use full time result table)
I was querying all events in a given timeframe, then iterating over them and querying for odds in each iteration, but this took a lot of time.
The whole process had an estimated of 5 hours
wild hornetBOT
nova crescent
SQL Schema
https://paste.pythondiscord.com/6XWA
Before, with sqlite, I was doing
# self.database.fetch_closing_odds
def fetch_closing_odds(self, league_ids=None, books=None):
"""
Recupera las cuotas de cierre de los eventos, opcionalmente filtradas por ligas y casas de apuestas.
Parameters:
- league_ids (list[int]): Lista de IDs de ligas para filtrar eventos (opcional).
- books (list[str]): Lista de nombres de casas de apuestas para filtrar cuotas (opcional).
Returns:
- list[dict]: Lista de diccionarios con las cuotas de cierre.
"""
query = """
SELECT
ftr.event_id,
ftr.home_odd,
ftr.draw_odd,
ftr.away_odd,
ftr.source
FROM full_time_result ftr
JOIN (
SELECT event_id, source, MAX(add_time) AS max_add_time
FROM full_time_result
GROUP BY event_id, source
) latest
ON ftr.event_id = latest.event_id AND ftr.source = latest.source AND ftr.add_time = latest.max_add_time
"""
conditions = []
params = []
if league_ids:
conditions.append(
"ftr.event_id IN (SELECT id FROM events WHERE league_id IN ({}))".format(
",".join("?" for _ in league_ids)
)
)
params.extend(league_ids)
if books:
conditions.append("ftr.source IN ({})".format(",".join("?" for _ in books)))
params.extend(books)
if conditions:
query += " WHERE " + " AND ".join(conditions)
self.cursor.execute(query, params)
rows = self.cursor.fetchall()
return [
{
"event_id": row[0],
"home_odd": row[1],
"draw_odd": row[2],
"away_odd": row[3],
"source": row[4],
}
for row in rows
]
# self.database.fetch_events_info
def fetch_events_info(self, start_ts, end_ts):
"""
Devuelve filas (id, home_score, away_score, time) de 'events',
filtradas por start_ts y end_ts si están dados.
"""
query = "SELECT id, home_score, away_score, time FROM events"
conditions = []
params = []
if start_ts is not None:
conditions.append("time >= ?")
params.append(start_ts)
if end_ts is not None:
conditions.append("time <= ?")
params.append(end_ts)
if conditions:
query += " WHERE " + " AND ".join(conditions)
self.database.cursor.execute(query, params)
return self.database.cursor.fetchall()
# Calculate Alphas
odds_data = self.database.fetch_closing_odds(league_ids = league_ids, books = sources)
events_info = self._fetch_events_info(start_time, end_time)
event_data = {row[0]: row for row in events_info}
for row in odds_data:
# Perform calculations
...
and this took like... 15 mins
proud lion
if you paste your database schema (the table create commands), it will help
nova crescent
SQL Schema
https://paste.pythondiscord.com/6XWA
here
proud lion
oh, sorry I missed that
nova crescent
np 🙂
proud lion
have you tried analyzing the queries yet?
EXPLAIN SELECT ...
you'll want to make sure there are no unnecessary sequential scans there
nova crescent
never tried that
harsh summit
maybe do some of the compairisons inside the db?
proud lion
how can this work anyway? the events table does not have a column named time according to your schema
why do you have a multi-column PK in events?
nova crescent
right, I went from sqlite to postgresql, now time (which was an int ) is event_time (which is now a naive datetime.datetime object)
proud lion
to what end?
nova crescent
because odds have like 1 000 000 000 rows
proud lion
and what does the partitioning have to do with either?
nova crescent
I made a partition based on event time
and to partition based on event time, event time needs to be the PK
or part of a multi column pk
well the thing is, I dont care about event time as pk
I do care about id as pk
hencethe multicolumn pk
proud lion
ok so which query seems slow? is there a way for you to reduce the number of the queries?
harsh summit
could chuck quieries, could grab all and then chunk and use multiprocessing if running it singular is slow.
burnt lodge
I am not great at databases, but in case you think that you cannot optmize the queries anymore and they are still very slow, I recommend you checkout duck db, its good for fast complex queries and have good support for python
rugged carbon
@nova crescent you should look into joins and such constructs to do things inside the database instead of pulling the data out and looping over it from the program
this is why you should use relational databases
harsh summit
I still think running and pre-selection/case/match criteria the db offers will spped up processing as prefilter.
nova crescent
<@850444002582593567> you should look into joins and such constructs to do thing...
Dont I have a relational db?
rugged carbon
and don't do premature optimization of the database unless you know exactly what is causing the slowness
rugged carbon
Dont I have a relational db?
yes, you do, you just aren't leverage it as such
not fully anyway
and you should be optimizing the database after you have analyzed the execution plan
nova crescent
Ive no clue what optimization is about tbh
rugged carbon
Ive no clue what optimization is about tbh
it's a whole subject in itself, i would consult a professional DBA or other database expert (i'm nether, just know some basics) for help with this or you have a lot of reading up and learning to do
nova crescent
this is a personal project, and I dont have a contact for that
rugged carbon
this is a personal project, and I dont have a contact for that
i see, it's hard to even try things out without having the data and being able to test things yourself, especially if you don't know these things really really well (which i don't)
nova crescent
I can share the db
rugged carbon
I can share the db
that is quite dangerous, you probably shouldn't do that with random people on the internet
but i'm a bit surprised of how you have amassed that amount of data in your database for a personal project
@nova crescent i do know that there is some really good database people hanging out in #databases
nova crescent
but i'm a bit surprised of how you have amassed that amount of data in your data...
Not even that difficult
Paid for betsapi.com package
Been pulling data for a week
It might end up around 400GB when Im done downloading
I'll need queries to be fast, cant wait a week for calculations 😄
nova crescent
so what are your recommendations for my situation?
rugged carbon
I'll need queries to be fast, cant wait a week for calculations 😄
that is understandable
nova crescent
10 months worth of queries was like 5 hours eta, imagine 4 years
rugged carbon
so what are your recommendations for my situation?
have you tried running one of your queries with explain as suggested above?
rugged carbon
10 months worth of queries was like 5 hours eta, imagine 4 years
yeah, if it's linear time it sounds like you could wait over 24 hours then, but it might be much worse then that it it grinds to a crawl due to the amount of data to search through
nova crescent
Not home, once Im back Ill use the rewt of the day to try and understand the problem Im facing
Ill send more info here when I have it
rugged carbon
@nova crescent what kind of disk is your database stored in and how much ram do you have?
nova crescent
yeah, if it's linear time it sounds like you could wait over 24 hours then, but ...
It is linear
nova crescent
<@850444002582593567> what kind of disk is your database stored in and how much ...
32gb, hdd
rugged carbon
32gb, hdd
if that is spinning 7200 rpm or maybe even lower i can definitely see that being slow compared to a ssd, or better yet, a high performance nvme drive
nova crescent
but my previous sqlite db was also in my hdd
rugged carbon
but my previous sqlite db was also in my hdd
but i'm guessing the sqlite database wasn't nearly as big, because sqlite really isn't made for that amount of data
rugged carbon
And my current postgres is around 100
postgres on the other hand is made to handle terabytes of data without a problem on the right type of hardware, configuration and database structure
nova crescent
if it handle teras, it shpuld be able to handle gbs
I mean Im sure I can reduce the execution time at least to sqlite level
Im also sure my db struct, config and hw could be better
rugged carbon
if it handle teras, it shpuld be able to handle gbs
it definitely should, but it is much more complex and needs more administration and expertise
nova crescent
I also agree, and I dont have that, but it has to be possibleeee ðŸ˜
If sqlite was fast enough...
Unless I just copy everything into a sqlite db lol
rugged carbon
if you are up for some reading: https://www.postgresql.org/docs/current/performance-tips.html
nova crescent
Yeah Im defo available
rugged carbon
starting with explain
nova crescent
Tbh Ive been using o1 A LOT for this, it would have been a long lasting time project, it ended up being one though, and still I have a lot to do (like performance)
wild hornetBOT
Python help channel closed for inactivity