The Web is Slow Because of Database Queries in Loops | Hacker News Reader

The Web is Slow Because of Database Queries in Loops

functionwhatwhat.com

11 pointsDrJokepu8 comments

The Web's simple, easily-accessible form made it great for those of us who cut our teeth writing awful-looking Geocities pages with <blink> tags and stealing images from other sites in the early and mid 90s. The syntax is more or less straightforward and easy to comprehend, the format is simple and doesn't need a compiler, and you can write your whole site in notepad if you want.

I think that that's been a blessing and a curse because, damn it, a lot of "web developers" are still stumbling onto concepts that systems developers figured out in the 1970s.

Maybe it's just the perspective that comes with age, but even I know better than to re-establish a database connection every single loop iteration. Isn't this pretty basic stuff? I don't pretend for an instant to be an all-star coder, but surely people have progressed beyond what 16-year-old me could have recognized.

If it's as widespread as the author claims, aren't/shouldn't things like this be taught in your basic CS courses?

> If it's as widespread as the author claims, aren't/shouldn't things like this be taught in your basic CS courses?

Its pretty much been covered in every introductory database book/course/etc. I've seen. The problem is that application developers (and this is by no means limited to web app developers, or application developers which in other domains have limited technical skills) often feel that they don't need to understand databases, and tutorials on application-side technologies that use databases often promote bad use of databases (and, often, ORMs go even further in making bad choices the path of least resistance.)

and, often, ORMs go even further in making bad choices the path of least resistance

And when the ORM alone isn't enough to make an application performant? "Fuck these RDBMS, lets get a real database engine in here!"

Thankfully we are nearing the end of the era in which "object oriented" was considered to mean "superior choice" and the veils are coming off of our eyes. Similarly NoSQL has moved on from the "neat new toy" phase in to the "deconstructing the RDBMS" phase which allows us to have our cake (SQL) and eat it too (PostgreSQL hstore and JSON support).

I don't think OO has ever really been the problem with regard to databases, or even the particular C++ and Java approach to OO, nor do I think SQL engine support for document storage apporaches is a solution any more than SQL engine support for OO structures was.

The problem is developers of applications which rely on persistent data not feeling the need for a more than superficial understanding of the data storage technology they are using, which is a problem independent of programming paradigm or database paradigm or database language.

You and I know this because we did not learn big-O notation to express algorithm efficiency. Instead we learned to think about loops, nesting of loops, what drives the number of loop iterations (per customer, per stock item, per character, per pixel) and we also learned to think about minimizing the work done in loops. Unfortunately many modern developers do not go beyond big O notation, which in many cases is totally irrelevant because they are talking about memory accesses which are light years faster than disk.

Actually, it is also useful these days to thing of whether work is done all in registers, all in L1 cache, all in L2 cache, all in RAM, all in disk cache(a form of RAM), all in disk interface cache(RAM on the hard drive controller), disk, or somewhere across a network connection which could be in the data center, across town, across the country or across the world.

It would be useful if training courses moved that big-O stuff off the the sidelines as a little historical note because it really is a theoretical analysis of algorithms, all things being equal. But in the real world, all things are NOT equal.

Similarly the issues around threading an locks on mutable state which can consume 50% or more of CPU time on a modern processer when an developer tries to roll their own multithreading. Immutable state and interthread queues are the only way to go, and the best way to do it is to explicitly design around the actor model.

To add to this blog post (which I thought was very good, definitely informative) -- This problem is ofter exasterbated by incorrectly/inefficiently used ORMS.

Usually a combination of lazy-loading + loops, or doing things in python/ruby/etc that the SQL/whatever optimizer could have done much faster

There's also approach 5: Pre-caching. 0 ms.

There are so many things that could be pre-cached on most sites, and still, tools and frameworks often optimize for the use case of request -> query -> response.

Reply on news.ycombinator.com