von Markus Winand.

Blog Archiv


Nicht alle Artikel sind auf Deutsch verfügbar.

2017-05-11 Neues über Datenbanken — Frühling 2017

2016-11-28 Große Neuigkeiten über Datenbanken

2016 ist ein Jahr voller Überraschungen. Auch in der Welt der Datenbanken. Daher möchte ich die weitreichendsten Neuerungen rund um Datenbanken kurz zusammenfassen.

    (Weiter lesen)

    2016-07-29 On Uber's Choice of Databases

    Translations

    A Japanese translation of this article is available here.

    A Chinese translation of this article is available here.

    A few days ago Uber published the article “Why Uber Engineering Switched from Postgres to MySQL”. I didn't read the article right away because my inner nerd told me to do some home improvements instead. While doing so my mailbox was filling up with questions like “Is PostgreSQL really that lousy?”. Knowing that PostgreSQL is not generally lousy, these messages made me wonder what the heck is written in this article. This post is an attempt to make sense out of Uber's article.

    In my opinion Uber's article basically says that they found MySQL to be a better fit for their environment as PostgreSQL. However, the article does a lousy job to transport this message. Instead of writing “PostgreSQL has some limitations for update-heavy use-cases” the article just says “Inefficient architecture for writes,” for example. In case you don't have an update-heavy use-case, don't worry about the problems described in Uber's article.

    In this post I'll explain why I think Uber's article must not be taken as general advice about the choice of databases, why MySQL might still be a good fit for Uber, and why success might cause more problems than just scaling the data store.

    1. On UPDATE
    2. On SELECT
    3. On Index Rebalancing
    4. On Physical Replication
    5. On Developers
    6. On Success
    7. On Uber's Choice of Databases

    (Read more)

    2015-02-02 Modern SQL in PostgreSQL [and other databases]

    SQL has gone out of fashion lately—partly due to the NoSQL movement, but mostly because SQL is often still used like 20 years ago. As a matter of fact, the SQL standard continued to evolve during the past decades resulting in the current release of 2011. In this session, we will go through the most important additions since the widely known SQL-92, explain how they work and how PostgreSQL supports and extends them. We will cover common table expressions and window functions in detail and have a very short look at the temporal features of SQL:2011 and the related features of PostgreSQL.”

    This is the abstract for the talk I've given at FOSDEM in Brussels on Saturday. The PostgreSQL community was so kind to host this talk in their (way too small) devroom—thus the references to PostgreSQL. However, the talk is build upon standard SQL and covers features that are commonly available in DB2, Oracle, SQL Server and SQLite. MySQL does not yet support any of those features except OFFSET, which is evil.

    (Read more)

    2014-11-03 Seven Surprising Findings About DB2

    I've just completed IBM DB2 for Linux, Unix and Windows (LUW) coverage here on Use The Index, Luke as preparation for an upcoming training I'm giving. This blog post describes the major differences I've found compared to the other databases I'm covering (Oracle, SQL Server, PostgreSQL and MySQL).

    1. Free & Easy
    2. No Easy Explain
    3. Emulating Partial Indexes is Possible
    4. INCLUDE Clause Only for Unique Indexes
    5. Almost No NULLS FIRST/LAST Support until 11.1
    6. SQL:2008 FETCH FIRST but not OFFSET
    7. Decent Row-Value Predicates Support

    (Read more)

    2014-10-17 Neues Maskottchen, Neue Sprache, Neue Datenbank

    In letzter Zeit ist es hier auf Use The Index, Luke ruhig geworden. Das liegt aber nicht daran, dass mir nichts mehr zu schreiben einfällt – tatsächlich wird meine Themen-Liste immer länger – die Ruhe rührt daher, dass ich derzeit mit etwas aufwendigeren Projekten beschäftigt bin.

    Zu aller erst hat Use The Index, Luke ein neues Maskottchen bekommen – das alleine wäre wohl kaum eine Schlagzeile wert. Nennenswert ist es dennoch, da ich derzeit daran arbeite, Fan-Artikel mit dem neuen Maskottchen zu machen. Details folgen, wenn es soweit ist.

    (Weiter lesen)

    2014-10-15 Wir brauchen Tool-Unterstützung zum Blättern mit Keysets

    Wusstest du, dass Blättern mit offset sehr problematisch, aber einfach zu vermeiden ist?

    offset sagt der Datenbank die ersten N Ergebnisse eine Abfrage zu überspringen. Die Datenbank muss diese Ergebnisse aber trotzdem laden und in die gewünschte Reihenfolge bringen, bevor sie die darauffolgenden Ergebnisse senden kann.

    Das ist kein Problem der Implementierung, sondern ein Design-Problem von offset:

    …the rows are first sorted according to the <order by clause> and then limited by dropping the number of rows specified in the <result offset clause> from the beginning…

    SQL:2016, Part 2, §4.15.3 Derived tables

    In anderen Worten: Große offsets verursachen einen großen Aufwand für die Datenbank – unabhängig ob SQL oder NoSQL.

    Es gibt aber noch andere Probleme, wenn man mit offset blättert: Was passiert wenn, zwischen dem Laden zweier Seiten ein neuer Eintrag eingefügt wird?

    Wenn man offset nutzt➌, um die zuvor geladenen Einträge❶ zu überspringen, erhält man Duplikate, wenn in der Zwischenzeit neue Einträge gespeichert wurden➋. Natürlich gibt es durch offset noch andere Anormalitäten, dies ist aber die Häufigste.

    Das ist auch kein Datenbank-spezifisches Problem – es wird vielmehr dadurch verursacht, dass beim Blättern Seitennummern verwendet werden. Nur mit dieser Information kann es keine Datenbank besser machen.

      (Weiter lesen)

      2014-07-09 Finding All the Red M&Ms: A Story of Indexes and Full‑Table Scans

      In this guest post, Chris Saxon explains a very important topic using an analogy with chocolates: When does a database use an index and when is it better not using it. Although Chris explanation has the Oracle database in mind, the principles apply to other databases too.

      A common question that comes up when people start tuning queries is “why doesn’t this query use the index I expect?”. There are a few myths surrounding when database optimizers will use an index. A common one I’ve heard is that an index will be used when accessing 5% or less of the rows in a table. This isn’t the case however - the basic decision on whether or not to use an index comes down to its cost.

      1. How do databases determine the cost of an index?
      2. What have M&M's got to do with databases?
      3. When does an index scan become more efficient than a FTS?
      4. Bringing it all together

      (Read more)

      2014-05-25 What I learned about SQLite…at a PostgreSQL conference

      So, I've been to PgCon 2014 in Ottawa to give a short version of my SQL performance training. However, I think I ended up learning more about SQLite than about PostgreSQL there. Here is how that happened and what I actually learned.

      Richard Hipp, creator of SQLite was the keynote speaker at this years PgCon. In his keynote (slides, video) he has put the focus on three topics: how PostgreSQL influenced SQLite development (“SQLite was originally written from PostgreSQL 6.5 documentation” and the “What Would PostgreSQL Do?” (WWPD) way of finding out what the SQL standard tries to tell us). The second main topic was that SQLite should be seen as an application file format—an alternative to inventing own file formats or using ZIPped XMLs. The statement “SQLite is not a replacement for PostgreSQL. SQLite is a replacement for fopen()” nails that (slide 21). Finally, Richard put a lot of emphasis on that fact that SQLite takes care of your data (crash safe, ACID)—unlike many of the so-called NoSQL systems, which Richard refers to as “Postmodern Databases: absence of objective truth; Queries return opinions rather than facts”. In his keynote, Richard has also shown that SQLite is pretty relaxed when it comes to data types. As a matter of fact, SQLite accepts strings like “Hello” for INT fields. Note that it still stores “Hello”—no data is lost. I think he mentioned that it is possible to enforce the types via CHECK constraints.

      (Read more)

      2014-04-25 What's left of NoSQL?

      This is my own and very loose translation of an article I wrote for the Austrian newspaper derStandard.at in October 2013. As this article was very well received and the SQL vs. NoSQL discussion is currently hot again, I though it might be a good time for a translation.

      Back in 2013 The Register reported that Google sets its bets on SQL again. On the first sight this might look like a surprising move because it was of all things Google's publications about MapReduce and BigTable that gave the NoSQL movement a big boost in the first place. On a second sight it turns out that there is a trend to use SQL or similar languages to access data from NoSQL systems—and that's not even a new trend. However, it raises a question: What remains of NoSQL if we add SQL again? To answer this question, I need to start with a brief summary about the history of databases.

      1. Top Dog SQL
      2. And Then: NoSQL
      3. Nevertheless: Back to SQL
      4. What's left of NoSQL?

      (Read more)

      2014-04-01 Thank You MySQL, We'll Miss You!

      Dear MySQL,

      Thank you for introducing me to SQL. It must have been 1998 when we first met I and fell in love with the simplicity of SQL immediately. Before that I've been using C structs all the time; I had to do my joins programmatically and also create and maintain my indexes manually. It was even hard to combine several search conditions via and and or. But then there was the shiny new world of SQL you were showing me…

      (Read more)

      2014-02-20 Ergebnisse des SQL Performance Tests

      Im Jahr 2011 habe ich den „3-Minuten Test: Was weißt Du über SQL-Performance?“ gestartet. Der Test besteht aus fünf Fragen die alle dem selben Muster folgen: Jede zeigt ein Abfrage/Index-Paar und fragt, ob das gute Indizierung ist, oder nicht. Bis heute hat sich der Test zu einem der populärsten Features auf Use The Index, Luke entwickelt und wurde über 28.000 mal durchgeführt.

      Beachte

      Falls du neugierig geworden bist, mach den Test selbst, bevor du weiter liest. Ansonsten könnte dir dieser Artikel den Aha-Effekt verderben.

      Obwohl der Test der Bildung dient, wollte ich wissen ob, man aus diesen 28.000 Ergebnissen interessante Erkenntnisse ableiten kann. Und ich denke, man kann. Man muss sich dabei aber einige Dinge vor Augen halten: Erstens verwendet der Test den Überraschungs-Faktor zur Steigerung der Aufmerksamkeit. Das heißt, es gibt drei Fragen, bei denen SQL und Index auf den ersten Blick gut aussehen, es aber nicht sind. Eine Frage macht es genau umgekehrt und zeigt eine SQL-Abfrage, die gefährlich aussieht, es aber dank des passenden Indexes nicht ist. Nur bei einer Frage stimmt der erste Eindruck mit dem tatsächlichen Sachverhalt überein. Der zweite Effekt, der die Aussagekraft der Ergebnisse beeinflussen könnte, ist, dass es keinerlei repräsentative Selektion der Teilnehmer gab. Jeder kann den Test machen. Auch mehrfach – und beim zweiten Mal potenziell ein besseres Ergebnis erzielen. Denke einfach daran, dass der Test nicht dafür ausgelegt war, wissenschaftliche Untersuchungen über das Indizierungs-Know-how in der Branche durchzuführen. Dennoch denke ich, dass die Menge der Daten groß genug ist, um einen Eindruck zu vermitteln.

      Im Folgenden zeige ich für jede der fünf Fragen zwei Statistiken: Zuerst die Durchschnittsrate, mit der diese Frage richtig beantwortet wurde. Dann, wie diese Rate bei Nutzern von MySQL, Oracle, PostgreSQL und SQL Server Datenbanken abweicht. In anderen Worten zeigt es, ob MySQL-Nutzer mehr über Indizierung wissen als z.B. PostgreSQL-Nutzer. Vorgriff: Es ist genau anders herum. Der einzige Grund, warum ich in der glücklichen Lage bin, diese Daten zu zeigen, ist, dass der Test teilweise herstellerabhängige Syntax verwendet. Zum Beispiel verwenden MySQL und PostgreSQL das Schlüsselwort LIMIT, SQL Server aber TOP. Daher muss jeder Teilnehmer am Anfang des Tests eine Datenbank auswählen, damit die Fragen dann in der entsprechenden Syntax angezeigt werden.

      1. Frage 1: Funktionen in der WHERE-Klausel
      2. Frage 2: Indizierte Top-N Abfragen
      3. Frage 3: Die Index-Spaltenreihenfolge
      4. Frage 4: LIKE-Suchen
      5. Frage 5a: Index-Only-Scan
      6. Frage 5b: Index-Spaltenreihenfolge und Bereichs-Bedingungen

      (Weiter lesen)

      2014-01-17 Unsinnige Defaults: Primärschlüssel als Clusterschlüssel

      Wie du vielleicht schon bemerkt hast – zumindest wenn du SQL Performance Explained gelesen hast – glaube ich nicht, dass Clustered-Indizes so nützlich sind, wie viele behaupten. Das kommt daher, dass die Wahl eines guten Clusterschlüssels (Clustering Keys) verdammt schwierig ist. In der Tat ist die Wahl eines guten – des „richtigen“ – Clusterschlüssels nahezu unmöglich, sobald die betroffene Tabelle mehr als ein oder zwei Indizes hat. Das Ergebnis ist, dass oft der Default Clusterschlüssel verwendet wird—also der Primärschlüssel. Leider ist das fast immer die falsche Wahl.

      In diesem Artikel erkläre ich die Bestie namens Clustered-Index mit allen Nachteilen. Obwohl der Artikel SQL Server also Demo-Datenbank verwendet, trifft das Gesagte auch auf Oracle Index-organisierte Tabellen und MySQL mit InnoDB zu.

        (Weiter lesen)

        2013-10-01 MongoDB is to NoSQL like MySQL to SQL — in the most harmful way

        Translations

        A Japanese translation of this article is available here.

        Yesterday evening I tweeted: “MongoDB seems to be as bad for NoSQL as MySQL is for SQL.” Unfortunately, I tweeted without context. But I guess I couldn't have given all the required context in a single tweet anyway, so I'm dedicating this post to it. I hope this answers some of the questions I've got in response to the tweet.

        (Read more)

        2013-08-25 Myth: Select * is bad

        This is one of the most persistent myths I've seen in the field. It's there for decades. If a myth is alive that long there must be some truth behind it. So, what could be bad about select *? Let's have a closer look.

        We all know that selecting “*” is just a short-hand for selecting all columns. Believe it or not, this makes a big difference to many people. So, lets first rephrase the question using this “finding”:

        (Read more)

        2013-08-13 Try it online!

        UPDATE: SQLFiddle integration has been removed in 2015 due to the low usage and high effort on my side.

        (Read more)

        2013-07-26 About Optimizer Hints: Prefer supportive and avoid restictive SQL hints

        Quite often I'm asked what I think about query hints. The answer is more lengthy and probably also more two-fold than most people expect it to be. However, to answer this question once and forever, I though I should write it down.

        The most important fact about query hints is that not all query hints are born equally. I distinguish two major types:

        Restricting Hints

        Most query hints are restricting hints: they limit the optimizers' freedom to choose an execution plan. “Hint” is an incredibly bad name for these things as they force the optimizer to do what it has been told—probably the reason MySQL uses the FORCE keyword for those.

        I do not like restricting hints, yet I use them sometimes to test different execution plans. It usually goes like this: when I believe a different execution plan could (should?) give better performance, I just hint it to see if it really gives better performance. Quite often it becomes slower and sometimes I even realize that the execution plan I though of does not work at all—at least not with the database I'm working at that moment.

        Typical examples for restricting query hints are hints that force the database to use or not use a particular index (e.g., INDEX and NO_INDEX in the Oracle database, USE INDEX and IGNORE INDEX in MySQL, or INDEX, FORCESEEK and the like in SQL Server).

        So, what's wrong with them? Well, the two main problems are that they (1) restrict the optimizer and that they (2) often need volatile object names as parameters (e.g., index names). Example: if you use a hint to use index ABC for a query, the hint becomes ineffective when somebody changes the name of the index to ABCD. Further, if you restrict the optimizer you can no longer expect it to adjust the execution plan if you add another index that servers the query better. Of course there are ways around these problems. The Oracle database, for example, offers "index description" hints to avoid both issues: instead of specifying the index name, it accepts a description of the ideal index (column list) and it selects the index that matches this definition best.

        Nevertheless, I strongly recommend against using restricting query hints in production. Instead you should find out why the optimizer does “the wrong thing”™ and fix the root cause. Restricting hints fix the symptom, not the cause. That being said, I know that there is sometimes no other reasonable choice.

        Supporting Hints

        The second major type of query hints are supporting hints: they support the optimizer by providing information it doesn't have otherwise. Supporting hints are rare—I'm only aware of a few good examples and the most useful one has already become obsolete: it's FAST number_rows (SQL Server) and FIRST_ROWS(n) (Oracle). They tell the optimizer that the application plans to fetch only that many rows of the result. Consequently, the optimizer can prefer using indexes and nested loop joins that would be inefficient when fetching the full result (see Chapter 7, Partial Results for more details). Although being kind-of obsolete, I'm still using these hints as the defining example for supporting hints because they provide information the optimizer cannot have otherwise. This particular example is important enough that it was worth defining new keywords in the ISO SQL:2008: FETCH FIRST ... ROWS ONLY and OFFSET. That's why this hint is a very good, yet obsolete example for supporting query hints.

        Another example for supporting hints is the (undocumented) CARDINALITY hint of the Oracle database. It basically overwrites the row count estimate of sub-queries. This hint was often used if the combined selectivity of two predicates was way off the product of the selectivity of each individual predicate (see Combined Selectivity Example). But this hint is also outdated since Oracle database 11g introduced extended statistics to cope with issues like that. SQL Server's filtered statistics serve the same purpose. If your database cannot reflect data correlation in it's statistics, you'll need to fall back to restricting hints.

        The Oracle hint OPT_ESTIMATE is somehow the successor of the CARDINALITY hint for cases when the estimations are still off. Pythian wrote a nice article about OPT_ESTIMATE.

        Combined Selectivity Example

        Let's say we have two Y/N columns and each has a 50:50 distribution. When you select using both columns most optimizers estimate that the query matches 25% of the table (by multiplying two times 50%). That means that the optimizer assumes there is no correlation between those two columns.

        Column 1Column 2count(*)
        YY25
        YN25
        NY25
        NN25

        If there is a correlation, however, so that most rows that have Y in one column also have Y in the other column, then the estimate is way off.

        Column 1Column 2count(*)
        YY49
        YN1
        NY1
        NN49

        If you query one of the rare Y/N combinations, the optimizer might refrain from using an index due to the high cardinality estimate. Nevertheless, it would be better to use the index because this particular combination is very selective.

        It think supporting hints are not that bad: they are just a way to cope with known limitations of the optimizer. That's probably why they tend to become obsolete when the optimizers evolve.

          (Read more)

          2013-07-18 Pagination Done the Right Way

          Here is another slide deck for my "Pagination Done the Right Way" talk that I've given at many occasions.

          Please also have a look at this blog post by Gary Millsap about “The Ramp”. Do you see how using OFFSET implements this anti-pattern?

          (Read more)

          2013-07-10 Indexes: The neglected performance all-rounder

          I think I've actually never shared the slides of my talk given in Paris at Dalibo's PostgreSQL Session about Performance. So, here they are.

          2013-07-02 Afraid of SSD? Will SSD kill the profession of database and SQL tuning specialists?

          When clients tell me about their plans to invest in SSD storage for their database, they often look at me like a doctor telling a patient about his deadly disease. I didn't discuss this with my clients until recently, when one client just asked me straight away: “As SQL tuning guy, are you afraid of SSD because it kills your job?” Here is what I told that client.

          (Read more)

          2013-05-21 Training and Conference Dates

          A few weeks ago I invited you to take part in a survey about your interest in SQL performance training for developers. In the meanwhile there is a schedule for German and English trainings available. In particular, I'd like to point out four online courses I'm giving during summer time. Hope to see you there.

          Another opportunity for a short get-together are conferences I'll attend and/or speak at. The next one is the Prague PostgreSQL Developers' Day (p2d2) next Thursday. You can buy SQL Performance Explained there (CZK 700; in the breaks and after the conference). The next conference that I can already confirm is this years PostgreSQL Conference Europe in Dublin end of October. You might have noticed that I attended a lot of PostgreSQL conferences recently (Brussels in February, Paris in March). I do plan to attend other conferences too and I've just filed some proposals for talks at other conferences. I'll let you know if they are accepted.

          (Read more)

          2013-04-23 The two top performance problems caused by ORM tools

          ORMs are not entirely useless…” I just tweeted in response to a not exactly constructive message that we should fire developers who want to use ORMs. But than I continued in a not exactly constructive tone myself when I wrote "…the problem is that the ORM authors don't know anything about database performance”.

          (Read more)

          2013-04-22 Ich brauche deine Hilfe!

          Hallo!

          Schenkst du mir bitte ein paar Minuten deiner Zeit, um mir zu helfen?

          (Weiter lesen)

          2013-03-04 Party time

          Good news everybody. I'm in a good mood today ;) I'll explain. First of all, the French edition of SQL Performance Explained is almost done and will ship at the end of the month (pre-order now ;). It has a different name (SQL : Au cœur des performances) but is otherwise the same. So, that triggered some kind of "project done" feeling.

          Further, I've given a few good trainings recently and looking forward for some more in the next weeks. It's just awesome when the audience comes up with great questions, but it's even more awesome if they come up with great conclusions that proof their correct understanding. That triggers a "missing accomplished" feeling.

          (Read more)

          2013-02-22 Better Performance with PostgreSQL

          Dalibo, the company where Guillaume Lelarge works (he does the French translation of SQL Performance Explained) invited me to give a talk at their (mostly French) for-free conference “Better Performance with PostgreSQL” on 28th March in Paris. Seats are limited: if you'd like to go there, register now!

          (Read more)

          2013-02-05 Top Tweets January 2013

          This is a short collection of the best tweets on @SQLPerfTips and/or @MarkusWinand for those who missed them and those who don't use twitter at all.

          (Read more)

          2013-02-05 FOSDEM Impressions

          Last weekend I've visited the Free and Open source Software Developers’ European Meeting (FOSDEM) in Brussels, Belgium. It is a huge and free conference covering countless open source topics such as Open/Libre Office, Microkernels, MySQL, NoSQL, PostgreSQL, OWASP, and much more.

          (Read more)

          2013-01-08 I'm speaking at FOSDEM PGDay 2013

          My talk “Pagination done the PostgreSQL way” was accepted for the one-day PostgreSQL conference PGDay on 1st Feb 2013 just before FOSDEM in Brussels.

          (Read more)

          2012-12-28 Top Tweets December 2012

          This is a short collection of the best tweets on @SQLPerfTips and/or @MarkusWinand for those who missed them and those who don't use twitter at all.

          (Read more)

          2012-12-19 French anybody? Use The Index, Luke is now available in French.

          Good news everybody—well, at least if you speak French. Use The Index, Luke and SQL Performance Explained will be translated. You can already start reading the first chapters here.

          (Read more)

          2012-12-06 Instant What?

          You might guess it, but I'm not making my living from this site nor are the book sales paying all the bills—although the books pays some bills, thank you! However, to pay the remaining bills I'm doing coaching, training and consulting. You can find all the details on my professional site http://winand.at/ but I'd like to highlight one service here: Instant Coaching.

          After googling and trying many ways to solve a problem, have you ever got that feeling of resignation where you just want to ask somebody who should know the answer? Well, that's Instant Coaching—at least if your problem is about performance and/or databases.

          (Read more)

          2012-11-15 Joe Celko's review of “SQL Performance Explained”

          Joe Celko's review of my book SQL Performance Explained was just published. The bottom line: “This book is definitively worth having in the company library.”

          2012-11-13 PG.Conf EU 2012 — Summary

          With a little delay of just three weeks, I'm writing something about this years PostgreSQL conference Europe.

          Including the training day, it was four days full of database stuff—in other terms: an awesome time, if you love databases :) But let's start from the beginning.

          (Read more)

          2012-10-12 Stuff you missed without Twitter

          If you are not following my SQL Performance Tips (@SQLPerfTips) feed on twitter, you are missing a lot. However, two things you should probably know, even without using twitter:

          All You Base Conf — Database conference for web developers

          I'm in no way affiliated with those guys, but they were so nice to offer a discount code for my readers (followers?). The conference takes place in Oxford, UK on 23rd Nov 2012. Have a look at http://allyourbaseconf.com/ for more details. Use the promo code “SQLPerf” to get £35 off the standard ticket price.

          Celebrating 10k followers

          My @SQLPerfTips account has crossed the 10k followers mark recently. So I though I'll celebrate this with another discount code (10kflwrs) that is good for -10% for orders of “SQL Performance Explained” when placed directly at http://sql-performance-explained.com/. This code expires on Fri, 12th Oct 20:00 Vienna local time (CEST). That's 11am PDT, for example.

          2012-10-08 My PGConf.EU 2012 Schedule

          As mentioned before, I'll be at this year PostgreSQL Conference in Prague. Still two weeks to go, but I just had a few minutes "free" time and prepared my personal schedule.

          As usual, I plan to attend as many sessions as possible. As usual, I'll skip some in favour of a nice chat and/or change my mind in the last moment.

            (Read more)

            2012-08-13 Zweiter Jahrestag

            Heute feiert „Use The Index, Luke!“ seinen zweiten Jahrestag. Leider bin ich nicht so gut vorbereitet wie letztes Jahr, als ich den 3-minuten Test vorstellte. Der ist übrigens bis heute sehr beliebt und wird täglich ca. 20-mal durchgeführt. Heute kann ich euch nur ein Versprechen anbieten.

            Kennst du http://sqlfiddle.com/? Solltest du aber ;) Es ist ein Online-Tool mit dem man SQL-Abfragen auf fünf verschiedenen Datenbanken (SQL Server, MySQL, Oracle, PostgreSQL und SQLite) ausprobieren kann. Es ist keine Cloud-Datenbank in dem Sinn, dass man damit Anwendungen bauen könnte. SQL Fiddle ist ein Tool, um kollaborativ an SQL-Abfragen zu arbeiten und wurde mit dem Hintergedanken entwickelt, die Beantwortung von SQL-Fragen auf Seiten wie Stackoverflow zu erleichtern.

            (Weiter lesen)

            2012-08-13 Convinced

            After publishing the English edition of “SQL Performance Explained” there was a huge demand for an ebook edition. After a few days answering these queries, I though it's probably easier to offer an e-book than to explain why there isn't one. Well, I must admit that I don't read ebooks very much. My wife has an e-ink device, but is hardly using it (probably in lack of German content). I've uploaded the ePUB pre-release from 2011 (not available anymore) and must say it works, but isn't nice. However, many people told me they would be fine with a PDF, because they are anyway using tablets or smartphones to read their ebooks. Fast forward a few weeks: since Monday I can offer a PDF download for just EUR 9.95 (English, German [update] or French, you choose).

            Afterwards there was a huge demand—again—for a bundle discount when purchasing both, the PDF and the hard copy. That caught me by surprise. Not that people want a discount, but that people would like to buy both editions. So I asked, and listened, and learned something about my own business.

            (Read more)

            2012-08-12 Zoll und, nunja, Errata...

            Ich habe es endlich geschafft, die bisher gemeldeten Errata online zu stellen. Es ist zwar nichts, worauf ich besonders Stolz bin, aber es gibt schon mehr als 30 bekannte Errata in der deutschen Ausgabe. Ich habe zwar noch einige Stück der deutschen Ausgabe auf Lager, es scheint aber so als müsste ich demnächst eine zweite Auflage machen. Dann werde ich diese Fehler natürlich alle berücksichtigen. In der englischen Ausgabe gibt es bisher nur zwei bekannte Fehler—das wird aber nicht lange so bleiben. Falls du einen Fehler findest, der nicht in der Liste ist, würde es mich freuen, wenn du ihn meldest.

            Und jetzt, ganz was anderes: Zoll. Ich habe mehrere Anfragen erhalten, ob ich sagen kann, wie viel Zoll verrechnet wird, wenn man das Buch außerhalb der EU bestellt. Ich kenne die Zollsätze der einzelnen Länder natürlich nicht, aber ich kann helfen es zu googeln. Zur Bestimmung des Zolltarifes benötigt man die sogenannte HS-Nummer und das Ursprungsland der Ware. Die HS-Nummer für Bücher ist 490199 und meine Bücher werden in Österreich gedruckt. Daher schreibe ich "490199 - printed in Austria" auf den Zollzettel CN22 der aussen am Packet angebracht ist. Ich wurde auch darauf aufmerksam gemacht, dass manche Länder neben Zoll auch andere Gebühren einheben können (z.B. USt). Dazu habe ich aber keine weiteren Details.

            (Weiter lesen)

            2012-08-08 Endlich: beinahe weltweiter gratis Versand

            Die englische Ausgabe von „SQL Performance Explained“ ist endlich verfügbar. Die englische Ausgabe hat natürlich auch eine eigene Web-Site http://sq l-performance-explained.com/ auf der es eine PDF-Vorschau und Bestellinformationen gibt. Der Listenpreis ist EUR 29,95 oder GBP 26,99.

            Wenn man es außerhalb der EU bestellt, muss man berücksichtigen, dass man eventuell noch Import-Zoll im Zielland zahlen muss!

            (Weiter lesen)

            2012-05-25 Fertig! Oder was?

            In den letzten Wochen ist es hier auf Use The Index, Luke! etwas ruhig geworden. Heute möchte ich die Neuigkeiten, die dazu geführt haben, kurz zusammenfassen und auch einen kleinen Ausblick geben, wie es weiter geht.

            Die wichtigste Neuigkeit ist, dass ich Vater wurde. Naja, zumindest ist es für mich das Wichtigste ;) Mein Sohn, Marcel, wurde am 25.3.2012 geboren. Wir hatten also schon zwei Monate Zeit uns einzuleben. Die Mama hat die Nacht­schicht ganz gut unter Kontrolle. Ich kann also relativ normal schlafen und habe tagsüber dementsprechend Zeit und Energie zum Arbeiten.

            (Weiter lesen)

            2011-08-13 One Year and Three Minutes

            Use The Index, Luke! was one today.

            There was quite some progress during this year. Besides the steadily growing content, I am particularly happy that the appendices about execution plans and the example schema cover MySQL, PostgreSQL and SQL Server in addition to Oracle.

            (Read more)

            2011-07-30 MySQL Zeilengenerator

            Ein Zeilengenerator ist eine Methode um Zeilen nach Bedarf zu erzeugen. Zeilengeneratoren können mit reinem Standard SQL und rekursiven common table expressions (CTE bzw. die with-Klausel) umgesetzt werden. Falls du noch nie etwas von der with-Klausel gehört hast, liegt das bestimmt daran, dass MySQL diesen Teil des SQL-99 Standards nicht unterstützt (feature Anfrage aus 2006). Dieser Artikel stellt eine Sammlung von Zeilengenerator views für MySQL vor. Nicht so leistungsfähig wie CTEs, aber gut genug für die meisten Fälle. Aber bevor wir uns die Implementierung ansehen, werde ich erst einmal zeigen, wofür man Zeilengeneratoren überhaupt benutzt.

            Zeilengeneratoren sind praktisch um Lücken in Ergebnissen zu füllen. Zum Beispiel im folgenden Fall:

            (Weiter lesen)

            2011-07-16 Planning For Reuse

            There was a discussion about bind parameters and execution plan caching in a recent reddit thread about my "Avoid Smart Logic for Conditional WHERE clauses" page. My article mentions that bind variables make the actual values "invisible" for the optimizer resulting in a generic plan that can be cached and re-used later on. Jeff Davis emphasized that using bind parameters and the caching of execution plans are two distinct concepts and provided a PostgreSQL/Ruby example for that.

            (Read more)

            2011-06-24 Dear Database Vendors

            I have one wish for each database. Some of them are big wishes, so I thought I'll better send them early. Only six month until Christmas, you know ;)

            Oracle

            '' IS NULL? Are you serious? I know, it has always been that way, but does that mean it will stay so forever?

            SQL Server

            Could you please enable READ_COMMITTED_SNAPSHOT per default? Or, at least, make it a mandatory question during database creation? That could prevent so many locking problems.

            PostgreSQL

            Could you please implement index-only scans (aka "covering indexes")? It's a great performance feature, which I really miss in PostgreSQL.

            UPDATE: This one became true with PostgreSQL 9.2.

            MySQL

            Could you please implement the hash join algorithm? So many applications suffer from poor join performance, just because MySQL doesn't have hash joins.

            From today's perspective, it looks like my PostgreSQL wish is the only one that might come true. But I'll keep hoping for the others as well.

            Über den Autor

            Foto von Markus Winand

            Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

            Sein Buch bei Amazon kaufen

            Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

            Die Essenz: SQL-Tuning auf 200 Seiten

            Bei Amazon kaufen
            (Taschenbuch)

            Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

            Holen Sie sich Markus

            …für ein Training ins Büro.

            Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

            Erfahren Sie mehr»

            „Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
            Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz