6 ms·

I've worked in projects where no one seemed to know SQL, where massive speed improvements were made by fixing very low hanging fruits like removing select * que

I've worked in projects where no one seemed to know SQL, where massive speed improvements were made by fixing very low hanging fruits like removing select * queries, adding naive indexes, removing N+1 queries etc. Likewise, I've worked in code bases where performance had been dreadful, yet there were no obvious bottlenecks. Little by little, replacing iterators with loops, objects/closures with enum-backed structs/tables, early exits and so on accumulating to the point where speed ups ranged from 2X to 50X without changing algorithms (outside of fixing basic mistakes like not pre allocating vectors). Always fun to see these videos. I highly recommend his `Performance Aware Programming` course linked in the description. It's concise and to the point, which is a nice break from his more casual videos/streams which tend to be long-winded/ranty.

pocket_cheese 4y ago
Could you elaborate what you mean by "naive indexes"?
- fabrice_d 4y ago
  In general that means looking at the sql query plan for a slow query, and adding appropriate indexes when there are full table scans.
- ocimbote 4y ago
  I assume "naive" means "simple, basic" here. As in "index this column. Period".
- dgb23 4y ago
  Not GP. But you can often anticipate where indexes have to go for common queries. There might be some important colums like say a “status” or a “date”, which are fundamental to a lot of queries. Or you have colums X and Y being used frequently or importantly in where clauses together, then that’s a candidate for composite indexes. Stuff like that.
  - hinkley 4y ago
    I think historically people had a lot of bad intuitions about how effective non-composite indexes are in databases. That if I have an index for A and an index for B I should be able to do A & B and get a quick answer. There's been a lot more educational material on composite indexes and in particular partial indexes and so I'm not sure if someone with 3 years' experience today can accurately judge a conversation talking about ten years ago.
- scotty79 4y ago
  If some field is referenced in WHERE clause, add an index for it. If there are a few fields referenced in a single WHERE add a single index that includes all of them. If you have index that has a, b, c then it is as if you also had indexes a, b and a. If condition in WHERE is = put this field at the beginning of an index. If it's < (or similar) put it at the end. You'll get best results if you have none or only one < in your query.
dgb23 4y ago
I can very much relate to this. Just taking the little bit of time to think about what the computer needs to do and making a reasonable effort to not do unnecessary stuff goes a long way. That 2x-50x factor is in fact very familiar. That’s something loading in a second rather than in a minute, or something feeling snappy instead of slightly laggy. And it matters much more than people say it does. The “premature optimisation...” quote has been grossly misused to a degree that it’s almost comical. It’s not a good excuse for being careless.
- jay_kyburz 4y ago
  One thing I find frustrating in game development, is we often put off optimization until the end of the project when we know we are happy with the game. But that means _I_ have to live with a slow code base for 2 years. It takes 19 seconds for the main menu to load when you push play on our current game in Unity. It's killing me. Meanwhile in my lua side project, its less than a second.
  - variadix 4y ago
    Indeed. Having to deal with slow code (and slow compile times to some extent) for the majority of the project’s lifespan ruins iteration rate.
iamcreasy 4y ago
> I've worked in projects where no one seemed to know SQL, where massive speed improvements were made by fixing very low hanging fruits like removing select * queries, adding naive indexes, removing N+1 queries etc. Can you recommend any SQL book with main focus on performance improvements like this?
- doodlesdev 4y ago
  Just know SQL, as noted these are really low hanging fruit. If you know SQL you should know about these.
- fredrikholm 4y ago
  I've learnt as I've needed it, so I'm afraid I don't have a single source to point at. Of the things that I've listed, a quick google search on each should give you enough info to be useful. > Select * queries Sometimes you only want two columns, but you ask for 5. Say you query a million rows, where you ask for but throw away 60% of all the data you get back. > Naive indexes As in, just slapping an index on a table that doesn't have one makes such a big difference that sometimes it's all you need. > N+1 queries This is more of a problem in ORMs, but any time you call the database N times instead of 1 time. A classic example is writing a for loop that asks for one row at a time, instead of asking for all rows once.
