3 ms·
> Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution. This is exactly what the parent comment said
by CharlesW 6y ago
> Then we looked at it, and with a few changes in indexes and query hints brought it down to sub-second execution.
This is exactly what the parent comment said: "you should try to build your application so queries and transactions are very short".
If you're claiming that the parent is incorrect about "sometimes, the only answer is to distribute the queries across other boxes", my guess is that probably don't work at a scale where you've learned that query optimization can't solve every database performance problem.
- btilly 6y agoBased on my past experiences, I'd be happy to take an even money bet that more than 95% of organizations that go distributed for performance reasons actually caused themselves more problems than they solved. This does NOT mean that there are no use cases for distributed - I've seen Google's internals and it would be impossible to do that any other way. But it does mean that when someone is telling you with a straight face that they needed horizontal scalability for performance, you should assume that they were probably wrong. (Though probably saying that is not the wisest thing - particularly if the person you're talking to is the architect whose beautiful diagrams you'd be criticizing.) So yes, there are problems that require a distributed architecture for performance problems. That doesn't contradict the point that every other option should be explored first.
- CharlesW 6y ago> Based on my past experiences, I'd be happy to take an even money bet that more than 95% of organizations that go distributed for performance reasons actually caused themselves more problems than they solved. Oh, I'm absolutely positive you're right! And as you know, even in distributed architectures there's a huge range of solutions from "let's do these n reasonably simple things" to "let's just rebuild our system to solve every conceivable future problem". I don't know of a scenario where the latter has ever worked.
- mmacedo_en 6y agoI'm referring to parent's point "Sometimes you just have to do joins across large tables.". Indexes and query hints can make a world of difference once you profile how the query is being executed. Another option is to create a stored procedure which first extracts a smaller amount of data from first table to a temporary table and then join it to the other large table. Measuring and testing different optimization methods takes a lot of effort and time. We should only do that when faced with a real problem of slowness. "The first rule of optimization is: Don't do it. The second rule of optimization (for experts only) is: Don't do it yet. Measure twice, optimize once."