3 ms·

Don't forget the impact of network. I managed to get a several hundred times performance improvement on one occasion because I found a distributed query that wa

Don't forget the impact of network. I managed to get a several hundred times performance improvement on one occasion because I found a distributed query that was pulling back roughly 1M rows over the network and then doing a join that dropped all but 5 - 10 of them. I restructured the query so the join occurred on the remote server and only 5 - 10 rows were sent over the network and, boom, suddenly it's fast. There's always going to be some fixed overhead and latency (and there's a great article about the impact of latency on performance called "It's the latency, stupid" that's worth a read: http://www.stuartcheshire.org/rants/latency.html http://www.stuartcheshire.org/rants/latency.html) but sending far more data than is needed over a network connection will sooner or later kill performance. Overall though, I agree with your considerations, and in roughish terms the order of them.

epr 2y ago
I meant this kind of thing to fall under #1. Don't do work that can be avoided includes pulling 1M rows * a bunch of columns you don't need over the network. From your description though, it doesn't sound like something I'd classify as a network issue. That's just classic orm nonsense. I guess I don't know what you mean by "distributed query", but it sounds terrible. The most classic network performance issue is forgetting to disable nagle's algorithm. The most classic sql performance issue is not using an index.
- bartread 2y ago
  A distributed query is something you execute over multiple instances of your DBMS. I actually would disagree with you that this specific issue was an instance of (1). There wasn't anything wrong with the query per se but rather the issue was with where the bulk of the work in the query was being done: move that work to the right place and the query becomes fast. When considering performance issues, in my experience it's a mistake not to explicitly consider the network.
  - buran77 2y ago
    > I actually would disagree with you that this specific issue was an instance of (1). There wasn't anything wrong with the query per se I think OP's #1 agrees that there's nothing "technically" wrong with such a query (or an algo). It just generated work you didn't have to do. Work takes time. So you used time you didn't have to use. I also think this is the number 1 way of improving performance in general (computer, life). A perfectly valid query of fetching 1M rows turned into 99.xxx% unnecessary work when you only needed a handful of rows. The query wasn't slow, it was just generating more work than you actually needed. The network also wasn't slow, it simply had to transfer (even at peak theoretical efficiency) a lot of data you never used. You then used an equally valid query that wasn't even necessarily fast, it just generated much less work. This query (quote from #1) "eliminate[d] some work entirely", the work of carrying over unnecessary data.

epr 2y ago

I meant this kind of thing to fall under #1. Don't do work that can be avoided includes pulling 1M rows * a bunch of columns you don't need over the network. From your description though, it doesn't sound like something I'd classify as a network issue. That's just classic orm nonsense. I guess I don't know what you mean by "distributed query", but it sounds terrible. The most classic network performance issue is forgetting to disable nagle's algorithm. The most classic sql performance issue is not using an index.

bartread 2y ago
A distributed query is something you execute over multiple instances of your DBMS. I actually would disagree with you that this specific issue was an instance of (1). There wasn't anything wrong with the query per se but rather the issue was with where the bulk of the work in the query was being done: move that work to the right place and the query becomes fast. When considering performance issues, in my experience it's a mistake not to explicitly consider the network.
- buran77 2y ago
  > I actually would disagree with you that this specific issue was an instance of (1). There wasn't anything wrong with the query per se I think OP's #1 agrees that there's nothing "technically" wrong with such a query (or an algo). It just generated work you didn't have to do. Work takes time. So you used time you didn't have to use. I also think this is the number 1 way of improving performance in general (computer, life). A perfectly valid query of fetching 1M rows turned into 99.xxx% unnecessary work when you only needed a handful of rows. The query wasn't slow, it was just generating more work than you actually needed. The network also wasn't slow, it simply had to transfer (even at peak theoretical efficiency) a lot of data you never used. You then used an equally valid query that wasn't even necessarily fast, it just generated much less work. This query (quote from #1) "eliminate[d] some work entirely", the work of carrying over unnecessary data.