Every week here and in our newsletter, we feature a new developer/blogger from the DZone community to catch up and find out what he or she is working on now and what's coming next.
This week we're talking to Markus Winand, author, trainer, and coach specializing in helping developers cope with SQL performance issues. Some of his most recent DZone posts include:
- Finding All the Red M&Ms: A Story of Indexes and Full‑Table Scans
- What I Learned About SQLite... at a PostgreSQL Conference
- What's Left of NoSQL?
1. What have you been working on lately?
I've a tradition on my blog: every anniversary I'm launching something “special”: first it was the “3-Minute Quiz: What do you know about SQL performance” — which is still the most popular feature on my site. Then it was SQLFiddle integration. This year it's a campaign to make keyset pagination more widely known. Keyset pagination is an alternative way to fetch data from a backend page-by-page: instead of specifying which page number to fetch, we just say tell the backend the last entry we have got before. The backend just needs to deliver results from that entry on. That can be done pretty easily. Keyset pagination is particularly well suited for infinite scrolling and delivers better performance and more stable results than offset-based approaches. However, tool support is pretty “sparse.” That's why I thought I must do something about this: and the NoOffset campaign was born.
2. Your blog focuses on SQL performance. Can you offer a few main tips or solutions for SQL performance problems? When somebody comes to you with a performance issue, where do you start?
Well, my best tip is to take indexing serious and acknowledge it as the developers' responsibility.
When I've evaluated the above-mentioned quiz, I found that 60% fail. Everybody knows that indexing is important for performance, yet there is so little knowledge about how to do it right—especially among developers. This discrepancy originally motivated me to launch Use The Index, Luke—a indexing tutorial specifically aimed at developers.
When looking into performance issues, the best start is to look at the execution plan of the query. In the execution plan you can see which indexes are used, and more importantly, how they are used. It's still a very common misunderstanding that indexing is just about making sure an index is used. In fact, a database might use an index in a very bad way—often a desperate deed because the right index is missing or the query is written in a way which makes the index useless. Just because a database uses an index doesn't mean it's the best possible index.
3. Are there any particular developer tools or resources you couldn't live without?
Not sure if this qualifies as a tool, but I've to mention the execution plans again. It's just the first spot to look at when coping with performance issues. Every database supports them, it's built in, it's for free. Learn about them here.
StackOverflow is also very important to me, but not because I'm posting questions there. I'm following StackOverflow questions to learn about peoples misunderstandings. Once I understood people's misbelieves, I can clarify them. I've spent a lot of time on reading StackOverflow questions before I started writing Use The Index, Luke! I'm also answering questions, of course, but quite often I'm just too slow for that :)
4. Do you have a favorite open source project (or projects) that you've contributed to recently?
Although I didn't contribute any code yet, I'd put PostgreSQL here. The quality and completeness of this database is just remarkable. PostgreSQL is in the same league as the commercial SQL databases, just for free. It is definitively worth spending my time on contributing code—I do even have some ideas what do to, but haven't found the time yet.
5. Do you follow any blogs or Twitter feeds that you would recommend to developers?
Oh yes. More than I like. I guess I spend about an hour every day staying up to date. Unfortunately, most blogs I follow ain't well suited for developers—they generally tend to get lost in the databases internals which are not relevant for a wider developer audience. When they occasionally write something that is relevant for developers, I'll tweet it on my @SQLPerfTips account.
However, here are some Twitter feeds that tend to produce very high quality content: @morgo (MySQL), @robertmhaas (PostgreSQL), @SQL_Kiwi (SQL Server), @richardfoote (Oracle). @JavaOOQ covers a lot of interesting SQL stuff too—might be interesting even when not using Java.
Finally, my all-time favorite SQL blogger is Alex Bolenok (aka Quassnoi). He blogs at http://explainextended.com/
6. Did you have a coding first love -- a particular program, gadget, game, or language that set you on the path to life as a developer?
I started coding on a Commodore 64 at the age of nine. I didn't have my own C64 at the time, so I started saving my pocket money for it. My parents didn't believe I'm gonna do that for long. However, I did. A a year I've had enough for a C64. But I could only afford the main unit—nothing more, not even the “Datasette”. Every program I wrote was just in RAM and gone when the computer was switched off. After my parents realized that I've to re-write everything every day (because they told me to switch it off!), I've finally got a floppy drive for Christmas. Best. Christmas. Ever.
7. Is there anything else you'd like to mention?
Don't thread SQL database as "persistance layers." SQL is a data processing language that can do quite impressive work for you. Storing data is really just the boring part of it. As an example, have a look at window-functions: http://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/