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

21–30 of 98 posts

Re: Results of the SQL Performance Quiz

#21

Post by Guvante »

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?

As much as we would love to have a magical language that knows exactly how to take anything and make it performant enough, there is a decent amount of evidence to say that is impossible.

Honestly though the nice thing about SQL is you can quickly get something that works, and then use tracing tools to figure out what is taking longer than it should.

Re: Results of the SQL Performance Quiz

#22

Post by eterm »

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?

That was my reasoning when I answered, but I had missed the fact it was a GROUP BY, which means you can't just filter after the fact.

Edit: In other words it was 100 or 10 aggregated rows. A extra WHERE clause will change the values of each of the rows rather than just filter the rows from 100 to 10. (Which a HAVING clause would do.)

Re: Results of the SQL Performance Quiz

#23

Post by nousernamesleft »

38.2% of people got 4/5 or 5/5 questions right. That is amazing. I never would have guessed anywhere even close to that. I would have figured it would be around 15% or so given that guessing randomly would put it at 12.5%. I guess I get a skewed perspective from looking at applicants since presumably most of those 38% are happy with their current jobs.

Re: Results of the SQL Performance Quiz

#24

Post by sergiotapia »

bhaak wrote: post #18
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!)

Oh I don't have any false illusions. I know ORMs fail most of the time when you need very specialized queries - and I know how to do them - but I still hate them.

They're a pain in the ass.

Re: Results of the SQL Performance Quiz

#25

Post by jcampbell1 »

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.

Re: Results of the SQL Performance Quiz

#26

Post by sz4kerto »

sergiotapia wrote: post #9

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

SQL is extremely powerful. Whenever something comes up and says that it's going to hide the complexity of SQL/raw DB access, it's lying. :) I can work well in many cases, then it bites you in the ass later. That's not a reason to not use ORMs for example, but without knowing how the underlying DB works you can have problems. Abstractions are leaky, always. Same applies if you work in a high level language and you don't care/know how caches work; if you use HTTP requests and you have no idea how HTTP works, and so on.

Re: Results of the SQL Performance Quiz

#28

Post by buckbova »

eterm wrote: post #22
brianberns wrote: post #13

Earlier quoted context omitted.

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?

That was my reasoning when I answered, but I had missed the fact it was a GROUP BY, which means you can't just filter after the fact. Edit: In other words it was 100 or 10 aggregated rows. A extra WHERE clause will change the values of each of the rows rather than just filter the rows from 100 to 10. (Which a HAVING clause would do.)

It's much simpler than that. The first query only has to reference the index because the data is IN the index. The second query has to access the table. That's it.

It's called a covering index.

Re: Results of the SQL Performance Quiz

#30

Post by pradocchia »

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 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 being executed 1000s of times per second, in which case maybe your real problem lies elsewhere.

Post reply on HN