12 ms·
SQL databases come up with algorithms you’d never have dreamed of (2017) [video]
- asah 8y agoThis is quite possibly the best talk I've ever seen. No joke.
- skullborg 8y agoI'm liking it so far as well - I enjoy SQL though
- Sulong 8y agoMay be the topic of talk should be how to compare apples and oranges for half of time in the talk.
- StreamBright 8y agoFrom the business point of view there is only apple.
- lukaseder 8y agoYes that's what this talk is about. A lot of people recognise apples and oranges for what they are. The talk is meaningless to those people. A lot of other people don't / can't make the distinction (yet). For those people, the talk is an eye opener.
- didibus 8y agoPretty funny talk, but also informative.
- ak39 8y agoSo in 20 years we'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 "object-relational impedance mismatch" (which the author claims he doesn't "believe" exists - it'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. Am I understanding this guy's presentation correctly? (I left around the 20 min mark of the 50 min presentation).
- thegeomaster 8y agoRecently I tried using an ORM "properly" for a bigger application. I tried for days to fix the performance problems, which were mainly caused by serialization/deserialization overhead because of humongous generated queries where I didn't need 90% of the info. I finally gave up, and now most of the ORM usage in the codebase is just to communicate my custom queries to the underlying database and translate to/from domain entities. I feel like this is the sweet spot―give me a thin abstraction over SQL and do the gruntwork of converting domain entities to tables and vice-versa, but nothing else, thanks. I'm still unhappy about the performance (when everything is in the working set, query takes 2ms and returns a couple of rows but the whole API call, which doesn't do much except massage the response to some kind of JSON, takes 30ms!), but that's on me for my ORM/language/runtime choice, I suppose.
- lugg 8y agoWhat language are you using? Most languages plain sql drivers have something like a cast result row into object as you cursor over the set. E.g. PHP has fetchObject https://www.php.net/manual/en/pdostatement.fetchobject.php https://www.php.net/manual/en/pdostatement.fetchobject.php
- ak39 8y ago"I feel like this is the sweet spot―give me a thin abstraction over SQL and do the gruntwork of converting domain entities to tables and vice-versa, but nothing else, thanks." Something like iBatis/nBatis? (Haven't used this since 2004)
- lukaseder 8y agojOOQ!
- coleifer 8y agojOOQ is legit. I found it very helpful to read jOOQ as I was writing my own library in python. There's a huge difference between a query-builder and something like hibernate or sqlalchemy. Query-builders make it easy to compose queries, leaning on your programming language to enforce syntactic correctness. When you're dealing with SQL directly, composition means string concatenation. A query builder gives you a data-structure that represents a query. I think it's helpful to distinguish between query-building and object mapping when the "orm-vs-sql" thing comes up.
- rgoulter 8y agoAt around 22:10, the speaker summarises equivalencies between common SQL clauses (SELECT, GROUP BY, etc.) and what Java 8 calls a "stream API" (map, collect, etc.). https://blog.jooq.org/2015/08/13/common-sql-clauses-and-their-equivalents-in-java-8-streams/ https://blog.jooq.org/2015/08/13/common-sql-clauses-and-thei... Neat. I hadn't thought of it that way before.
- mjburgess 8y agoI teach most (dynamic) functional programming constructs by starting with SQL. Python comprehensions, etc. are mostly just SQL -- likewise most languages where "map" is part of the collections API. SQL motivates the utility of functional programming for data-oriented applications.
- collyw 8y agoHave you got any material online to look at? I am curious to see the sort of stuff you are talking about ("Python comprehensions, etc. are mostly just SQL" doesn't make a lot of sense to me right now. I see them as an inline for loop).
- afandian 8y agoI wouldn't compare a comprehension to a loop because a loop doesn't typically have an output. If a loop does produce an output it's through side effects. List comprehensions are the same as maps, and generator comprehension even more so, as they can compose functions not values. And a map can be the same as a projection as found in sql.
- taeric 8y agoLoops in lisp would like a word with you. :) The difference is that many languages have constructs that weren't value producing. It is why some have the ternary operator, but lisp just had if statements. So, to that end, most loops do produce a value. And there are several common ways they do it. In many languages, you have to give the details of how they work. In some, you can only those details and the building if the output is a bit more declarative.
- ilitirit 8y agoIs SQL no longer featured as part of most Comp Sci/IS courses any more?
- collyw 8y agoGod help us if it's not.
- watsocd 8y agoI am teaching a college course on web development later in the spring and teaching raw SQL will be one of the main segments. I will discuss a few ORMs to make the students aware they are out there but we will not teach one in the course.
- Izkata 8y agoIt was for mine (2006-2010), but significantly more limited than you'd expect. It was essentially bare minimum, focused on theory, and could be summarized as "SELECT .. FROM .. INNER JOIN .. WHERE .." Most of the focus was on things like database normalization instead of practical usage. I recall upon starting my first job, a co-worker gave me a crash course in what exactly a LEFT JOIN was (though at this point it had been ~3 years since the college database course, which had been so lackluster I hadn't used one during those years).
- continuational 8y agoThe 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. 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's much, much nicer. On the other hand, you lose joins, which is a huge downside.
- anticensor 8y agoButcher a SQL adapter over Elastic output and call it a day.
- michaelcampbell 8y agohttps://github.com/NLPchina/elasticsearch-sql/ https://github.com/NLPchina/elasticsearch-sql/ https://www.elastic.co/products/stack/elasticsearch-sql https://www.elastic.co/products/stack/elasticsearch-sql
- bladelessninja 8y agoYou miss the point. ElasticSearch just do some gardening for your data automaticaly, where in pure SQL you need to do it yourself, but there are tools for it.
- Seb-C 8y agoThis is actually more a problem of using MySQL than a problem with SQL itself in my experience.
- ThJ 8y agoThis talk didn't hook me, but then again, it looks like it's aimed at developers who jumped on the NoSQL bandwagon and now need to be told why SQL is clever. 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. What I'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. 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. Now pull this off while still retaining the ability to open an SQL REPL to test out statements, or manipulate field definitions. I think that's what everyone wants, really. A seamless way for code to interact with data on all levels, with no perceptible boundary once you've opened a session to your DB server.
- lugg 8y agoLook up some of the old YouTube vids on datomic / rich Hickey. "Database as a value" had me jaw dropping and then crying most of the way through..
- dustingetz 8y ago+1 for "Database as a Value", this talk changed my world
- dustingetz 8y agoHow about something like this? http://www.hyperfiddle.net/ http://www.hyperfiddle.net/
- jstimpfle 8y ago> map, reduce, sort and filter on arrays in modern JS is similar to SQL maybe that, but then again, what we do with custom data structures that are required for certain things to be halfway efficient, is much harder or impossible to do in SQL, correct?
- jokoon 8y agoSQL feels opaque, and its syntax is one of a particular kind (can't remember how it's called), which makes it difficult to learn and understand how it's parsed, because it's not very well compartmentalized. If the language was better structured like other programming languages are, that'd be better, and jooq looks like something that should be standard across all languages. Usually when I'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 "algorithms" really are after all. I heard that databases engines use backtracking, but I'm not really sure that's what it is talked about here. Maybe databases don't use BT? Using another language that parses every time you do something doesn't feel very fast. It is fast for many applications, but I don't think it is for all of them. In the end, it's the same old combat, either choose peak performance or development delays with good enough performance. 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.
- dahfizz 8y agoI'm not sure that parsing is going to be a bottleneck when you consider that DBMS read from the disk.
- thesz 8y agoPrepared statements would not have been invented if parsing and checking was not a significant issue.
- jhayward 8y agoThe main point of prepared statements is that expensively optimized query execution plans can be re-used, including incorporating things learned from data patterns by previous queries. I've never considered them to be a crutch on parsing.
- bladelessninja 8y agoI have strong .net background with mostly pretty legacy code and I'm amazed that this talk is so "wow" for some people. Till this point I wasn't aware people has such problems like described in this talk. Maybe I'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 "how I would just loop through all this data an calculate this".
- mnazim 8y agoA very good way to understand the fact "class are for encapsulating behaviour, not data". But we don't learn until we fall.
- jayrwren 8y agoreminds me of the presentations coming out of the .NET world in 2005 after they launched LINQ. Nearly identical points being made.
- jayd16 8y agoI 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. That said, SQL doesn't lend itself to easy composability and you can end up with "4000 line monsters" if you really tried to put as much business logic into the query as possible. 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?
- spullara 8y agoNot designed for developers but sigmacomputing.com let's you write 4000 lines of SQL using a purely visual intetface. I have built queries with it and then used them in code though.
- btschaegg 8y ago> [...] and you can end up with "4000 line monsters" if you really tried to put as much business logic into the query as possible. In my experience so far, this has usually been the actual problem. If you are not actively trying to "cleanly" separate your business logic from your storage, you tend to end up maintaining a mess. People correctly identify the problems with the mess, but incorrectly attribute it to SQL instead of architectural problems. So they switch to an ORM and wonder why it doesn't improve anything a little while later. And that's not to say it is easy to come up with a useful way to interface between storage and the business logic -- on the contrary. It's just that the naive approach (strongly coupling the two everywhere) is kind of a worst-case scenario. Also, there are certainly situations where ORMs are very useful; it's just that a messily integrated ORM isn't any better than messily integrated SQL. The thing is: Your business logic needs to be provided with ways of triggering certain operations (along with certain guarantees). I haven't seen cases where a single operation would take 4000 lines of "interesting" SQL though (ignoring very long lists of columns :) ). So, if the SQL is hard to maintain: Is this a problem of the SQL, or is this a byproduct of unclear (or ever changing) interface requirements? Sometimes, taking a couple of steps back and considering alternative approaches can yield much better results than optimizing a "wrong" solution. (I guess this could be considered a form of the "X Y Problem": http://xyproblem.info/ http://xyproblem.info/).
- RandyRanderson 8y agoI'll save ppl some time: * first 20 mins: Writing a contrived report query that's easy in SQL is easy in SQL. * but: most real 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. :< * you often can't do some of what the author suggests bc in a "real" org you have db zealots that only allow access to their precious through stored procs and a surprisingly large number of meetings * trying to do some non-trivial where clauses? Not in SQL - so you have to use an external lang. I call this "Works in my presentation" syndrome. * he acts surprised that a lang that uses a db through a SQL api can't be as fast/efficient/terse as a system that uses SQL and has direct access to the db/storage. Stopped after that. PS Seems like a funny guy tho - liked his style! :>
- lukaseder 8y ago"Funny guy" here. Can't comment on db zealots. It must suck when that happens. But disagree entirely on your other parts. Haven't run into such cases yet, wrote all my reports in SQL (and something like XSLT for presentation logic). I've mainly used Oracle.
- ipsocannibal 8y agoOne 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'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.
- tatersolid 8y agoPure ANSI SQL gets you 90% of what most apps need and is portable between MSSQL and Postgres. MySQL/MariaDB are far less ANSI-SQL-compliant, but usually only function calls change when porting. Nobody sane uses oracle for new development. If you chose to code your app to MongoDB or Dynamo or whatever you are 100% locked in unless you do a rewrite of your whole data access layer. To top it off, in the real world you’ll still need a separate SQL DB for reporting and analytics.