3 ms·

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

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&utm_campaign=for-developers&utm_medium=web&utm_content=sql-performance-explained https://sql-performance-explained.com/?utm_source=winand.at&...

krylon 5y 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 5y 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.

krylon 5y 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 5y 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.