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

71–80 of 98 posts

Re: Results of the SQL Performance Quiz

#71

Post by fleitz »

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 query language and an RDBMS is an implementation.

It's like calculus, you can compute efficiently or inefficiently it has nothing to do with calculus.

The same developers that make retarded SQL queries make god awfully slow programs with their pointer chasing mess because they don't understand that their programs run on actual real hardware and that a cache miss is pretty much the single most expensive operation a modern CPU can make by 2 to 3 orders of magnitude.

They just go on and on about interfaces, inline methods, virtual methods, etc, and never check whether their fancy smancy object fits on a cache line. Its the same with databases they just talk about 'webscale' and 'big data' abandon SQL and then wonder why their DB explodes once it reaches 100 GB.

Re: Results of the SQL Performance Quiz

#74

Post by skyebook »

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…

Except using indexes is a major part of developing for a database. In many simple schemas, performance will completely break down without proper indexing. That's a bit different than spending time investigating if for-each is faster than a traditional loop.

Re: Results of the SQL Performance Quiz

#75

Post by tom_b »

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…

To clarify, two SQL questions were asked during an interview round for a job description that specifically required the ability to write ANSI SQL against databases for reporting/analytics purposes. Opposed to bringing candidates in for a job description requiring Java experience and then dropping a couple of SQL "gotcha" questions on a surprised candidate.

I think at this point, most HN'ers would accept that a work sample test (of some limited scope and effort) is a better test than any interview question or whiteboard coding process. From that perspective, no question is really a good one. Unfortunately, at my workplace, there are strict rules in place specifically prohibiting this type of applicant process.

Re: Results of the SQL Performance Quiz

#76

Post by deepsun »

Interestingly, if you worked a lot with NoSQL systems, you could get 5/5 on that score.

Although I worked a lot with SQL in past, I think good knowledge of only a NoSQL system (e.g. GAE Datastore) would give you enough understanding of how simple indexes are, and how to deal with them to pass this pure SQL test.

Would like to hear a feedback from someone with only NoSQL experience after taking that test.

Re: Results of the SQL Performance Quiz

#77

Post by jsmeaton »

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 agree. I scored 5 out of 5 (Oracle flavour), and didn't have to think too hard about any of the questions. The review article specifically mentions that he has no data about the kinds of people answering the test though. A lot of people just don't do raw SQL that often.

Re: Results of the SQL Performance Quiz

#78

Post by cleaver »

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 bit deceptive in that it cloaks set-based operations in something apparently simple. Iterative operations are easier for most people to conceptualize—you could create a flow chart, whereas sets require a bit of math. Once you can think in terms of sets, SQL becomes a lot clearer. The issue with sets is, in my opinion, the root of problems in Object-Relational Mapping.

There were other query languages that were less of a messy compromise. QUEL, for example was based more rigorously on relational calculus. I think it never took off because the dumbed-down SQL was a little easier for programmers to conceptualize initially.

Re: Results of the SQL Performance Quiz

#79

Post by einhverfr »

He wonders about PostgreSQL users doing poorly on question 4. This is a fairly tricky question. Since the index is a btree index, it is pretty clear what the right answer is, but there are ways to address the wildcard issue with better indexes. PostgreSQL folks who have less familiarity with the db may miss the fact that a GIN or GIST index is required along with the pg_trgm addon, to make that query fast.

Re: Results of the SQL Performance Quiz

#80

Post by Alex3917 »

buckbova wrote: post #34
eterm wrote: post #32

Earlier quoted context omitted.

But if it wasn't for the GROUP BY, filtering 100 results of a million down to 10 results wouldn't change performance much even if you read every column of every row of those 100. The trick is the fact that the GROUP BY means that "It used to return 100 it now returns 10" is a red herring, it still has to read every row to make up those 10.

I don't understand what you mean "trick". SELECT date_column, count(*) FROM tbl WHERE a = @a AND b = @b GROUP BY date_column; The "AND b = @b" causes the sql engine to access data in the table instead of solely relying on the index. GROUP BY has 0 to do with it. If you changed the query to SELECT a, date_column FROM tbl WHERE a = @a and SELECT a, date_column FROM tbl WHERE a = @a AND b = @b The answer would be the sa…

While you're correct, GROUP BY apparently also does kill the indexing:

http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization...

Post reply on HN