- bart_spoon 4y ago
  https://use-the-index-luke.com/ https://use-the-index-luke.com/
scotty79 4y ago
> adding naive indexes You get 90% of the improvement just from that. Indexes are the whole reason anyone even uses databases. And yet some backend guys think they are optional.

pocket_cheese 4y ago

Could you elaborate what you mean by "naive indexes"?

fabrice_d 4y ago
In general that means looking at the sql query plan for a slow query, and adding appropriate indexes when there are full table scans.
ocimbote 4y ago
I assume "naive" means "simple, basic" here. As in "index this column. Period".
dgb23 4y ago
Not GP. But you can often anticipate where indexes have to go for common queries. There might be some important colums like say a “status” or a “date”, which are fundamental to a lot of queries. Or you have colums X and Y being used frequently or importantly in where clauses together, then that’s a candidate for composite indexes. Stuff like that.
- hinkley 4y ago
  I think historically people had a lot of bad intuitions about how effective non-composite indexes are in databases. That if I have an index for A and an index for B I should be able to do A & B and get a quick answer. There's been a lot more educational material on composite indexes and in particular partial indexes and so I'm not sure if someone with 3 years' experience today can accurately judge a conversation talking about ten years ago.
scotty79 4y ago
If some field is referenced in WHERE clause, add an index for it. If there are a few fields referenced in a single WHERE add a single index that includes all of them. If you have index that has a, b, c then it is as if you also had indexes a, b and a. If condition in WHERE is = put this field at the beginning of an index. If it's < (or similar) put it at the end. You'll get best results if you have none or only one < in your query.

dgb23 4y ago

I can very much relate to this. Just taking the little bit of time to think about what the computer needs to do and making a reasonable effort to not do unnecessary stuff goes a long way. That 2x-50x factor is in fact very familiar. That’s something loading in a second rather than in a minute, or something feeling snappy instead of slightly laggy. And it matters much more than people say it does. The “premature optimisation...” quote has been grossly misused to a degree that it’s almost comical. It’s not a good excuse for being careless.

jay_kyburz 4y ago
One thing I find frustrating in game development, is we often put off optimization until the end of the project when we know we are happy with the game. But that means _I_ have to live with a slow code base for 2 years. It takes 19 seconds for the main menu to load when you push play on our current game in Unity. It's killing me. Meanwhile in my lua side project, its less than a second.
- variadix 4y ago
  Indeed. Having to deal with slow code (and slow compile times to some extent) for the majority of the project’s lifespan ruins iteration rate.

iamcreasy 4y ago

> I've worked in projects where no one seemed to know SQL, where massive speed improvements were made by fixing very low hanging fruits like removing select * queries, adding naive indexes, removing N+1 queries etc. Can you recommend any SQL book with main focus on performance improvements like this?

doodlesdev 4y ago
Just know SQL, as noted these are really low hanging fruit. If you know SQL you should know about these.
fredrikholm 4y ago
I've learnt as I've needed it, so I'm afraid I don't have a single source to point at. Of the things that I've listed, a quick google search on each should give you enough info to be useful. > Select * queries Sometimes you only want two columns, but you ask for 5. Say you query a million rows, where you ask for but throw away 60% of all the data you get back. > Naive indexes As in, just slapping an index on a table that doesn't have one makes such a big difference that sometimes it's all you need. > N+1 queries This is more of a problem in ORMs, but any time you call the database N times instead of 1 time. A classic example is writing a for loop that asks for one row at a time, instead of asking for all rows once.
bart_spoon 4y ago
https://use-the-index-luke.com/ https://use-the-index-luke.com/

scotty79 4y ago

> adding naive indexes You get 90% of the improvement just from that. Indexes are the whole reason anyone even uses databases. And yet some backend guys think they are optional.