Programming

All the stackoverflow answers lead to - its complex. read a 2000 page book.

This is an exceptionally good answer and you're doing everything possible to avoid doing it, when you could have been half way done with the book by now probably. Database administration is a profession, not a job. It requires specialized training to do it well and doing everything possible to avoid that training and knowledge won't help you one bit.

my queries are not that complex.

It doesn't matter. Your database is very complex.

they simply go through the whole table to identify any duplicates

You search 10 million records on every request and you wonder why it's slow?

is there a wizard move to bypass any of my restriction or is a change in the setup and algorithm inevitable?

No. Database administration is very difficult. Reading that 2000 page book is essential for setting up infrastructure to avoid a monolithic setup like this in the first place.

the other culprit is that our server runs on a HDD which is with 150mb read and write per second probably on its edge.

lol wtf

Realistically, this setup is 10 years too old. How large is your database? Is there any reason why it can't be run in memory? 10 million lines isn't insurmountable. Full text with a moderate number of tables could be ~10GB--no reason that can't be run in memory with Redis or other in-memory database or to update to a more modern in-memory database solution like Dice.

Your biggest problem is the lack of deduplication and normalization in your database design. If it's not fixed now, it'll simply get worse YOY until it's unusable. Either spend the time and money now, or spend even more time and money later to fix it. 🤷‍♂️

tl;dr: RTFM.

• spent time to generate/optomize your indexes.
• faster storage/cpu/ram for your rdbms
• get the data needed by specific services into the service, only get the data from a central place if you have to (spinning up a new instance, another service changes state of data you need, which is a warning sign in itself that your architecture is brittle...)
• faster storage/cpu/ram
• generate indexes
• 2nd level cache shared between services
• establish a faster datastore for often requested data thats used by multiple services (that might be something like redis, or another rdbms on beefier hardware)
• optimize queries
• generate indexes
• faster storage/cpu/ram

Indexes and pagination would be good starts

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.

While I get that SO can be monstrously unhelpful, database optimization is a whole profession so I think we need a bit more to help

A few directions we could go here: Post your SQL query. This could be a structure or query issue. Best case, we could do some query optimization. Also, have you looked into indexing?

Where are your bottlenecks coming from? Is your server desined for a I/O intensive workload like databases. Sequential read speed is not a good metrix.

What about concurrency? If this is is super read/write intensive, optimization could depend on where data is written while you're reading

Lotta smarter people than me have already posted better answers in this thread, but this really stood out to me:

the thing is. my queries are not that complex. they simply go through the whole table to identify any duplicates which are not further processed then, because the processing takes time (which we thought would be the bottleneck). but the time savings to not process duplicates seems now probably less than that it takes to compare batches with the SQL table

Why aren't you de-duping the table before processing? What's inserting these duplicates and why are they necessary to the table? If they serve no purpose, find out what's generating them and stop it, or write a pre-load script to clean it up before your core processing queries access that table. I'd start here - it sounds like what's really happening is that you've got a garbage query dumping dupes into your table and bloating your db.

To paraquote H. L. Mencken: For every problem, there is a solution that's cheap, fast, easy to implement -- and wrong.

Silver bullets and magic wands don't really exist, I'm afraid. There's amble reasons for DBA's being well-paid people.

There's basically three options: Either increase the hardware capabilities to be able to handle the amount of data you want to deal with, decrease the amount of data so that the hardware you've got can handle it at the level of performance you want or... Live with the status quo.

If throwing more hardware at the issue was an option, I presume you would just have done so. As for how to viably decrease the amount of data in your active set, well, that's hard to say without knowledge of the data and what you want to do with it. Is it a historical dataset or time series? If so, do you need to integrate the entire series back until the dawn of time, or can you narrow the focus to a recent time window and shunt old data off to cold storage? Is all the data per sample required at all times, or can details that are only seldom needed be split off into separate detail tables that can be stored on separate physical drives at least?

Do you remember the part of education where they talked about tradeoffs? How making decision a means x, y, x good things and a, b, c bad things? Because it’s reading strongly like your system design methodology was “this is the path of least resistance so I’m doing that”.

Most code is not complex. Good code is usually very easy to read and understand.

Just because you can read and understand the queries you wrote doesn’t mean they’re efficient or that you’re using good design.

So yes. Stack Overflow is going to tell you to RTFM. Because someone needs to sit down with this mess, determine the pros and cons of the system design, and figure out where to start overhauling.

What is the execution path? What indexes are being hit? What are the keys? Can you separate things by, for example, category since dupes wouldn't typically exist there? There are lots of potential things that might tell you more or improve performance, but this is super vague.

sounds like some changes would be a good idea 😅

Database performance tuning is its own little world and there are lots of complexities and caveats depending on the database system.

With MSSQL, the first thing you should check is your indexes. You should have indexes on commonly queried fields and any foreign keys. It's the best place to start because indexing alone can often make or break database performance.

could you tell me what book it is 👀

If you are new to something and want to learn, seek resources and educate yourself with them. Learning takes time, and there are no shortcuts.

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.

SQL server can generate execution plans for you. For your queries, generate those, and see if you're doing any operations that involve iterating the entire table. You should avoid scanning an entire table with a huge number of rows when possible, at least during requests.

If you want to do some kind of dupe protection, let the DB do it for you. Create an index and a table constraint on the relevant columns. If the data is too complex for that, find a way to do it, like generating and storing hashes, sorting lists/dicts, etc 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).

For read-heavy workflows, consider whether caches or read replicas will benefit you.

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.

Ms sql is trash

Indexes are great but probably don't get you far if it is already really slow.

Running anything on a Hdd is a joke

You read write and compare continuously? Did you try to split it into smaller chunks?

"They simply go through the whole table".. that's the problem. A full table scan should be avoided at all costs.

Learn: how to run and read an explain plan, indexes, keys, constraints, and query optimization (broadly you want to locate individual records as quickly as possible by using the most selective criteria).

You also need to learn basic schema design and to familiarize yourself with normalization.

Avoid processing huge result sets in your application. The database is good at answering questions about data it contains. It isn't just a big bucket to throw data into to retrieve later.