Blog Archiv

Der folgende Chart zeigt, welche Systeme Where-Klausel-Ausdrücke, die auf die Jahreskomponente einer Date- oder Timestamp-Spalte zugreifen, in Bereichsbedingungen wie ts >= DATE'2024-01-01' AND ts < DATE'2025-01-01' umschreiben.

Die gelben Häkchen bedeuten, dass immer umgeschrieben wird – auch wenn ein bestehender funktions-basierter Index dadurch nicht mehr genutzt werden kann.

(Weiter lesen)

Einige Datenbanken, nämlich Microsoft SQL Server, IBM Db2 und PostgreSQL (seit Version 11), unterstützen eine Include-Klausel bei der Create Index-Anweisung. Die Einführung dieser Klausel bei PostgreSQL ist auch der Anlass für diesen längst überfälligen Artikel über die Include-Klausel.

Bevor es an die Details geht, möchte ich noch eine kurze Wiederholung über die allgemeine Funktion von (non-clustered) B-Tree Indizes und dem allmächtigen Index-Only-Scan machen.

1. Wiederholung: B-Tree Indizes
2. Wiederholung: Index-Only-Scan
3. Die Include-Klausel
4. Filter auf Include-Spalten
5. Unique-Indizes mit Include-Klausel
6. Compatibility
7. PostgreSQL: Kein Filter vor der Sichtbarkeitsprüfung

(Weiter lesen)

I haven’t blogged here in a while. It doesn’t mean Use The Index, Luke! is abandoned or anything the like. Au contraire, I’m having some topics in the pipeline I’m hoping to blog soon about. Furthermore, Use The Index, Luke! is regularly updated to reflect changes in newer databases releases.

The last updates were done a few weeks back when MySQL 8.0 was released (see my blog posts “One Giant Leap For SQL: MySQL 8.0 Released” and “Big News In Databases — Summer 2018”). Feel free to subscribe my modern SQL blog too (RSS, EMail) so you don’t miss any news.

But what I actually wanted to tell you about is my upcoming advanced SQL training in Vienna (Austria) from September 17 to 21 2018. Yes, that’s full week of SQL goodness.

The training covers the full content of my book “SQL Performance Explained” and a lot of the “modern SQL” topics I’m currently writing and talking about (window functions, recursion, …) but also barely known basics like the three-valued logic of SQL. You can find all details and register (without obligation) at my “commercial” website winand.at.

(Read more)

Die spanische Übersetzung meines Buches SQL Performance Explained ist jetzt erhältlich. Das ist die fünfte Sprache nach Deutsch, Englisch, Französisch, und Japanisch!

Man kann die spanische Übersetzung direkt auf meiner Webseite (PDF und Taschenbuch) und auf einigen europäischen Amazon-Seiten (nur Taschenbuch) kaufen. Die Preise sind ident zu den anderen Fassungen: €9,95 für das PDF, €29,95 für das Taschenbuch, €34,95 für PDF und Taschenbuch. Auf meiner Webseite gib’s weltweiten Gratisversand.

Wie immer gibt es den gesamten Inhalt auch auf https://use-the-index-luke.com/es/ – völlig gratis.

(Weiter lesen)

(Weiter lesen)

Bloß nicht den Überblick verlieren: Hier sind die wichtigsten Datenbank-Neuigkeiten der letzten sechs Monate.

1. SQL im Aufwind
2. Der Cloud-Krieg geht weiter
3. IBMs Umbenennungswahnsinn
4. Neue Hauptversionen
5. Neues auf meinen Webseiten
6. Via Twitter, in aller Kürze (folge mir auf Twitter)

(Weiter lesen)

Hier sind die wichtigsten Neuigkeiten seit meinem letzten „Neues über Datenbanken“-Artikel aus November 2016.

1. Neue Version des SQL-Standards
2. Oracle-Datenbank 12.2 als Download, Cloud-Preise für AWS und Azure verdoppelt
3. Datenbank des Jahres
4. ACIDRain: Studie zeigt das Risiko von falsch genutzten Transaktionen
5. Aus meinem Twitter-Feed

(Weiter lesen)

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)

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)

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 (LUW), Oracle, SQL Server and SQLite. MySQL does not yet support any of those features except OFFSET, which is evil.

One last thing before going on to the slides: Use The Index, Luke has a shop. Stickers, coasters, books, mugs. Have a look.

(Read more)

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 (until 11.1)
7. Decent Row-Value Predicates Support

(Read more)

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.

Im deutschen Sprachraum vermutlich auch nicht so interessant, aber dennoch sei es erwähnt: Use The Index, Luke wird gerade ins Japanische übersetzt. Rund die Hälfte ist bereits erledigt und online.

