Comment by ako | Hacker News Reader

Comment by ako | Hacker News Reader

Parent Full thread

The problem with procedural is that it doesn’t optimize very well. The fastest way to get your data for a large number of random queries often depends on the data size, the available indexes, but is also influenced by changes in data size, etc. What is fast for a small dataset might be slow for a larger dataset. The right algorithm also depends on your filters and caching.

It’s almost impossible to write procedural queries that always perform, that is a really hard task. That’s why we use a declarative query language, I tell the database what data I need, and the database optimizer will determine the best performing algorithm to fetch the data based on all dynamic statistics it has.

Don’t underestimate how much hard work the database optimizer takes care of, I’m glad I don’t have to program all of that myself.

Better get used to this way of working, it resembles pretty much how AI assists us.

I have written 4 query optimizers: HypersonicSQL, H2 database, PointBase Micro, and Apache Jackrabbit Oak. I know procedural language don't have such optimizers. But I argue that you don't always need them. I argue that the database engine query optimizer is often more a risk than a help. I have seen missing indexes far too often. SQL doesn't require that the programmer thinks how the data is accessed, so the result is that too many programmers don't think about it, and so don't add indexes, request too much data, and so on. The SQL statements work fine with small (development) data sets, so the same statements are used with production databases, and you run into problems too late.

I don't think it's hard to write procedural queries that are always fast. You write a loop or map/filter/collect method. You just explicitly need to mention which index to use, is all.

Agree on the missing indexes, was in a debugging session yesterday where missing indexes turned out to cause some issues.

But I have little faith that people who forget indexes are capable of writing procedural queries that are always fast. These less experienced devs are the ones that benefit most from the query optimizer doing the hard work. I think the real solution is for database to automatically create indexes where needed, as some database already do. And some better visual query editor like ultorg.

Reply on news.ycombinator.com