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.
You couldn't get the sp to run within few seconds, left the work for your viewers to finish, and yet this was so enjoyable. Its rare someone talks of tech and keeps the humour. I did learn some of the basics of running, so that you! I'll stay tuned.
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?"
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
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!
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!
Quickest video I’ve watched in one hour ?! Can’t believe how much I enjoyed this
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!
You couldn't get the sp to run within few seconds, left the work for your viewers to finish, and yet this was so enjoyable. Its rare someone talks of tech and keeps the humour. I did learn some of the basics of running, so that you! I'll stay tuned.
So good I watched it twice.
You’re great…Learned a lot today!
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
The great video sir , thank you
You are welcome
You’ve got a great Liam Neeson impression going!
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.
Soooo what was the right answer?:)))) option recompile? so at least for other locations it would work fast?
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.
Super helpful, thank you!
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 UA-cam channel.
Please review your procedure, many thanks.
"I have 5 fingers on this hand and 5 fingers on that hand" - that's suprising)
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.
Excellent
Thanks!
Skeet Skeet...im sorry i couldnt help myself :P
I love you man.
Hahaha, thanks!
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.
That clippy voice... 😂😂
You are lovely :)
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
I can't remember that I have ever enjoyed any video on such critical issue
awww, thanks!