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

11–20 of 98 posts

Re: Results of the SQL Performance Quiz

#11

Post by sokoloff »

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?

SQL is a reasonably declarative language in that it will give you the correct answer without you knowing all the gory internal details.

This quiz is about performance tuning, not correctness. For plenty of valuable real-people use cases, it's plenty fast without any performance tuning.

Re: Results of the SQL Performance Quiz

#12

Post by mattfenwick »

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…

Just curious -- where did the "30%" figure come from?

Re: Results of the SQL Performance Quiz

#13

Post by brianberns »

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…

I had the same thought. Seems like the optimizer could still perform an index-only scan to get to 100 rows, then go to the table to filter them down to 10 rows. Yes, the second step is extra, but should still be fast. What am I missing?

Re: Results of the SQL Performance Quiz

#14

Post by Robin_Message »

troels wrote: post #2

Maybe I'm just a bit slow, but what's going on with the 50/50 "guessing score" reasoning? Surely, you should half the wrong answers as well, rather than just subtracting it from the right answers?!?

If proportion X (between 0 and 1) of the population knows the right answer (we'll call A, and the wrong answer B), and the other half guesses evenly, you would expect:

    A = X + (1 - X) / 2
So to calculate X, given A,

    2A = 2X + 1 - X = X + 1
    X = 2A - 1
The author implied the formula

    X = A - 0.5
which is not correct. In particular, assume everyone got the answer right (X=A=1). Then the assertion that half of the answers are correct guesses is absurd.

The correct lucky guess fraction (amount of the right answers to discard) is:

    A - X = A - (2A - 1) = 1 - A
If A is near to 0.5 (which we would expect if our model is accurate and few people know the answer, the 50% approximation the author used is about right.)

Re: Results of the SQL Performance Quiz

#15

Post by herge »

mattfenwick wrote: post #12
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…

Just curious -- where did the "30%" figure come from?

Probably his intuition.

Re: Results of the SQL Performance Quiz

#16

Post by [deleted] »

brianberns wrote: post #13
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…

I had the same thought. Seems like the optimizer could still perform an index-only scan to get to 100 rows, then go to the table to filter them down to 10 rows. Yes, the second step is extra, but should still be fast. What am I missing?

[deleted]

Re: Results of the SQL Performance Quiz

#17

Post by walshemj »

sergiotapia wrote: post #9

Wow this just highlights why I absolutely hate working with raw SQL be it Postgres or MSSQL.

Really :-) try working with the preceding tech which in my case was raw ISAM where you had to build your own ACID on top of a very basic API.

doing Map reduce with non of your fancy pants hadoop malarky

Ps and we had to build our own build system using CPL (prime JCL)

Re: Results of the SQL Performance Quiz

#18

Post by bhaak »

sergiotapia wrote: post #9

Wow this just highlights why I absolutely hate working with raw SQL be it Postgres or MSSQL.

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!)

Re: Results of the SQL Performance Quiz

#19

Post by nwatson »

mattfenwick wrote: post #12
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…

Just curious -- where did the "30%" figure come from?

A guess, based on my experience and accounts of others here and on other similar forums. The figure could be much different for smaller startups, I've usually joined teams of between 3 and 15 engineers, where the adaptability and learn-as-you-go are more valuable.

Re: Results of the SQL Performance Quiz

#20

Post by Guvante »

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…

My thought was akin to what he said, the system needs to grab all 100 rows to do the second filter.

I didn't think about the fact that the original one was an index-only scan, so went with "Roughly the same", since outside of that property the performance is similar.

Too bad there is no way to get good data on why people thought that way without a much longer quiz.

Post reply on HN