Over a million developers have joined DZone.
Silver Partner

The Myth of Slow SQL JOIN Operations

· Database Zone

The Database Zone is brought to you in partnership with JetBrains.   Discover how DataGrip provides a UI for operations like creating/modifying tables and managing their columns, keys and indices. 

In my recent SQL work for a large Swiss bank, I have maintained nested database view monsters whose unnested SQL code amounted up to 5,000 lines of code, joining the same table over and over again in separate subselects combined via UNION operations. This monster performed in way under 50ms, no matter how we queried it (see “10 more common mistakes” about the speed of queries). Of course, this performance was only achieved after lots of fine-tuning, load-testing and benchmarking. But it worked. Our Oracle database never let us down on these things.

Nonetheless, many SQL users think that JOIN operations are slow. Why? Perhaps because they are or used to be in MySQL? I’m currently reading this interesting book by Markus Winand. The book is called SQL Performance Explained. He’s also the author of Use-The-Index-Luke.com where you can get free insight into his book. I still recommend reading the whole book, though. Even SQL old-timers and SQL nerds like me will find one or two novel, very interesting approaches, some of which will be incorporated into jOOQ very soon!

In particular, consider this page which explains very well how Hash JOIN operations work:
http://use-the-index-luke.com/sql/join/hash-join-partial-objects

The Database Zone is brought to you in partnership with JetBrains.   Discover how DataGrip provides a UI for operations like creating/modifying tables and managing their columns, keys and indices. 

Topics:
java,sql,high-perf,tips and tricks,sql join

Published at DZone with permission of Lukas Eder , DZone MVB .

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}