this post was submitted on 02 May 2025
16 points (86.4% liked)
Programming
20356 readers
193 users here now
Welcome to the main community in programming.dev! Feel free to post anything relating to programming here!
Cross posting is strongly encouraged in the instance. If you feel your post or another person's post makes sense in another community cross post into it.
Hope you enjoy the instance!
Rules
Rules
- Follow the programming.dev instance rules
- Keep content related to programming in some way
- If you're posting long videos try to add in some form of tldr for those who don't want to watch videos
Wormhole
Follow the wormhole through a path of communities !webdev@programming.dev
founded 2 years ago
MODERATORS
you are viewing a single comment's thread
view the rest of the comments
view the rest of the comments
What? Problems like this usually come down to some missing indexes. Can you view the query plan for your slow queries? See how long they are taking? IDK about SQL Server but usually there is a command called something like ANALYZE, that breaks down a query into the different parts of its execution plan, executes it, and measures how long each part takes. If you see something like "FULL TABLE SCAN" taking a long time, that can usually be fixed with an index.
If this doesn't make any sense to you, ask if there are any database gurus at your company, or book a few hours with a consultant. If you go the paid consultant route, say you want someone good at SQL Server query optimization.
By the way I think some people in this thread are overestimating the complexity of this type of problem or are maybe unintentionally spreading FUD. I'm not a DB guru but I would say that by now I'm somewhat clueful, and I got that way mostly by reading the SQLlite docs including the implementation manuals over a few evenings. That's probably a few hundred pages but not 2000 or anything like that.
First question: how many separate tables does your DB have? If less than say 20, you are probably in simple territory.
Also, look at your slowest queries. They likely say SELECT something FROM this JOIN that JOIN otherthing bla bla bla. How many different JOINs are in that query? If just one, you probably need an index; if two or three, it might take a bit of head scratching; and if 4 or more, something is possibly wrong with your schema or how the queries are written and you have to straighten that out.
Basically from having seen this type of thing many times before, there is about a 50% chance that it can be solved with very little effort, by adding indexes based on studying the slow query executions.
Currently about ~50. But like 30 of them are the result of splitting them into a common column like "country". In the beginning I assumed this lead to the same as partitioning one large table?
The different queries itself take not long because of the query per se. but due to the limitation of the HDD, SQL reads as much as possible from the disk to go through a table, given that there are now multiple connections all querying multiple tables this leads to a server overload. While I see now the issue with our approach, I hope that migrating the server from SQL server to postgreSQL and to modern hardware + refactoring our approach in general will give us a boost.
Actually no JOIN. Most "complex" query is INSERT INTO with a WHEN NOT EXIST constraint.
But thank you for your advice. I will incorporate the tips in our new design approach.
You really have to see what the db is doing to understand where the bottlenecks are, i.e. find the query plans. It's ok if it's just single selects. Look for stuff like table scans that shouldn't happen. How many queries per second are there? Remember that SSD's have been a common thing for maybe 10 years. Before that it was HDD's everywhere, and people still ran systems with very high throughput. They had much less ram then than now too.