There are very few people who could make query tuning enjoyable and entertaining... you, my friend, are one of them! Thank you so much for the information and providing it in an easily memorable way.
You are really good presenter and explain clearly what you want us to understand. Well done, I appreciate your effort and please keep doing. Especially advanced topics: optimization, sharding, indexes and etc. thank you
Thank you very much! You'll enjoy my training classes where I cover those advanced topics. Go to BrentOzar.com and click Training at the top of the page.
I think the query was designed to be horrible. There are lots of solutions though, my attempt is below. CREATE OR ALTER PROC dbo.rpt_TopUsers_ByLocation @Location NVARCHAR(100), @StartDate DATE, @EndDate DATE AS BEGIN create table #Score (UserID int primary key clustered, PostScore int ) insert into #Score(UserID, PostScore) SELECT u.Id, SUM(isnull(p.Score, 0)) AS PostsScore FROM dbo.Users u LEFT OUTER JOIN dbo.Posts p ON u.Id = p.OwnerUserId AND p.CreationDate BETWEEN @StartDate AND @EndDate WHERE u.Location = @Location GROUP BY u.Id select u.Id, u.DisplayName, u.AboutMe, s.PostScore, SUM(c.Score) AS CommentsScore from dbo.Users u join #Score s on u.Id = s.UserID left join dbo.Comments c ON u.Id = c.UserId AND c.CreationDate BETWEEN @StartDate AND @EndDate group by u.Id, u.DisplayName, u.AboutMe, s.PostScore order by s.PostScore desc END GO
I'll rephrase your question and see if I understand right: "If I have a slow query, wouldn't it be better to add new tables with the results, keep the results up to date, and then query those new tables instead?"
There are very few people who could make query tuning enjoyable and entertaining... you, my friend, are one of them! Thank you so much for the information and providing it in an easily memorable way.
Wow, thank you!
So enjoyable to watch omg I was tuned in for the entire video understanding everything 100%. You have a wonderful gift
Quickest video I’ve watched in one hour ?! Can’t believe how much I enjoyed this
Very good video, I love it presenters say that they are terrible at presenting (me being one of them) and they make the best presentations 😆
Thanks for that!
You are really good presenter and explain clearly what you want us to understand. Well done, I appreciate your effort and please keep doing. Especially advanced topics: optimization, sharding, indexes and etc. thank you
Thank you very much! You'll enjoy my training classes where I cover those advanced topics. Go to BrentOzar.com and click Training at the top of the page.
Really nice rep I did not feel boring thank you so much and hope to see new videos
Sure, check out my channel. I have several new videos a week. Cheers!
So good I watched it twice.
Very helpful video, though the sound effects and talk of weed over my speakers at work was less than professional..lol
Goodness! Best to stay clear of my other videos, then. I'm not boring, for sure, heh.
You’ve got a great Liam Neeson impression going!
Thank you for this
I'm being destroyed by an app at work and this has given me a lot of things to try
Did you get it sorted
You’re great…Learned a lot today!
The great video sir , thank you
You are welcome
Super helpful, thank you!
Thank you again, that was wonderful to watch!! BTW, can we get the SP to complete in less than 29 seconds on this hardware??
Check out my Mastering Query Tuning class where you can learn techniques to help.
Soooo what was the right answer?:)))) option recompile? so at least for other locations it would work fast?
Excellent
"I have 5 fingers on this hand and 5 fingers on that hand" - that's suprising)
Please review your procedure, many thanks.
Thanks!
You are lovely :)
Skeet Skeet...im sorry i couldnt help myself :P
I love you man.
Hahaha, thanks!
soooo are we unable to do anything to statistics to improve the estimates?
We have a whole class on that! Check out the free Statistics class on our RUclips channel.
That clippy voice... 😂😂
So no solution!....emmmm O
I think the query was designed to be horrible. There are lots of solutions though, my attempt is below.
CREATE OR ALTER PROC dbo.rpt_TopUsers_ByLocation
@Location NVARCHAR(100),
@StartDate DATE,
@EndDate DATE
AS
BEGIN
create table #Score (UserID int primary key clustered, PostScore int )
insert into #Score(UserID, PostScore)
SELECT
u.Id,
SUM(isnull(p.Score, 0)) AS PostsScore
FROM dbo.Users u
LEFT OUTER JOIN dbo.Posts p ON u.Id = p.OwnerUserId AND p.CreationDate BETWEEN @StartDate AND @EndDate
WHERE
u.Location = @Location
GROUP BY
u.Id
select
u.Id,
u.DisplayName,
u.AboutMe,
s.PostScore,
SUM(c.Score) AS CommentsScore
from
dbo.Users u
join #Score s on u.Id = s.UserID
left join dbo.Comments c ON u.Id = c.UserId AND c.CreationDate BETWEEN @StartDate AND @EndDate
group by
u.Id,
u.DisplayName,
u.AboutMe,
s.PostScore
order by
s.PostScore desc
END
GO
wouldn't it be better to create a stats table where the counts are kept updated, instead of calculating it every time through this query?
I'll rephrase your question and see if I understand right: "If I have a slow query, wouldn't it be better to add new tables with the results, keep the results up to date, and then query those new tables instead?"
Yes. Although I am totally intrigued by this demo and would try out the same on my sandbox soon.
This video could've been 10minutes. Needs a performance optimization
By all means, feel free to release your own! I'd love to see it.
I can't remember that I have ever enjoyed any video on such critical issue
awww, thanks!