Und zu guter Letzt arbeite ich aufgrund einer Kundenanfrage nach meinem SQL-Performance-Kurs für IBM Db2 (LUW) daran Use The Index, Luke um diese Datenbank zu erweitern. Genau genommen arbeite ich mit der Gratis-Version Express-C für Linux, Unix und Windows (LUW). Der Anhang über Db2 Ausführungspläne ist fast fertig, der Rest folgt in den nächsten Wochen.

(Weiter lesen)

(Weiter lesen)

(Weiter lesen)

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)

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.

(Read more)

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)

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…

Everything was easily. Just write a where clause, no matter how complex, you found the right rows. Joins were equally easy to write and you took all the effort to combine the data from several tables as I needed them. I also remember how easy it became to manage the schema. Instead of writing a program to copy my data from one C struct to another, I just say alter table now—in the meanwhile it even works online in many cases! I didn’t take long until I used SQL for stuff I wouldn’t have thought a database could do for me. So I was quickly embracing group by and co.

But I haven’t spent a lot of time with you lately. It’s not because I was too busy. I’m still practicing what you have shown me! And I’ve moved on. Now I’m using common table expressions to organize complex queries and I use window functions to calculate running totals or just do a ranking. I’m also using joins more efficiently because I know about hash and sort/merge joins. A while ago I was wondering why you didn’t tell me about these things. But then I realized that you don’t know them.

(Read more)

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.

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)

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)

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.

First of all, I think everybody should know that I’m not a NoSQL fanboy, yet I’m open to the polyglot persistence idea. This distinction doesn’t seem to make sense if you read NoSQL as “not only SQL” (as you are supposed to do). However, I believe there are NoSQL systems out there that greatly benefit from the idea that SQL is bad and not using SQL is good. On other words, they offer “not using SQL” as their main advantage. MongoDB seems to be one of them. Just my perception.

But if I don’t like NoSQL, then I should like MySQL? Not exactly. In my eyes, MySQL has done great harm to SQL because many of the problems people associate with SQL are in fact just MySQL problems. One of the more important examples is that MySQL is rather poor at joining because is only supports nested loops joins. Most other SQL database implement the hash join and sort/merge join algorithms too—both deliver better performance for non-tiny data sets. Considering the wide adoption of MySQL (“The most popular open source database”) and the observation that many people move away from SQL because “joins are slow,” it isn’t far-fetched to say that an implementation limitation of MySQL pushes people towards NoSQL.

(Read more)

select * has a bad reputation. Most people think it is because changes in the table definition cause hard-to-detect bugs. Listing all columns in the desired order solves that problem, right? Yes, but that doesn’t solve the second problem of select *.

The other problem is that asking for more columns causes more work for the database. The surprise is that the performance impact can be huge.

Up to 100x slower when preventing an Index-Only Scan

Broadly speaking, the less columns you ask for, the less data must be loaded from disk when processing your query. However, this relationship is non-linear.

Quite often, selecting from a table involves two steps: (1) use an index to find the address where the selected rows are stored; (2) load the selected rows from the table. Now imagine that you are just selecting columns that are present in the index. Why should the database still perform the second step? In fact, most databases don’t. They can process your query just with the information stored in the index—hence index-only scan.

But why should an index-only scan be 100 times faster? Simple: an ideal index stores the selected rows next to each other. It’s not uncommon that each index page holds about 100 rows—a ballpark figure; it depends on the size of the indexed columns. Nonetheless, it means that one IO operation might fetch 100 rows. The table data, on the other hand, is not organized like that (exceptions). Here it is quite common that a page just contains one of the selected rows—along with many other rows that are of no interest for the particular query. So, the reason an Index-Only Scan can be 100 times faster is that an index access can easily deliver 100 rows per IO while the table access typically just fetches a few rows per IO.

If you select a single column that’s not in the index, the database cannot do an index-only scan. If you select all columns, … , well I guess you know the answer.

Further, some databases store large objects in a separate place (e.g., LOBs in Oracle). Accessing those causes an extra IO too.

Up to 5x slower when bloating server memory footprint

Although databases avoid storing the result in the server’s main memory—instead they deliver each row after loading and forget about it again—it is sometimes inevitable. Sorting, for example, needs to keep all rows—and all selected columns—in memory to do the job. Once again, the more columns you select, the more memory the database needs. In the worst case, the database might even need to do an external sort on disk.

However, most database are extremely well tuned for this kind of workload. Although I’ve seen a sorting speed-up of factor two quite often—just by removing a few unused columns—I cannot remember having got more than factor five. However, it’s not just sorting, hash joins are rather sensitive to memory bloat too. Don’t know what that is? Please read this article.

These are just the two top issues from database perspective. Remember that the network suffers too and that the client needs to process the data as well—which might put a considerable pressure on garbage collection.

(Read more)

(Read more)

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 1	Column 2	count(*)
Y	Y	25
Y	N	25
N	Y	25
N	N	25

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 1	Column 2	count(*)
Y	Y	49
Y	N	1
N	Y	1
N	N	49

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)

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 Cary Millsap about “The Ramp”. Do you see how using OFFSET implements this anti-pattern?

