high risk/(hopefully) reward situation yes. But also probably because I am lazy - or lets say, I dont want to change my private life center because of the job.
It always boils down to many factors and my gut feeling tells me thats the best compromise. Or its the anxiety to push against change. nobody knows.
But thanks. may your life be prosperous. Especially if mine wont. ;)
The thing is, I am already in a niche - since I am coming from the engineering / non-IT side and sliding into development. Probably the start-up character of my company doesnt help here expecting high income for now.
But I am going with either earning, learning or leaving. And since I learn so fucking much I cannot leave.
Since all trains commute via Salzburg - this is probably the most classic city to visit.
Lots of culture. Probably lots of tourists too. It is a beautiful town - with many options to enjoy a beautiful day. probably enough places to enjoy also during bad weather.
If you like it quiet Linz is also an option. but to be honest Linz is probably worth a day trip without the need to stay overnight - except you want to visit nightlife.
Nevertheless given your preferences to not divert from the main train line - Salzburg is the way to go.
broadly you want to locate individual records as quickly as possible by using the most selective criteria
What can be more selective than "if ID = "XXX"? Yet the whole table still has to be reviewed until XXX is found?
... and to familiarize yourself with normalization.
based on a quick review of normalization, I doubt that this helps me - as we are not experiencing such links in the data. For us we "simply" have many products with certain parameters (title, description, etc.) and based on those we process the product and store the product with additional output in a table. However to not process products which were already processed, we want to dismiss any product which is in the processing pipeline which is already stored in the "final" table.
It isn’t just a big bucket to throw data into to retrieve later.
thats probably the biggest enlightment I have got since we started working with a database.
Anyway I appreciate your input. so thank you for this.
Ms sql is trash Running anything on a Hdd is a joke
thank you, I convinced management to migrate to a modern hardware, and we switch to PostgreSQL together with refactoring our design and approach.
You read write and compare continuously? Did you try to split it into smaller chunks?
Locks are handled by SQL. but yes, multiple tables are read, written and the final table compared with multiple requests/transactions (connections?) simultaneously. Split into smaller chunks would nonetheless mean that the query would loop through the whole table - in chunks? how would this help with simultaneous transactions?
A hot DB should not run on HDDs. Slap some nvme storage into that server if you can. If you can’t, consider getting a new server and migrating to it.
Did this because of the convincing replies in this thread. Migrating to modern hardware and switch SQL server with PostgreSQL (because its used by the other system we work with already, and there is know-how available in this domain).
You should avoid scanning an entire table with a huge number of rows when possible, at least during requests.
But how can we then ensure that I am not adding/processing products which are already in the "final" table, when I have no knowledge about ALL the products which are in this final table?
Create an index and a table constraint on the relevant columns. ... just so that the DB can do the work for you. The DB is better at enforcing constraints than you are (when it can do so).
This is helpful and also what I experienced. In the peak of the period where the server was overloaded the CPU load was pretty much zero - all processing happened related to disk read/write. Which was because we implemented poor query design/architecture.
For read-heavy workflows, consider whether caches or read replicas will benefit you.
May you elaborate what you mean with read replicas? Storage in memory?
And finally back to my first point: read. Learn. There are no shortcuts. You cannot get better at something if you don’t take the time to educate yourself on it.
Yes, I will swallow the pill. but thanks to the replies here I have many starting points on where to start.
RTFM is nice - but starting with page 0 is overwhelming.
Yes, we are currently in the process of migrating to PostgreSQL and to a new hardware. Nonetheless the approach we are using is a disaster. So we will refactor our approach as well. Appreciate your input.
All processing and SQL related transactions are executed via python. But should not have any influence since the SQL server is the bottleneck.
Yes I have considered this already for the next update. Since our setup can accept dirty reads - but I have not tested/quantified any benefits yet.
While I understand the underlying issue here, I do not know yet how to control this. Since we have multiple microservices set up which are connected to the DB and either fetch (read), write or delete from different tables. But to my understanding since I am currently not using NOLOCK such occurrences should be handled by SQL no? What I mean is that during a process the object is locked - so no other process can interfere on the SQL object?
Thanks for putting this together I will review it tomorrow again (Y).