jOOQ Tuesdays: Markus Winand is on a Modern SQL Mission

Welcome to the jOOQ Tuesdays series. In this series, we’ll publish an article on the third Tuesday every other month where we interview someone we find exciting in our industry from a jOOQ perspective. markuswinandThis includes people who work with SQL, Java, Open Source, and a variety of other related topics.

We are excited to talk with Markus Winand in this sixth edition. Markus is the author of the popular book SQL Performance Explained and the even more popular website Use The Index, Luke, and we’re thrilled to see that he’s pulling off another stunt:

Hi Markus – You have recently launched modern-sql.com. What is your goal with this website?

My goal for modern-sql.com is to create a textbook and reference about the SQL goodies you didn’t learn in school or university. Interestingly, online manuals about these features are pretty sparse. They come in two fashions: blog posts and vendor documentation. Blog posts are usually one-off events covering a particular feature or use case. There are many great blogs out there – the jOOQ blog being one of them – but there is no one I could recommend to learn all about recent SQL features. Vendor documentation, on the other hand, is mostly a reference about syntax—quite often even a bad one: they often don’t mention standard compliance at all and tend to follow a “proprietary features first” approach.

The consequence is that SQL market is very fragmented: besides SQL-92, there is no obvious base that is common to all databases. This becomes particularly evident on the job market: job offers either require just SQL—meaning good old relational SQL—or they require experience with a specific product. That’s pretty much the norm nowadays and nobody questions it. However, how would you think about this job opening: “Google Chrome Web Developer.” Web developers can’t choose the client’s browser. Many tried, but failed. Remember “optimized for XYZ”? That’s why web developers demanded standard conformance from browsers over the past decades. Just having launched a new website I can say that CSS conformance has improved drastically over the past five years. Ultimately, I’d like the same thing to happen for SQL. I hope that modern-sql.com sparks interest in standard conforming SQL so that developers also start to demand standard conformance from the database vendors. Quite an ambitious goal.

Last year, you’ve gone into battle against the SQL OFFSET clause. Want to shed some light on the background of that campaign?

The most striking problem with OFFSET is that it is generally used for an invalid use case: pagination. In this case, OFFSET is used to skip over a number of rows in the intention to find the rows following the previously selected ones. However, OFFSET does per definition not return the rows following one that was selected earlier, but just discards the first N rows of the result. Coincidentally, OFFSET yields the expected result if the data has not changed in the meanwhile—a case that is pretty common during development. But as soon as rows are added or deleted, discarding a fixed number of rows just doesn’t give the right result. The correct approach is to remember the last row fetched and use this data in a WHERE clause to select the rows following. This approach is explained in detail at http://use-the-index-luke.com/no-offset.

Besides the fact that OFFSET cannot be used to implement correct pagination, OFFSET is also bad for performance. OFFSET is wrong and slow. What else do you need? As a matter of fact, the only valid use case I know for OFFSET it to implement SLEEP in SQL—not that I ever need that. Unfortunately, OFFSET made it into the SQL standard in 2011. I consider this the worst mistake in recent history of SQL because it can’t be corrected. The only good part is that it is an optional feature—vendors don’t need to implement for standard conformance. Nevertheless, Oracle and Microsoft just recently added OFFSET to their SQL databases.

You’ve written a very popular book on SQL Performance called SQL Performance explained. How does it compare to other SQL books and why should our readers buy it?

I’ll start with the second question. First of all you must know that the full content of SQL Performance Explained is available for free at http://use-the-index-luke.com/. Most people I’m asking why they bought the book did so because the like the web site. They bought the book either to support my work on Use The Index, Luke (greatly appreciated!) or, more importantly, to finally read the book from cover to cover. A typical answer I get goes along these lines: “I knew Use The Index, Luke for years and have read many articles there, but I finally wanted to read everything from the beginning to end.”

