Join operators, part 3 - performance

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Comments posted to this topic are about the item Join operators, part 3 - performance


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Always embarrassing to see a mistake in one's own question.

    Both in the question and in the explanation I have consistently used the term "hash input" where I should have used "build input". There is no such thing as a "hash input" to a Hash match operator.

    (Luckily, this mistake did not affect the correct/incorrect answers in any way)


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Sean Lange

    SSC Guru

    Points: 286385

    Definitely a good start to the week when I get one of Hugo's questions correct and I was pretty confident about my answer. Another great question Hugo. Thanks.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Marcia J

    SSCertifiable

    Points: 5491

    Hugo, I'm wondering if there is an article you would recommend on the different operators and how they affect performance.

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    Marcia J (12/7/2015)


    Hugo, I'm wondering if there is an article you would recommend on the different operators and how they affect performance.

    Unfortunately, there is not much out there. When I prepared the content for a full-day training on this subject (that I have delivered as a precon a few times now, and hopefully will again in the future), I sometimes found some bits and pieces by googling names of individual operators, combining all I could find and trying to distinguish between fact, good speculation, bad speculation, and downright nonsense.

    I am currently converting my precon material to a Pluralsight course, but that is a slow process so I cannot point you there yet. Expect at least several months to pass before that course sees the light of day, I have to juggle time between day job, family, and several side projects of which Pluralsight is just one.

    Red Gate publishing has a book by Grant Fritchey (https://www.red-gate.com/community/books/sql-server-execution-plans-ed-2). You can download the PDF version for free, and the print copy won't cost you an arm and a leg. However, if you want to buy a physical copy, I recommend waiting for the third edition - and if you download I recommend downloading again once the third edition is out. Grant has made a lot of improvements that make the book much better than it already was, and hopefully it will get even better once I am done reviewing it (I am the tech editor for that book - one of those side projects). Do not expect an operator by operator approach in that book, that's not Grant's style. But you'll still learn a lot.

    I hope this helps aat least as a starting point.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Marcia J

    SSCertifiable

    Points: 5491

    Hugo Kornelis (12/7/2015)


    I have to juggle time between day job, family, and several side projects of which Pluralsight is just one.

    What? You mean you think you should have a life? 🙂

    Red Gate publishing has a book by Grant Fritchey (https://www.red-gate.com/community/books/sql-server-execution-plans-ed-2). You can download the PDF version for free, and the print copy won't cost you an arm and a leg. However, if you want to buy a physical copy, I recommend waiting for the third edition - and if you download I recommend downloading again once the third edition is out. Grant has made a lot of improvements that make the book much better than it already was, and hopefully it will get even better once I am done reviewing it (I am the tech editor for that book - one of those side projects). Do not expect an operator by operator approach in that book, that's not Grant's style. But you'll still learn a lot.

    Thanks for the information. I do appreciate it. Also appreciate the questions you create as well as your comments on other questions.

  • This was removed by the editor as SPAM

  • Ed Wagner

    SSC Guru

    Points: 286950

    Hugo Kornelis (12/7/2015)


    Red Gate publishing has a book by Grant Fritchey (https://www.red-gate.com/community/books/sql-server-execution-plans-ed-2). You can download the PDF version for free, and the print copy won't cost you an arm and a leg. However, if you want to buy a physical copy, I recommend waiting for the third edition - and if you download I recommend downloading again once the third edition is out. Grant has made a lot of improvements that make the book much better than it already was, and hopefully it will get even better once I am done reviewing it (I am the tech editor for that book - one of those side projects). Do not expect an operator by operator approach in that book, that's not Grant's style. But you'll still learn a lot.

    I must be getting out of date. I read the first edition. Thanks for a good question, Hugo.

  • Koen Verbeeck

    SSC Guru

    Points: 258854

    Great question Hugo, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Ken Wymore

    SSCoach

    Points: 16314

    Great question Hugo and a very nice explanation. I look forward to the third edition of Grant's book as well as that eventual Pluralsight course!

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply