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

81–90 of 98 posts

Re: Results of the SQL Performance Quiz

#81

Post by rschmitty »

bduerst wrote: post #56
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.

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?

You said cats and aristocrats and all I could think of was the movie The Aristocats.. so to me they seemed related :x

Re: Results of the SQL Performance Quiz

#82

Post by jcampbell1 »

skyebook wrote: post #74
jcampbell1 wrote: post #67

Earlier quoted context omitted.

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.

> Except

I am not sure what the exception is about. I agree 100% with the rest of your comment.

Re: Results of the SQL Performance Quiz

#83

Post by henrikschroder »

Alex3917 wrote: post #80
buckbova wrote: post #34

Earlier quoted context omitted.

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...

What are you talking about? According to that page, the example from the quiz would result in a tight index scan:

> The GROUP BY does not begin with the first part of the key, but there is a condition that provides a constant for that part:

> SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

Re: Results of the SQL Performance Quiz

#84

Post by skyebook »

jcampbell1 wrote: post #82
skyebook wrote: post #74

Earlier quoted context omitted.

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.

> Except I am not sure what the exception is about. I agree 100% with the rest of your comment.

My exception was to the idea of this being a premature optimization (sorry, I probably should've replied directly to your comment with the CSS example)

Re: Results of the SQL Performance Quiz

#85

Post by ars »

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.

> The worst case for the latter query is that it must aggregate over 999,910 rows.

No, it already said it only took 100 rows, it can't get worse from that.

Now if he actually meant the final result set was 100 rows (meaning after the group by) that's different. But that's not what he actually said, so the question is misleading.

Re: Results of the SQL Performance Quiz

#86

Post by eru »

cleaver wrote: post #78
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 we…

Actually SQL gives you bag semantics, not set semantics. (But otherwise, yes.)

Re: Results of the SQL Performance Quiz

#87

Post by Pamar »

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…

The problem is that "intuition, adaptability, and general experience" are difficult to test for in the limited time of a job interview.

Re: Results of the SQL Performance Quiz

#88

Post by thwarted »

Spooky23 wrote: post #37
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?

Promise delivered. Think of the first question. If you're going to use a function to transform data, you're going to take a performance hit vs. just doing and indexable operation against the data. If you want a facility that will magically figure out what you want when you are unable to express what you want, good luck. That's unicorn territory.

Reminds me of this Babbage quote:

"On two occasions I have been asked, 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question." ~ Charles Babbage

Re: Results of the SQL Performance Quiz

#89

Post by cbsmith »

einhverfr wrote: post #79

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.

Actually, since ~9.2 you don't need pg_trgm.

But yeah, I was kind of surprised that he was surprised about why PostgreSQL folks failed: they just didn't think about whether the index was optimal.

Re: Results of the SQL Performance Quiz

#90

Post by thwarted »

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.

No query optimizer would look at this and say "1M rows? Let's group and aggregate before filtering."

I hope no optimizer would say that. It is well defined that filtering, as expressed in the where clause (if it uses indexes or not) happens before group and aggregate, and that grouping happens on the result of the filtering. If the optimizer could choose one way or the other, you'd have different results. If you want to group and aggregate first, you need to explicitly express that with a subquery.

Post reply on HN