Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Results of the SQL Performance Quiz

use-the-index-luke.com

Post reply on HN
Print view View original

51–60 of 98 posts

Re: Results of the SQL Performance Quiz

#51

Post by saosebastiao »

dreamdu5t wrote: post #38
bhaak wrote: post #18

Earlier quoted context omitted.

How do you think an ORM will help you avoid those performance issues? Unless the ORM isn't very feature-rich but then you've got other problems anyway. SQL's syntax is ugly because it was designed in the 70s where some people had quite different ideas what a DSL should look like (hey, COBOL, you are guilty, too!)

Ugly compared to what query language of equal power and expressiveness?

Datalog.

I honestly love SQL...I consider it a noble language:) It is the only declarative language out there that is still used today, and there are some solid reasons for that.

However, I believe the biggest mistake that was made in the creation of SQL was to try to emulate natural language flow patterns. This makes it hard to format and edit, and when people optimize towards writability, they kill readability (such as the use of leading commas. The lack of any real standard indentation practice makes it cooperative work frustrating. And the natural language flow actually confuses the writer/reader into thinking that order of operations is linear, as opposed to the FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY->LIMIT order. This makes for really annoying logical bugs, such as how filter conditions in the where clause on a left-joined table can effectively turn your left join into an inner join.

Re: Results of the SQL Performance Quiz

#52

Post by tbrownaw »

tom_b wrote: post #7

I consider the critical path to SQL performance to be understanding what the data looks like and the type of queries to be executed against it rather than general guidelines. To be frank, knowing the difference in how to write an inner and outer join when given a three table schema and a desired output is a frightening filter of candidates. Having an instinct that some type of index could help a query probably makes…

knowing the difference in how to write an inner and outer join when given a three table schema and a desired output is a frightening filter of candidates

Even just asking for a basic understanding at the "use this to look up that" level -- since SQL syntax is easy to learn if you know what you want, and whoever does the recruiting has taken to mostly finding us new college graduates -- filters out an absurd number of candidates.

I guess it's the same thing as makes FizzBuzz a useful question.

Re: Results of the SQL Performance Quiz

#53

Post by [deleted] »

pdubs wrote: post #50
jcampbell1 wrote: post #44

Earlier quoted context omitted.

> It is already a highly selective query. Is it? The first query is always O(1). The worst case for the latter query is that it must aggregate over 999,910 rows. Consider the case where all values of 'a' are 123, and all values 'b' are 42, except 90.

No query optimizer would look at this and say "1M rows? Let's group and aggregate before filtering." Not to mention, the question specifically states that a=? would return 100 rows and a=? and b=? would return 10. Regarding O(1), the first query would be some form of O(n log n) or O(log n) depending on the table/index data structures.

[deleted]

Re: Results of the SQL Performance Quiz

#54

Post by jcampbell1 »

pdubs wrote: post #50
jcampbell1 wrote: post #44

Earlier quoted context omitted.

> It is already a highly selective query. Is it? The first query is always O(1). The worst case for the latter query is that it must aggregate over 999,910 rows. Consider the case where all values of 'a' are 123, and all values 'b' are 42, except 90.

No query optimizer would look at this and say "1M rows? Let's group and aggregate before filtering." Not to mention, the question specifically states that a=? would return 100 rows and a=? and b=? would return 10. Regarding O(1), the first query would be some form of O(n log n) or O(log n) depending on the table/index data structures.

I don't see that a=? rule. By selecting "100" rows out of a million, I think that means 100 distinct dates.

In this case, I take n = 1,000,000

The first query is likely O(log(x)) where x is number of distinct values of a. I approximated that to be O(1) relative to n.

I could be wrong here, or we could have seen different questions, or we are just interpreting the question differently.

Re: Results of the SQL Performance Quiz

#55

Post by icedchai »

henrikschroder wrote: post #40

Interesting, every question in this quiz is about the concept of covering indexes, and figuring out if the query in question is covered by the suggested index or not. I'm surprised people didn't score better on this, it's a very simple concept. :-/

I've interviewed many "senior developers" can't even JOIN two tables. The concept of a "covering index" might as well be brain surgery.

Re: Results of the SQL Performance Quiz

#56

Post by bduerst »

jcampbell1 wrote: post #25
bradleyjg wrote: post #4

The fact that something like this is necessary just goes to show you how much of a failure SQL is compared to what it was supposed to be. Remember the promise of a "declarative language" where you just had to tell it what you want, and it took care of the details?

I reach the opposite conclusion. These are premature optimization tweaks which means tons of people are using SQL successfully without knowing them. You could argue CSS is a failure, because designers don't know that #sidebar .widget is slower than .widget I'd argue that it is evidence CSS is a success because it makes no real difference, and people opt for the more readable solution.

I tried to think of an analogy of someone dragging CSS into this conversation, but I couldn't. Maybe like throwing a dead cat into a room full of aristocrats?

Re: Results of the SQL Performance Quiz

#57

Post by zzzeek »

nwatson wrote: post #8

Hmmm, I'm no SQL jockey, rarely deal with it directly, and currently work with MongoDB. I got 5-of-5 on the PostgreSQL flavor of the test but am sure I'd fail a whiteboard interview on SQL particulars. Being a generalist I can usually figure things out and think I have an intuitive grasp of issues involved in many systems -- but unfortunately only 30% of workplaces understand that intuition, adaptability, and general…

Figuring things out is great, it's how we all learn, and it's an essential skill. But if you lack curiosity and/or respect for the specifics of the technologies you're deploying, the systems you build by intuition alone will ultimately fail.

Re: Results of the SQL Performance Quiz

#58

Post by batbomb »

trhway wrote: post #47
pdubs wrote: post #5

Regarding #5: >That caught me by surprise. Both options “roughly the same” and “depends on the data” got about 25% — the guessing probably. I don't think it was guessing so much as reasoning that fetching 100 rows (and filtering by value) instead of 10 rows doesn't have significant real-world impact unless the row data is particularly large. I'll admit I didn't think of the out-of-index lookup, but my main thought wa…

it has significant impact because first case is "take 100 rows from index" and the second is "take 100 rows from index _and_ for each row go to the row in the table - do the random IO and with 1M rows it is probably 1 IO/row - and check for the value of 'b'" Such 100 random IOs will cost 0.5 sec on 1 iron platter HDD for example. So the query performance will degrade significantly until either the table is already pr…

> Such 100 random IOs will cost 0.5 sec on 1 iron platter HDD for example.

That's an incredibly, incredibly, iffy and mostly wrong statement which depends on arguably a corner case which doesn't often reflect reality (factors include which DBMS, row ordering, table size, cache size, block size, page size, RAM size, Hard Disk seek time, HDD throughput.

The only case where that's likely is performing a very cold query on a very large randomly distributed table once (and probably only once).

Even a table of 1 million rows with ~30B per row could easily be read into memory in about 300ms (100MB read time + ~5ms seek time, or ~= 5+(1e6*rowsize/ (100e3)) )

Query Optimizers do exactly this.

Re: Results of the SQL Performance Quiz

#59

Post by pradocchia »

jcampbell1 wrote: post #44
pradocchia wrote: post #30

Earlier quoted context omitted.

I also answered "about the same", and no I didn't notice the bookmark lookup (so-called on MSSQL), but even if I had I'm not sure I would have changed my answer--well maybe I would have because I would have noticed the "trick", but putting my test-taking adaptations aside.... It is already a highly selective query. Adding a 100 bookmark lookups will not cause a material change in performance, unless this query is bei…

> It is already a highly selective query. Is it? The first query is always O(1). The worst case for the latter query is that it must aggregate over 999,910 rows. Consider the case where all values of 'a' are 123, and all values 'b' are 42, except 90.

> Is it?

At least on MSSQL, I would expect a query plan like so:

  1. Index seek WHERE a = 123, yielding ~100 rows. [1]
  2. Bookmark lookup with results from (1), yielding ~100 rows.
  3. Filter (2) WHERE b = 42 and project date_column, yielding ~10 rows.
  4. Aggregate (3) by date_column, yielding ~10 rows or less.
And the optimizer will choose this over a full table scan so long as the 1+2+3 [1] Important caveat. I interpret this line,

Current situation, selecting about hundred rows out of a million:

....to mean selection of 100 rows from the base table, rather than projection of 100 rows in the result, post-aggregation.

But if he really means a SELECT statement that returns 100 rows, then we have no idea how selective the WHERE clause is, and my answer changes to "The query will be much slower (impact >10%)".

Re: Results of the SQL Performance Quiz

#60

Post by trhway »

batbomb wrote: post #58
trhway wrote: post #47

Earlier quoted context omitted.

it has significant impact because first case is "take 100 rows from index" and the second is "take 100 rows from index _and_ for each row go to the row in the table - do the random IO and with 1M rows it is probably 1 IO/row - and check for the value of 'b'" Such 100 random IOs will cost 0.5 sec on 1 iron platter HDD for example. So the query performance will degrade significantly until either the table is already pr…

> Such 100 random IOs will cost 0.5 sec on 1 iron platter HDD for example. That's an incredibly, incredibly, iffy and mostly wrong statement which depends on arguably a corner case which doesn't often reflect reality (factors include which DBMS, row ordering, table size, cache size, block size, page size, RAM size, Hard Disk seek time, HDD throughput. The only case where that's likely is performing a very cold query…

>> Such 100 random IOs will cost 0.5 sec on 1 iron platter HDD for example.

>That's an incredibly, incredibly, iffy and mostly wrong statement which depends on arguably a corner case which doesn't often reflect reality (factors include which DBMS, row ordering, table size, cache size, block size, page size, RAM size, Hard Disk seek time, HDD throughput.

you're welcome to specify iron platter HDDs which would do 100 random IOs in significant different time than 0.5 sec.

>Even a table of 1 million rows with ~30B per row could easily be read into memory in about 300ms (100MB read time + ~5ms seek time, or ~= 5+(1e6*rowsize/ (100e3)) )

>Query Optimizers do exactly this.

Not always. If it has enough info, it may take a full table scan path in such a case. Still it will be about the same time - 300ms vs. 0.5 sec. i mentioned.

Of course it is for cold queries. You forgot to mention, i guess, that full table scan you propose may by-pass DB cache (default depends on DB, modern tendency is by-pass by default), and thus second query will take the same time, while table blocks brought in by random IO would frequently be stored in DB cache thus making second query run somewhat faster - depends on how much data was brought in.

Post reply on HN