Results of the SQL Performance Quiz

http://use-the-index-luke.com/blog/2014-02/results-three-minutes-sql-performance-quiz

by randomdrake • 12 years ago

133 93 12 years ago

troels2• 12 years ago

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

civild• 12 years ago

If someone guessed incorrectly, they still didn't know the answer.

Robin_Message• 12 years ago

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 are correct guesses is absurd.

The correct lucky guess fraction (amount of the right answers to discard) is:

    A - X = A - (2A - 1) = 1 - A

If A is near to 0.5 (which we would expect if our model is accurate and few people know the answer, the 50% approximation the author used is about right.)

troels2• 12 years ago

Thanks - Not just me then.

chrisoverzero• 12 years ago

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.

bradleyjg• 12 years ago

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?

sokoloff• 12 years ago

SQL is a reasonably declarative language in that it will give you the correct answer without you knowing all the gory internal details.

This quiz is about performance tuning, not correctness. For plenty of valuable real-people use cases, it's plenty fast without any performance tuning.

Guvante2• 12 years ago

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.

jcampbell17• 12 years ago

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.

taeric• 12 years ago

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.

bduerst• 12 years ago

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?

nousernamesleft3• 12 years ago

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.

Spooky23• 12 years ago

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.

thwarted2• 12 years ago

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

fleitz• 12 years ago

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.

cleaver• 12 years ago

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.

eru• 12 years ago

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

pdubs2• 12 years ago

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 was 100/1000000 vs 10/1000000 isn't a big deal unless the DB is running on an abacus.

brianberns• 12 years ago

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?

eterm3• 12 years ago

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

Guvante2• 12 years ago

My thought was akin to what he said, the system needs to grab all 100 rows to do the second filter.

I didn't think about the fact that the original one was an index-only scan, so went with "Roughly the same", since outside of that property the performance is similar.

Too bad there is no way to get good data on why people thought that way without a much longer quiz.

pradocchia2• 12 years ago

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.

jcampbell17• 12 years ago

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.

trhway2• 12 years ago

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.

batbomb• 12 years ago

Such 100 random IOs will cost 0.5 sec on 1 iron platter HDD for example.

That's an incredibly, incredibly, iffy and mostly wrong statement which depends on arguably a corner case which doesn't often reflect reality (factors include which DBMS, row ordering, table size, cache size, block size, page size, RAM size, Hard Disk seek time, HDD throughput.

The only case where that's likely is performing a very cold query on a very large randomly distributed table once (and probably only once).

Even a table of 1 million rows with ~30B per row could easily be read into memory in about 300ms (100MB read time + ~5ms seek time, or ~= 5+(1e6*rowsize/ (100e3)) )

Query Optimizers do exactly this.

munimkazia• 12 years ago

Yep, that's exactly what I felt too. The only question I got wrong in fact.

tom_b2• 12 years ago

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 you a db wizard.

tbrownaw2• 12 years ago

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

I guess it's the same thing as makes FizzBuzz a useful question.

RogerL2• 12 years ago

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

nwatson2• 12 years ago

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 experience usually trump specific knowledge.

Fortunately I've landed pretty good jobs in spite of employer myopia, and work for a SV salary and a SV company though I live in a non-tech town in North Carolina. Just venting a frustration.

mattfenwick• 12 years ago

Just curious -- where did the "30%" figure come from?

herge• 12 years ago

Probably his intuition.

nwatson2• 12 years ago

A guess, based on my experience and accounts of others here and on other similar forums. The figure could be much different for smaller startups, I've usually joined teams of between 3 and 15 engineers, where the adaptability and learn-as-you-go are more valuable.

zzzeek• 12 years ago

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.

RogerL2• 12 years ago

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.

Pamar• 12 years ago

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

sergiotapia2• 12 years ago

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

walshemj• 12 years ago

Really :-) try working with the preceding tech which in my case was raw ISAM where you had to build your own ACID on top of a very basic API.

doing Map reduce with non of your fancy pants hadoop malarky

