Watch Brent Tune Queries at SQLSaturday Oslo

This morning, I had the honor of presenting virtually at SQLSaturday Oslo. The session title, Watch Brent Tune Queries, explains it all. Enjoy!

In the session, I used the 2018-06 Stack Overflow database, but any recent full-sized copy (not the 2010-2013 copies) will work. Here’s the query I worked with:

And for more sessions like this, check out the Watch Brent Tune Queries series page.

Previous Post
3 Ways to Run DBCC CHECKDB Faster
Next Post
SQL ConstantCare® Population Report: Summer 2020

6 Comments. Leave new

  • what happen at the end?
    how can I watch the rest?

    Reply
    • I’m not sure what you mean “the rest” – it was a one-hour session, and you see the whole thing up there in the video.

      Reply
      • I was expected to see you tune the query and it runs very fast
        I understand the concept of B.E. C.R.E.E.P.Y. but willing to see final optimization on this specific query.
        P.S. you are exceptional presenter 🙂 but I dont like new sound effects 🙂 it was hard to hear your voice when sound effect is playing

        Reply
  • Wojciech Sawicki
    August 31, 2020 5:18 am

    I have some doubts.
    select Id from Users u where u.DisplayName=’Jon Skeet’
    gives 22656

    SELECT SUM(p.Score) AS PostsScore
    FROM dbo.Posts p where p.OwnerUserId =22656 and
    p.CreationDate BETWEEN ‘20110901’ AND ‘20111001’
    gives 7947
    But result
    EXEC rpt_TopUsers_ByLocation
    @Location = N’Reading, United Kingdom’,
    @StartDate = ‘2011-09-01’, @EndDate = ‘2011-10-01’
    GO
    is
    Reputation DisplayName AboutMe PostsScore CommentsScore
    1047863 Jon Skeet … 9687393 677784

    A little more.
    Why?

    SELECT count(*)
    FROM dbo.Users u
    LEFT OUTER JOIN dbo.Posts p ON u.Id = p.OwnerUserId AND p.CreationDate BETWEEN ‘20110901’ AND ‘20111001’
    — LEFT OUTER JOIN dbo.Comments c ON u.Id = c.UserId AND c.CreationDate BETWEEN ‘20110901’ AND ‘20111001’
    WHERE u.Id = 22656

    =744

    SELECT count(*)
    FROM dbo.Users u
    — LEFT OUTER JOIN dbo.Posts p ON u.Id = p.OwnerUserId AND p.CreationDate BETWEEN ‘20110901’ AND ‘20111001’
    LEFT OUTER JOIN dbo.Comments c ON u.Id = c.UserId AND c.CreationDate BETWEEN ‘20110901’ AND ‘20111001’
    WHERE u.Id = 22656
    =1219

    SELECT count(*)
    FROM dbo.Users u
    LEFT OUTER JOIN dbo.Posts p ON u.Id = p.OwnerUserId AND p.CreationDate BETWEEN ‘20110901’ AND ‘20111001’
    LEFT OUTER JOIN dbo.Comments c ON u.Id = c.UserId AND c.CreationDate BETWEEN ‘20110901’ AND ‘20111001’
    WHERE u.Id = 22656
    =906936 (1219*744)

    Join Users Posts gives not unique u.Id and for each row we join dbo.Comments

    if we change logic:
    join Users Posts sum PostsScore get top 1000 and result (each row have unique Id) join with Comments
    It works much faster 🙂

    Reply
    • Yep, very good! That’s one of the goals of webcasts like this: to get you to download the Stack Overflow database, use these queries for practice, and learn to do performance tuning yourself. Good job!

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}