Now coming to the first question why the world needed another SQL performance tome: it didn’t. Therefore, I wrote a very small book that can be read in less than a day. I focused on the basic concepts, which are the same in most databases, and boldly skipped less common special cases. Its shortness is also most appreciated in the reviews. On the other hand, the book has occasionally being criticized as being incomplete—probably because the sub-title reads “Everything developers need to know about SQL performance”. Personally, I think these critiques somehow proof my point: Obviously, Java, PHP or .NET developers don’t need to know as much about SQL performance as database performance consultants. When writing for such an audience, you must skip a lot.

Where do you see SQL in 10 years from today?

I hope that the temporal features of SQL:2011 (see here) become commonly available—also in free open source databases. At the moment, they are only available in commercial databases—even there the completeness and standard conformance varies. I would also hope that the SQL standard finds a way to cope with the current trend that every database vendor adds its own proprietary set of JSON functions. Unfortunately, it might be too late for that already.

However, my greatest hope is that developers realize that SQL is not stuck in 1992. The standard has added many useful features since than. Most databases offer a good part of these features. It’s really just our perception of SQL that got stuck in 1992.

Learn more about Markus’s work

… Markus is giving his Modern SQL talk at conferences. Learn more about it here:

Still Using Windows 3.1? So why stick to SQL-92?

We’ve been blogging a lot about the merits of modern SQL on the jOOQ blog. Specifically, window functions are one of the most fascinating features. But there are many many others.

Markus Winand, author of the popular book SQL Performance Explained has recently given a very well-researched talk about modern SQL. We particularly like his headline:

Should this wonderful presentation have convinced you to buy a copy of SQL Performance Explained (our review here), do not forget to enter the “jOOQ” promo code for an exclusive 10% discount!

jOOQ Newsletter: jOOLY 23, 2014 – Only 8 Days Left in jOOLY

subscribe to this newsletter here

Only 8 Days Left in jOOLY

Time is running so fast! The month of jOOLY is almost over – have you taken advantage of our limited-time promotional discount of 20% that we’re offering to all of your purchases in July 2014? And that’s not it, you will also get a free copy of the popular e-book SQL Performance Explained by Markus Winand, a book that we believe belongs on the shelf of every SQL developer.

Act now to get 20% off your next jOOQ purchase!

Tweet of the Day

Our customers, users, and followers are sharing their love for jOOQ with the world and we can hardly catch up with them! Here are:

Álvaro Hernández Tortosa, who has the final word on frameworks that hide SQL, because SQL is really powerful

Calvin Thomas, who Has come to an end of his search for the stack he direly needs. And that consists of AngularJS, Bootstrap, Play, Scala, jOOQ. Well done!

The famous Adam Bien, who explains how to properly use jOOQ in a Java EE context.

Thanks for the shouts, guys! You make the jOOQ experience rock!

New tiered pricing model

In the recent months, we have been having a lot of interesting discussions about our workstation-based pricing model, and how that fits in larger organisations with more fluctuation among team members.

We think of our workstation-based model as particularly fair because the price increases when more value is added – but we have heard the various concerns about simplifying the administration effort for large volumes. This is why we’re now officially offering a tiered pricing model on all subscriptions larger than 10 workstations.

If this is interesting for your organisation, please consider the updated license textcontaining prices (on page 17), or contact us directly.

Of course, if you act quickly, this offering can be combined with the “jOOLY” promotional discount to help you get even more value out of your next purchase!

jOOQ 3.5: Oracle AQ Support

The upcoming jOOQ 3.5 will ship with an extension to the code generator and the API that will make using Oracle AQ with jOOQ as easy as everything else!

Oracle AQ is a very powerful feature when you need to notify your database clients of data changes. Typical use-cases include triggers on updates needing to invalidate a UI cache for an “expensive” value.

If you’re using Oracle AQ with JDBC directly, however, you might be put off by the complexity of binding / loading OBJECT types from CallableStatements. Not with jOOQ.