Ps and we had to build our own build system using CPL (prime JCL)

bhaak2• 12 years ago

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

sergiotapia2• 12 years ago

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.

dreamdu5t• 12 years ago

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

sz4kerto• 12 years ago

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.

einhverfr3• 12 years ago

Indeed. I would actually go further and say it can be a reason not to use ORMs though. But the rest of this is to bolster your point and hopefully give you some more ammo for your case.

One of the reasons I started working on the PGObject Perl framework[1] was because I found that if I hand-coded the SQL and programmed around that, I was more productive, and had fewer bugs.

The approach taken (based on our experience with LedgerSMB) is to focus on rules for mapping object oriented calls to stored procedure calls. The framework is of course PostgreSQL-only (and relies a lot on system catalog lookups).

My experience, backing what you are saying, is that if you know what you are doing, and you write good, maintainable stored procedures/UDF's then the power is totally worth it. Unfortunately, there are still some obstacles, and most of these are tooling.

I think the hatred of SQL from the parent (in the context of this quiz) is that if you separate performance from result, as SQL does, then you have to think about performance differently. In theory you code for results first and then tune performance later. In practice, this only gets you so far and you need to code with some knowledge of performance limitations. I know I have been bitten as badly as anyone else.

But, the tradeoff is that when I write more of my code into my queries, I get better performance, fewer bugs, and a significantly smaller codebase. The knowledge is worth it warts and all.

(When I have some really nice code samples, I will submit to HN)

[1] The official repos are the repos with names starting with "PGObject" at https://github.com/ledgersmb

marcosdumay• 12 years ago

What do you prefer? ORMs? Those keep the same set of problems, and add an entire new class of them.

The reason that you don't notice is that most of the times this stuff does not matter at all. Not because ORMs solve anything.

daigoba66• 12 years ago

Actually I think this highlights why it is important to understand and work with raw SQL when critical performance matters.

I'll let my ORM generate basic CRUD statements; the kind of statement which typically only involves PK lookups. But I find it better to write my own SQL statements for important queries, and of course let the ORM map the result set into objects. In fact, with enough experience, I've also found it _faster_ to just write a query in SQL versus learning yet another DSL or query builder API.

uptown• 12 years ago

Scored 4/5 ... Missed #3 - the multi-column index question.

nousernamesleft3• 12 years ago

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.

club7g• 12 years ago

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.

conradfr• 12 years ago

I'm guilty of using multiple single indexes instead of multi-column, so I'll try to correct that.

Hannan• 12 years ago

There's valid uses for both approaches; it all depends how you're querying the data.

eie• 12 years ago

I missed #2. I wonder which case assigning sort order(ASC or DESC) to columns in index gives performance advantage.

henrikschroder2• 12 years ago

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

cwyers2• 12 years ago

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

icedchai• 12 years ago

I've interviewed many "senior developers" can't even JOIN two tables. The concept of a "covering index" might as well be brain surgery.

jsmeaton• 12 years ago

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.

tbrownaw2• 12 years ago

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.

bowyakka• 12 years ago

I wonder if postgres higher scores come from the excellent visual explain that is inside pgadminIII ?

fooyc• 12 years ago

5/5, as a MySQL user.

I'm a bit ashamed by how bad MySQL users performed at this test.

veddev• 12 years ago

Very interesting thank you. Can anyone recommend any books on the matter? (on top of the one being recommended in the article itself).

deepsun• 12 years ago

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.

einhverfr3• 12 years ago

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.

cbsmith• 12 years ago

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.

Shivetya• 12 years ago

I like some of the examples they provide, however this is very platform dependent. Some advanced platforms have highly optimized query engines, to where even bad queries can be handled if they are run many times. Expressions can be reordered without the users knowledge and the results will be the same.

ugk• 12 years ago

Not enough info to make good choices on all, at least for MSSQL. Besides, the correct answer to most database issues is "it depends".

Results of the SQL Performance Quiz

Related Stories