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

41–50 of 98 posts

Re: Results of the SQL Performance Quiz

#41

Post by club7g »

nousernamesleft wrote: post #23

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.

I took the test twice so I'm guilty of skewing the stats. My reasoning - first time I got 4/5 and it told me 'you know a little bit about SQL performance tuning.' I was curious about what the 5/5 message would be. It's interesting on many levels that the message was the same.

Re: Results of the SQL Performance Quiz

#42

Post by jcampbell1 »

taeric wrote: post #35
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.

Do you have a good link for numbers on this css point? I would expect speed differences, but my naive view would be that they wouldn't be much.

The difference is probably .1us. You may find it interesting why browsers match CSS selectors from right to left (though this is a useless bit of trivia). see:

http://stackoverflow.com/questions/5797014/why-do-browsers-m...

Re: Results of the SQL Performance Quiz

#43

Post by cwyers »

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

The author has a website and sells a book devoted to teaching people about SQL indexing, so that's where his focus is. I wonder what kind of a pool he got; given that he seems to have given it mostly to people reading his site, you'd think that it'd be people who know more about SQL indexing than the average.

Most troubling to me was how people who chose the MySQL option did so much worse than pretty much every other database. (I took the MySQL option, even though I work on MS SQL these days, and got 4 out of 5.) I suppose for people who consider MySQL to be a toy RDBMS, that's less "troubling" than "confirming current perception."

Re: Results of the SQL Performance Quiz

#44

Post by jcampbell1 »

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

Re: Results of the SQL Performance Quiz

#45

Post by tbrownaw »

I have two nearly-identical tables, both shaped like (foreign_key some_data_type, name varchar2, value varchar2). (Before you say "that means you should use No-SQL", this is a staging environment for loading data into a claims handling system. Which is built in a language that comes with a relational db built in.)

They both have about 50M rows, with statistically identical data. I was running near-identical large queries on both, with the same execution plan (nested-loop join with index lookups), and getting vastly different timings.

This being a staging environment, these tables are re-populated by truncating them and running an ETL tool. What turned out to be happening, is that in one case the source query on the ETL tool was sorted by the foreign key, and in the other it wasn't. So in one case all those fetch-by-index-lookup operations added to to essentially a partial table scan, and in the other they added up to what you'd expect where blocks would be fetched in random order and probably re-fetched after falling out of cache.

Re: Results of the SQL Performance Quiz

#46

Post by nollidge »

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.

We're not talking about worst case. We're talking about 100 rows and 10 rows, which is what @pradocchia means by "selective".

Indexing shouldn't follow the theoretical worst case, it should follow what's actually in your table.

Re: Results of the SQL Performance Quiz

#47

Post by trhway »

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…

it has significant impact because first case is "take 100 rows from index" and the second is "take 100 rows from index _and_ for each row go to the row in the table - do the random IO and with 1M rows it is probably 1 IO/row - and check for the value of 'b'"

Such 100 random IOs will cost 0.5 sec on 1 iron platter HDD for example. So the query performance will degrade significantly until either the table is already preloaded into memory or you use SSD drives.

Re: Results of the SQL Performance Quiz

#48

Post by bhaak »

dreamdu5t wrote: post #38
bhaak wrote: post #18

Earlier quoted context omitted.

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

Ugly compared to what query language of equal power and expressiveness?

Expressiveness? SQL is quite verbose for what it does achieve and if you look at an SQL with some JOINs, I'm not sure you want to uphold that opinion of its expressiveness.

Don't get me wrong, I have no problem with SQL as such but it has some shortcomings. Probably most things can be blamed on its age.

Why is the syntax of INSERT and UPDATE statements unnecessarily distinct? They do almost the same thing. Why are the values and column names in the INSERT statements' syntax separated and not with an UPDATE statement.

Why are there keywords that consists of more than one word? (Maybe the same ill-guided idea as with COBOL that being able to "read" SQL would make it easier to write/maintain?)

The syntax of SQL is in many places not tight enough, so you often have to hunt down a missing comma or typo, because the SQL parser is just not able to find out what you've done wrong.

Re: Results of the SQL Performance Quiz

#50

Post by pdubs »

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.

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.

Post reply on HN