(Read more)

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.

(Read more)

(Read more)

(Read more)

(Read more)

Hallo!

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

Wie du vielleicht weißt — oder auch nicht — bestreitet ich meinen Lebensunterhalt als unabhängiger Trainer und Berater. Bisher habe ich meinen SQL-Performance Kurs für Entwickler nur bei meinen Kunden vor Ort abgehalten. Ich glaube aber, dass es Sinn macht den Kurs auch „normal“ anzubieten, damit man auch als einzelner Entwickler daran teilnehmen kann. Dafür müsste ich natürlich wissen, wie viele Personen an so einem Kurs interessiert wären, wo sie leben und welche Datenbank sie benutzen. Daher habe ich eine kurze Umfrage vorbereitet:

http://winand.at/de/leistungen/sql-performance-kurs/bedarf

(Weiter lesen)

(Read more)

(Read more)

(Read more)

(Read more)

(Read more)

(Read more)

(Read more)

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.

Instant Coaching is remarkably easy and efficient: we just arrange an online meeting where you present your problem or question to me. We’ll use a desktop sharing tool (TeamViewer) so that I can see your screen and—if you like—show you something on your computer (control your mouse/keyboard). Instant Coaching needs virtually no preparation: it often starts just a few minutes after you faced the problem—hence “Instant”!

Another critical success factor for Instant Coaching is that we can put our knowledge and experiences together: you bring the know-how about your application and your data while I’m adding a little database and performance know-how to the mix. Instead of guessing about things I don’t know about your application or you don’t know about databases, we can just ask each other! I’m usually getting asked a lot: you don’t just get the “magic” solution, but you learn about the rational behind it so that can solve similar problems on your own the next time—hence “Coaching”!

(Read more)

(Read more)

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.

I booked the full day training with Joe Celko on the first day. And I must say that was the best part of the conference—mostly because Joe is great. If you never meet Joe Celko, or don’t even know who the heck he is, you should definitely look for an opportunity to meet him.

The training he gave (“A day of SQL with Celko”) was a mixture of SQL related topics like keys, naming (e.g., plurals for tables), metadata, rollup (group by grouping sets, group by cube) and trees. Due to my day to day work, I’ve already seen some of the “rarely known features” he mentioned. It was still very valuable because he presented good examples for them. The “nested sets model” is particularly noteworthy. I hope to write about some of these topics soon.

(Read more)

(Read more)

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)

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.

Für mich ist SQL Fiddle wegen des kleinen „View Execution Plan“ Knopfes interessant :) Wie gesagt, es ist keine Cloud-Datenbank, auf der man Performance Tests durchführen könnte, aber man kann sich Ausführungspläne ansehen. Und ich glaube es wäre sehr praktisch, wenn es vorbereitete Fiddles gäbe, mit denen man die Beispiele aus „Use The Index, Luke!“ einfach online, ohne eine eigene Datenbank zu haben, ausprobieren kann.

Also genau das wird kommen. Zeitplan gibt es aber noch keinen. Bleib am Ball. UPDATE in 2015: SQLFiddle Integration wurde wieder entfernt, da sie kaum benutzt wurde und viel Arbeit verursacht hat.

(Weiter lesen)

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.

Sure there were usual suspected like “I’d just like the PDF for searching” “to have it with me when being on the road” and so forth. But the interesting point was “I’d like the hard copy, but would also like to start reading immediately.” Well, I didn’t think of that (shame on me). Although I’m offering free shipping to 150+ countries, it takes its time because I’m shipping from Europe only once a week. Instead of express delivery, I just need to offer a bundle.

It took another day to tweak my accounting software because there are different VAT rates applied to real books and PDF downloads—but the accounting software could do only one VAT rate for each invoice. After all I’m just a geek like most of you, so I hacked a day and made it work. The bundle is on sale for EUR 34.95 since yesterday evening—that’s a EUR 4.95 discount.

(Read more)

(Weiter lesen)

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!

Da ich weiß, dass ich überall auf der Welt Leser habe, habe ich auch einiges daran gesetzt einen weltweiten gratis Versand zu ermöglichen. Ich war beinahe erfolgreich. Wenn du das Direkt-Bestellungsformular benutzt, kann ich gratis Versand in über 150 Länder anbieten. Das ist jedoch nur möglich, wenn ich einen Mengenrabatt der österreichischen Post nutze. Daher versende ich internationale Bestellungen nur einmal die Woche.

SQL Performance Explained

(Weiter lesen)

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.

(Weiter lesen)

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.

But what’s next?

The content will be completed until early 2012 (final outline in the Preface). The second volume of SQL Performance Explained—the e-book edition—will be published at that time as well. Additionally, Use The Index, Luke! will be translated gradually into German.

(Read more)

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)

(Read more)

(Read more)