This is what an enqueue call will look like:

DBMS_AQ.enqueue(conf, QUEUE_NAME, object);

And this is what a dequeue call will look like:

MyObjectType object = DBMS_AQ.dequeue(conf, QUEUE_NAME);

Both the MyObjectType and the QUEUE_NAME reference are generated objects with type information associated with them. This means, you can enqueue / dequeue just as if Java were the same as PL/SQL. Excited? We are!

Community Zone – The jOOQ aficionados have been active!

The jOOQ community has been very active again in the last month. We’re happy to point out these editor’s picks from our radar:

Tired of building with Maven? We’re very happy to announce Etienne Studer’s publication of a fully-functional gradle-jooq-plugin. This is a great community effort for those of you working with jOOQ and Gradle – or even Groovy in general.

Bert van Langen is a passionate DB2 DBA who has given us this excellent introduction to jOOQ on his blog. An alternative tutorial that should get new users started very quickly.

Marco Behler has published a treaties about the Java persistence ghetto (and how jOOQ might change that). We’re very glad to see that Marco is also coming to the same conclusion that we try to repeat time and again: Nothing keeps you from using JPA and SQL (e.g. in the form of jOOQ) in the same project.

Feedback zone

You’ve read to the end of this newsletter, that’s great! Did you like it? What did we do great? What can we improve? What other subjects would you like us to cover?

We’d love to hear from you, so if you want to reach out to us, just drop a message tocontact@datageekery.com. Looking forward to hearing from you!

jOOQ Newsletter: April 30, 2014

Subscribe to this newsletter here

Tweet of the Day

Our customers, users, and followers are sharing their love for jOOQ to the world. Here are:

Santiago M. Mola who appreciates jOOQ’s affinity to SQL features and its correctness

Vlad Mihalcea who’s reading the jOOQ docs to learn about SQL

Thanks for the shouts, guys! It looks our attempts to make our users competent and enthusiast SQL aficionados are effective. If you, our customers, are productive with Java and SQL, then we did our job right. Last but not least:

Tom Bujok who’s is now a happy SQL aficionado.

Want awesome free jOOQ stickers yourself? Contact us!.

Book Promotion – 10% off SQL Performance Explained

This book is a must-read for every SQL developer! Invest a very reasonable amount of money and around six hours of quality reading time, and you’ll stop wasting days and days of confused SQL tuning guesswork, because once you understand indexes, you are able to solve 90% of your performance issues.

Get your copy of SQL Performance Explained now! Use the limited-time “jOOQ” coupon to get a 10% partner discount:

http://sql-performance-explained.com

Community Zone – The jOOQ aficionados have been active!

In last week’s newsletter, we’ve praised Petri Kainulainen’s latest piece of work, his jOOQ / Spring / CRUD tutorial:

But that was not his last shout. He’s already promised another tutorial explaining how to do sorting and pagination with jOOQ:

Luckily for us Markus Winand – our SQL Performance Partner jumped in and reminded us of the difference between OFFSET pagination and KEYSET pagination. More details about these tools in his book – see above.

Michael Hughes from codinginthetrenches.com has also been discovering the benefits of using a SQL DSL, which he has explained in his article here. We always like it when jOOQ users realise the power of using jOOQ with stored procedures. You will be indefinitely more efficient compared to the standards JDBC or JPA.

Krisztian Horvath from SequenceIQ has published a post about using jOOQ with HBase – why not? If you’re restricting yourself to using plain SQL, you can use the jOOQ Open Source Edition with a lot of databases, although you’re going to be missing out on the coolest features and productivity boosters, of course.

Harmeet Singh appreciates the jOOQ code generator in his introductory blog post, an important point to mention. jOOQ’s code generator has helped our customers avoid so many errors already at compile-time, instead of waiting for them to appear in production, at runtime.

SQL Zone – When to use views

