Modern SQL

https://winand.at/sql-slides-for-developers

by gilad • 5 years ago

83 15 5 years ago

winand.at

xdfgh1112• 5 years ago

This seems out of date (2015?) Unless I am missing something.

franole• 5 years ago

Probably.

In Postgres, WITH queries are not "optimizer fences" since version 12 [0].

But I discovered many other usefull features/tips.

[0] https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...

gordon_freeman• 5 years ago

If anyone is interested, Winand's book "SQL Performance Explained" [1] really goes deep into understanding and optimizing SQL queries and improving performance of SQL queries.

[1] https://sql-performance-explained.com/?utm_source=winand.at&...

krylon• 5 years ago

Unfortunately, I have forgotten the details, but a couple of years ago, I was writing a rather complex SQL query for my boss to extract some information from our ERP database the ERP software itself was incapable of providing (it was a kind of crappy ERP system, I suspect, not being an expert).

After much head-scratching, coffee-drinking and intense screen-staring, I came up with a query that gave the desired results, but took about an hour to complete.

I have only run into SQL performance problems very rarely, but this was an obvious stinker, so I dove in, using MS SQL Server Management Studio's query explainer to figure out what was going wrong. To my mild surprise and great relief, I did find the solution, I only recall it had something to do with JOINs (HASH JOIN? Is that a thing?). The query went from ~60 minutes to less than a second.

So I learned that SQL performance tuning is not black magic, although it's not trivial either. Fortunately, most of the time, my SQL queries run sufficiently fast it's not worth the effort to make 'em faster.

But damn, going from ~60 minutes to less than a second with just a relatively minor change in the query was probably the most effective optimization I ever did. :-)

throwaway81523• 5 years ago

I have that book (SQL Performance Explained) and benefited from it and think it was worth it, but I'd also like something more advanced. If he has another book out, that's great, I'm ready.

Added: But, it looks like the link for this thread is just a slide deck. Oh well.

Eikon2• 5 years ago

I really don't like this whole "OFFSET" kills performance thing that you see everywhere because it's only relevant for huge sets, think millions of rows.

And then, there's a reason why people are using offsets, cursors are usually not a good alternative and the after_id pattern is not flexible and don't allow to go to a random page number...

But still, some people are preaching for alternatives that are not actually solving the same problem as offsets.

simonw4• 5 years ago

Millions of rows isn't a lot of data though - I've seen plenty of systems that implemented pagination with OFFSET and later regretted it.

Eikon2• 5 years ago

By what measure "millions" is not big? We are talking about pagination there.

And even then you just scale up your database server to have performant disks and implement caching.

You'll be just fine going through even tens of millions of rows.

treve• 5 years ago

Is all the javascript broken? I can't move to the next slide, but the premise seems interesting.

simonw4• 5 years ago

Markus's site https://modern-sql.com/ is fantastic, I've learned a ton of useful tricks from it.

simonw4• 5 years ago

I recently found out that SQLite added support for FILTER aggregate clauses in version 3.30.0, released 2019-10-04: https://til.simonwillison.net/sqlite/sqlite-aggregate-filter...

MarkusWinand• 5 years ago

Includes an updates version of the first slideset: https://modern-sql.com/slides/ModernSQL-2019-05-30.pdf

bradford• 5 years ago

Great post: I found the problem/solution approach super helpful. I've used some of the more recent features, but have missed grouping sets, recursive CTEs, and MATCH_RECOGNIZE. hope to put these in application someday.

Modern SQL

Related Stories