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

61–70 of 98 posts

Re: Results of the SQL Performance Quiz

#61

Post by RogerL »

zzzeek wrote: post #57
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.

Yes, but I suspect his point was "I work with NoSQL, so testing me on my SQL knowledge isn't a very good proxy for my ability to tune a database". If he didn't know the details of MongoDB, and that was in the hot path (let's face it, for many situations it just doesn't matter), then that would be worrying.

Re: Results of the SQL Performance Quiz

#62

Post by jcampbell1 »

pradocchia wrote: post #59
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.

> 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 interp…

I took the latter interpretation. The "correct" solution uses the fact the first query can be solved by selecting 0 rows from the base table.

Re: Results of the SQL Performance Quiz

#63

Post by RogerL »

tbrownaw wrote: post #52
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…

I honestly don't grasp why this is a good question.

Sure, I write a SQL query now and again, but if I have to join I look it up. I don't do it a lot. If I had to do it a lot for you, I would learn it inside out.

If I told you I was a DB wizard, then sure, I better already know how to join. But I know tons of people that don't really do SQL. They are eminently hire-able and valuable.

source: a guy (me) who was bounced out of an interview because he didn't recall some specific detail about boost::shared_pointer().

Re: Results of the SQL Performance Quiz

#64

Post by nousernamesleft »

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.

>These are premature optimization tweaks

How can you apply a blanket "premature" to this? The questions are simply "can this be improved?". There's nothing premature about it, it is a quiz.

Re: Results of the SQL Performance Quiz

#65

Post by cwyers »

RogerL wrote: post #63
tbrownaw wrote: post #52

Earlier quoted context omitted.

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…

I honestly don't grasp why this is a good question. Sure, I write a SQL query now and again, but if I have to join I look it up. I don't do it a lot. If I had to do it a lot for you, I would learn it inside out. If I told you I was a DB wizard, then sure, I better already know how to join. But I know tons of people that don't really do SQL. They are eminently hire-able and valuable. source: a guy (me) who was bounced…

If you don't know how to do a JOIN you really don't know how to use SQL, and that's not a tricky use of JOINs at all. If you're hiring for a position that mostly uses an ORM or something and raw SQL is only important in cases where the ORM is giving bad performance, maybe not knowing how to do a three-table join is fine. If actually writing SQL is part of the job regularly, though, the sort of thing grandparent is talking about is absolutely a bare-minimum of knowledge needed, maybe even below the bare minimum.

Re: Results of the SQL Performance Quiz

#66

Post by chrisoverzero »

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

There are two choices. If everyone guessed, the expected value is 50% correct and 50% incorrect. This is why it's 25/75 for the question with four choices.

Re: Results of the SQL Performance Quiz

#67

Post by jcampbell1 »

nousernamesleft wrote: post #64
jcampbell1 wrote: post #25

Earlier quoted context omitted.

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.

>These are premature optimization tweaks How can you apply a blanket "premature" to this? The questions are simply "can this be improved?". There's nothing premature about it, it is a quiz.

It is a reference to this well known quote:

"Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3%." --Donald Knuth

Re: Results of the SQL Performance Quiz

#69

Post by nousernamesleft »

jcampbell1 wrote: post #67
nousernamesleft wrote: post #64

Earlier quoted context omitted.

>These are premature optimization tweaks How can you apply a blanket "premature" to this? The questions are simply "can this be improved?". There's nothing premature about it, it is a quiz.

It is a reference to this well known quote: "Programmers waste enormous amounts of time thinking about, or worrying about, the speed of noncritical parts of their programs, and these attempts at efficiency actually have a strong negative impact when debugging and maintenance are considered. We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we sho…

I am aware of that quote. And that is precisely my point, he is talking about premature optimization, not optimization. What could possibly make you think that the questions in this quiz are examples of premature optimization?

Re: Results of the SQL Performance Quiz

#70

Post by troels »

Robin_Message wrote: post #14
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…

Thanks - Not just me then.
Post reply on HN