There are a couple of great reasons why you should occasionally (or systematically) use views instead of tables. These reasons include:

  • Views provide abstraction over tables. You can add/remove fields easily in a view without modifying your underlying schema.
  • Views can model complex joins easily.
  • Views can hide database-specific stuff from you, e.g. using Oracle’s SYS_CONTEXT for security checks.
  • Views can be useful for managing GRANTS, and thus hiding tables from you.
  • Views can help you with backwards compatibility when you change the underlying schema, but leave the (versioned) views in place.
  • Views can implement an additional security layer, e.g. by using Oracle’s WITH CHECK OPTION directly in the view.

See the relevant Stack Overflow question here.

SQL Zone – Calculating a running total

We’ve said it before. Use your database for your calculations, when this is reasonable. And by this, we don’t just mean simple COUNT(*) or SUM(AMOUNT) calls. We cannot stress the fact that you should be using window functions enough.

Imagine you want to do something fancy like calculating a balance value on every bank transaction based on the current balance and each transaction’s amount:

| ID   | VALUE_DATE | AMOUNT |  BALANCE |
|------|------------|--------|----------|
| 9997 | 2014-03-18 |  99.17 | 19985.81 |
| 9981 | 2014-03-16 |  71.44 | 19886.64 |
| 9979 | 2014-03-16 | -94.60 | 19815.20 |
| 9977 | 2014-03-16 |  -6.96 | 19909.80 |
| 9971 | 2014-03-15 | -65.95 | 19916.76 |

You can do this in SQL! Check out our recent blog post about how to calculate a SQL standard running total directly in SQL!

Upcoming Events

The next weeks are a bit quiet, but we’re going to be active again in May and June! Have you missed any of our previous jOOQ talks? Soon you’ll get another chance to hear us talk about jOOQ or SQL in general in any of these upcoming events:

Stay informed about 2014 events on www.jooq.org/news.

The Premature Return to SQL

In online communities, the NoSQL topic (much like the ORM topic) is a guarantee to stir emotions. Many emotions are stirred by evangelists on either side for ideological or marketing reasons. Here’s an interesting post by Alex Popescu, a passionate NoSQL and polyglot persistence evangelist, claiming that the recent trend to return to SQL is premature:

This post triggered an equally interesting reaction by Markus Winand, author of SQL Performance Explained:

It’s really interesting, how often people think in terms of “trends” that introduce novel paradigms, obsoleting all we had before. I believe that these are not trends, but experiments. I’ve blogged before that you should be wary when NoSQL vendors promise you to put an end to DBAs. Very few “new” solutions or paradigms have ever completely replaced or substituted their predecessors. Or, in Isaac Newton’s words:

If I have seen further it is by standing on the shoulders of giants.

We’re not “returning to SQL”, nor is such a return “premature”. Yes, there are some innovative thinkers who are teaching an old elephant new tricks, and that’s good. It’s also good that such innovative thinkers get a piece of the cake and make money with their inventions.

It is also true that big database vendors are not very innovative. But they don’t have to be. Their asset is reliability, predictability, stability. Oracle SQL will still support all its age-old legacy in 15 years, which makes it a safe choice for banks and insurance companies. If a NoSQL or NewSQL feature proves to be innovative and reliable, Oracle et al. will most certainly pick it up and integrate it into SQL. Clever NoSQL vendors thus already prepare for their exits.

This happens outside the world of databases, of course:

  • Scala is innovative and contributes to Java (Generics in Java 5, Lambdas in Java 8).
  • Open Source developers (e.g. those of JAX-RS) are innovative and contribute to JEE.
  • PostgreSQL is innovative and contributes to other SQL dialects and eventually the SQL standard.
  • Instagram is innovative and contributed to Facebook (“shit happens!”).
  • jOOQ is innovative and contributes to JDBC and JPA (eventually, hopefully).

SQL is a safe bet and is here to stay.