Hacker News Reader

Comment by asdasf | Hacker News Reader

>Seven-table joins. Ugh.

Where does this attitude come from in the first place? Even when I was just first learning SQL the notion of doing multiple joins was never off-putting or scary. Quite the contrary, the fact that joining two relations produces a relation which I can then use in more joins seemed like a perfectly elegant abstraction.

>On my laptop, PostgreSQL takes about a minute to get denormalized data for 12,000 episodes

http://www.postgresql.org/docs/9.3/static/sql-createindex.ht...

View on HN

I'm not sure either but I think it's the procedural mindset. People learning about relational databases hear about how a join is equivalent to cartesian product, then imagine the nested-loops implementation and think that k-way join query fundamentally has performance O(n^k).

They haven't wrapped their brain around the declarative mindset that a join doesn't mean looping any more than multiplication implies a loop of additions.

I think it would help if relational databases acted less like black boxes and exposed worst-case performance guarantees for particular queries. AFAIK no DBMS actually promises that an equi-join on an indexed column has constant-time overhead despite being implemented that way.

While we're dreaming of database ponies, I'd love to see this taken even further - a database that lets you register queries (read-only sprocs) in a language that allows specifying worst-case asymptotic run-time. (Eg, "this should be logarithmic in the size of table foo".) The database would be responsible for establishing any indexes needed to meet the requested performance bounds.

It's not quite what you're asking for, but SQL Server Tuning Advisor will recommend indices. Hell, SSMS recommends a missing index if you display the execution plan for a query. Oracle has a tuning wizard too (though I've no experience with that one).

In my limited experience, the Oracle version is a lot more difficult to use than SSTA. (I think) it requires setting up a sampling of a particular query or table, through Grid, and then analysing the results offline.

I love the "missing index" feature of the explain plan in SSMS also - and missed it heavily when I moved to a shop that does Oracle.

It doesn't help when a co-worker writes a query that just left outer joins every table on the server and uses the where clause to filter out the excess...

(found one of those this morning)

Throw a "distinct" in there for good measure

Just make sure they don't find out about recursive common table expressions.

Joins are potentially explosive (cartesian explosion :). So they have to be treated with respect, just as other explosive substances should be. That doesn't mean they should never be used - modern industries use thousands of dangerous things routinely. But proper handling is necessary - indexes, watching the data consistency, etc. So it is a good instinct to feel uneasy when there are seven joins around. It's not necessary fatal but it is something that would require attention.

I've never written an application that didn't involve multiple queries with 7+ joins. There is absolutely no reason that should make anyone feel uneasy, it is perfectly normal and expected.

>On my laptop, PostgreSQL takes about a minute to get denormalized data for 12,000 episodes

I have seen numerous relational databases where people complained they were slow and they had zero indexes. 12k records to any rdbms is nothing. Which touches on another pet peeve of mine when people talk about how much data they have so they have to go nosql, yet they only have thousands or millions of records.

It's because so many of us have seen joins go bad, either due to poor code or other factors. I regularly hear people talk of never doing joins again. Not that I agree with this attitude, but it is certainly out there.

I've been using relational databases for 15 years and I've never "seen joins go bad". I don't even know what that means. I know some people avoid joins, but from my interactions with those people it has always been because they heard the myth that joins are slow, and foolishly figure writing a naive join implementation in PHP will be faster.

Reply on news.ycombinator.com