u/brunocborgesOraclein r/Database • Recommends
How Modern SQL Databases Come up with Algorithms that You Would Have Never Dreamed Of
0:00 / 0:00
#sql#queryoptimizer#cost-basedoptimization
How Modern SQL Databases Come up with Algorithms that You Would Have Never Dreamed Of by Lukas Eder
Devoxx
172K subscribers • 52 videos on Hallway • Conference channel
- Recommended by 4 people: 3 on Hacker News and 1 in engineering communities.
- A classic. People kept recommending it from 2017 to 2023.
- Presented by Lukas Eder
Intermediate. Assumes working knowledge.
55K views9 years ago4 recommendations
Databases and dataSystems programmingSQLDatabase internalsPerformance and optimization
Lukas Eder explains how modern cost-based SQL query optimizers rewrite and plan complex queries to outperform hand-written retrieval algorithms.
SQL is the only ever successful, mainstream, and general-purpose 4GL (Fourth Generation Programming Language) and it is awesome! With modern cost based optimisation, relational databases like Oracle, SQL Server, PostgreSQL finally keep up to the promise of a powerful declarative programming model by adapting to ever changing productive data without performance penalties. Thousand-line-long, complex SQL statements can be run in far below a millisecond against billion-row strong tables if database developers know their ways around the SQL language - and the best news is: It's not that hard! In this talk, I'll show how the SQL database will constantly outperform any hand written data retrieval algorithm - or in other words - how SQL, being a logic language, is the best language for business logic.Why it's here
4 people recommend thisWhat people say. Recommended as an eye-opening demonstration of how smart modern SQL query engines are at optimizing execution plans.
nerdbaggyon Hacker News • Recommends
On “Medium-hard SQL interview questions”
I use to think that too. This is a good talk about how smart SQL query engines are. It is an hour though link to this video
loudlambdaon Hacker News • Recommends
On “A Hairy PostgreSQL Incident”
I would suggest that learning SQL (the syntax) isn't the crux--it's just syntax you learn over time based off need, just like learning python--what you need is motivation. I'd suggest watching this video to get a flavor of how much leverage using the database can give you: link to this video If the database can make you more productive and more efficient and make your life easier, you now have incentive to use it more. Another thing that took my use of databases to the next level, was using PostGraphile ( link ). It takes a PostgreSQL database and provides a GraphQL endpoint for it. Use that as your only backend. You can use PostgreSQL row-level security to do access control (who can see or modify wh…
lukasederon Hacker News • Recommends
On “Permazen: Language-natural persistence to KV stores”
> This may be more intuitive for some developers, especially those that don't use SQL all the time. I tend to recommend my famous talk to such developers: link to this video
jodrellblankon Hacker News • Mentions
On “Joins In Steps”
This talk ("How Modern SQL Databases Come up with Algorithms that You Would Have Never Dreamed Of by Lukas Eder" link to this video ) is not particularly gripping, but he does claim that modern database engines will ignore indexes if they estimate it will be faster to read all the data than it is to access the index first then read the relevant data. And that Oracle can have multiple execution plans and switch between them mid-query if the first one isn't working out as expected. Could this be a rare case of the mythical sufficiently smart compiler having more information at runtime than the programmer has at coding time, and that if you could hint which index to use, the trade off would be that you'd…
tafferon Hacker News • Mentions
On “Modern Data Practice and the SQL Tradition”
> further, afaik there's no assignment nor iteration in SQL That was until CTEs were introduced in SQL:1999 > it's not a programming language at all. It's a.. query language. The two are not mutually exclusive. SQL is used to tell a computer what to do, and it is very powerful at it: link to this video
lukasederon Hacker News • Mentions
On “Parsing SQL”
>Totally agree. Lukas Eder has some nice presentations about it. Thanks for the shout out! For the record, that's probably the referenced talk: link to this video