5 ms·

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

by bcoates 13y ago

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.

loqi 13y ago
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.
- pdubs 13y ago
  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).
  - jsmeaton 13y ago
    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.
mcdougle 13y ago
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)
- schrodinger 13y ago
  Throw a "distinct" in there for good measure
- twic 13y ago
  Just make sure they don't find out about recursive common table expressions.

by bcoates 13y ago

loqi 13y ago

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.

pdubs 13y ago
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).
- jsmeaton 13y ago
  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.

mcdougle 13y ago

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)

schrodinger 13y ago
Throw a "distinct" in there for good measure
twic 13y ago
Just make sure they don't find out about recursive common table expressions.