First question: how many separate tables does your DB have? If less than say 20, you are probably in simple territory.
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?
Also, look at your slowest queries
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.
They likely say SELECT something FROM this JOIN that JOIN otherthing bla bla bla. How many different JOINs are in that query?
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.
This can be the new slogan of our development. :')
I have convinced management to switch to a modern server. In addition we hope refactoring our approach (no random reads, no dedupe processes for a whole table, etc.) will lead us somewhere.
Actually now. We are adding a layer of processing products to an already in-production system which handles already multiple millions of products on a daily basis. Since we not only have to process the new/updated products but have to catch up with processing the historical (older) products as well its a massive amount of products. We thought since the order is not important to use a random approach to catch up. But I see now that this is a major bottleneck in our design.
so no. No narrowing.
Also no IMO. since we dont want a product to be processed twice, we want to ensure deduplication - this requires knowledge of all already processed products. Therefore comparing with the whole table everytime.