Hacker News Reader

Comment by fredrikholm | Hacker News Reader

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.

View on HN

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.

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.

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.

> 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?

Just know SQL, as noted these are really low hanging fruit. If you know SQL you should know about these.

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.

https://use-the-index-luke.com/

> 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.

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

In general that means looking at the sql query plan for a slow query, and adding appropriate indexes when there are full table scans.

I assume "naive" means "simple, basic" here. As in "index this column. Period".

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.

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.

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.

Reply on news.ycombinator.com