SQL databases come up with algorithms you’d never have dreamed of (2017) [video]

So in 20 years we&#x27;ve gone from vilifying SQL statements in code, witch-hunted folks who did this and instead invented and propped up clunky ORM implementations, ignored sage advice on the famous &quot;object-relational impedance mismatch&quot; (which the author claims he doesn&#x27;t &quot;believe&quot; exists - it&#x27;s not a choice btw) and now, after 20 years of agent orange in the bloody Vietnam of Computer Science, we are presenting why SQL was better all along - showing off windowing functions as examples? This is irony that took 20 years to sink in.<p>Am I understanding this guy&#x27;s presentation correctly? (I left around the 20 min mark of the 50 min presentation).

This talk didn&#x27;t hook me, but then again, it looks like it&#x27;s aimed at developers who jumped on the NoSQL bandwagon and now need to be told why SQL is clever.<p>I feel that much of what we do with map, reduce, sort and filter on arrays in modern JS is similar to SQL. Unfortunately, there is no query optimisation, because that would break your procedural code.<p>What I&#x27;d like to see more of is domain-specific languages embedded in general-purpose languages, like React with JSX, or just a better bridge between the two. We have these two worlds of in-memory data structures on one side, and databases on the other, that we keep separate. NoSQL was an attempt at bringing them together, but it put far too little emphasis on complex queries and filters.<p>It would be nice if we could just define data structures and their relations once and not have to do it again. It would be nice if foreign keys neatly mapped to object references in OOP and everything stayed live, so if you change a reference, that happens instantly in the database too, unless you wrap it in a transaction block.<p>Now pull this off while still retaining the ability to open an SQL REPL to test out statements, or manipulate field definitions.<p>I think that&#x27;s what everyone wants, really. A seamless way for code to interact with data on all levels, with no perceptible boundary once you&#x27;ve opened a session to your DB server.

At around 22:10, the speaker summarises equivalencies between common SQL clauses (SELECT, GROUP BY, etc.) and what Java 8 calls a &quot;stream API&quot; (map, collect, etc.). <a href="https:&#x2F;&#x2F;blog.jooq.org&#x2F;2015&#x2F;08&#x2F;13&#x2F;common-sql-clauses-and-their-equivalents-in-java-8-streams&#x2F;" rel="nofollow">https:&#x2F;&#x2F;blog.jooq.org&#x2F;2015&#x2F;08&#x2F;13&#x2F;common-sql-clauses-and-thei...</a><p>Neat. I hadn&#x27;t thought of it that way before.

A very good way to understand the fact &quot;class are for encapsulating behaviour, not data&quot;. But we don&#x27;t learn until we fall.

I have strong .net background with mostly pretty legacy code and I&#x27;m amazed that this talk is so &quot;wow&quot; for some people. Till this point I wasn&#x27;t aware people has such problems like described in this talk. Maybe I&#x27;m just getting old, but SQL was my first idea to solve problem. I think for most reporting related tasks simple SQL will do. I think some people just get caught in their bubble and forgot the basics: choosing right tool for a task. Of course SQL has its issues: e.g. it is problematic to test it and to manage db model changes (code first aproach wins there, hands down), but for data extensive tasks it is the way to go. This whole talk just remind me some of legacy code I was investigating one time, where developer just though he can implement everythin on his own better than standard library. Probably you need some distance when looking on particular problem to see more just &quot;how I would just loop through all this data an calculate this&quot;.

May be the topic of talk should be how to compare apples and oranges for half of time in the talk.

I&#x27;ll save ppl some time:<p>* first 20 mins: Writing a contrived report query that&#x27;s easy in SQL is easy in SQL.<p>* but: most <i>real</i> reports require many different fields collated into many parts of say a pdf or xls. Once you take this into account writing custom code to do the entire report is often the only way to do it. :&lt;<p>* you often can&#x27;t do some of what the author suggests bc in a &quot;real&quot; org you have db zealots that only allow access to their precious through stored procs and a surprisingly large number of meetings<p>* trying to do some non-trivial where clauses? Not in SQL - so you have to use an external lang. I call this &quot;Works in my presentation&quot; syndrome.<p>* he acts surprised that a lang that uses a db through a SQL api can&#x27;t be as fast&#x2F;efficient&#x2F;terse as a system that uses SQL and has direct access to the db&#x2F;storage.<p>Stopped after that.<p>PS Seems like a funny guy tho - liked his style! :&gt;

This is quite possibly the best talk I&#x27;ve ever seen. No joke.

One of the biggest problems with embedding SQL directly into your application is the dreaded vendor lock in. In my experience each RDBMS vendor supports its own special flavor of SQL. Once you&#x27;ve baked that flavor into your code base it becomes non-trivial to change your SQL backend if necessary. From what I understand Amazon has spent years trying to untangle the rats nest of embedded Oracle SQL dependencies within its codebase so it can extract itself from Oracle vendor lock in. So before you bake that SQL string into your code think carefully about what SQL functionality you are using and ask yourself if its representation is specific to your current RDBMS vendor.

I really do think a lot of ORM use is because devs are afraid to use SQL or at least afraid of how others devs might use it. This is true whether devs want to admit it or not.<p>That said, SQL doesn&#x27;t lend itself to easy composability and you can end up with &quot;4000 line monsters&quot; if you really tried to put as much business logic into the query as possible.<p>Is anyone working on SQL extensions, ORM, or new 4th Gen language that could actually support something like 4000 lines of SQL in a maintainable way?

SQL feels opaque, and its syntax is one of a particular kind (can&#x27;t remember how it&#x27;s called), which makes it difficult to learn and understand how it&#x27;s parsed, because it&#x27;s not very well compartmentalized. If the language was better structured like other programming languages are, that&#x27;d be better, and jooq looks like something that should be standard across all languages.<p>Usually when I&#x27;m using any technology, being able to understand how it works is my most important priority, because it prevents me from using it improperly. I have a hard time understanding what those &quot;algorithms&quot; really are after all. I heard that databases engines use backtracking, but I&#x27;m not really sure that&#x27;s what it is talked about here. Maybe databases don&#x27;t use BT?<p>Using another language that parses every time you do something doesn&#x27;t feel very fast. It is fast for many applications, but I don&#x27;t think it is for all of them.<p>In the end, it&#x27;s the same old combat, either choose peak performance or development delays with good enough performance.<p>Although I have to admit that for GIS (geographic information system), databases with embedded R-trees and other things are very much welcome, since implementing those algorithms from the ground up is way too hard.

reminds me of the presentations coming out of the .NET world in 2005 after they launched LINQ. Nearly identical points being made.

Is SQL no longer featured as part of most Comp Sci&#x2F;IS courses any more?

Pretty funny talk, but also informative.

The problem with SQL is that SQL that works today will be too slow tomorrow. Fixing it involves trying to please the query planner, all while making your query less and less comprehensible.<p>As a contrasting example, the experience you get with ElasticSearch is that queries have predictable performance even as your data changes. In that way it&#x27;s much, much nicer. On the other hand, you lose joins, which is a huge downside.