Are the posted questions getting worse?
-
Jack Corbett (1/26/2015)
Grant Fritchey (1/26/2015)
What it's like, at this exact moment, everywhere in Massachusetts.That's awesome. Of course, last night I told my wife, "make sure you fill the gas tank and get bread and milk tomorrow".
*Gets home, finds the gas tank full of bread and milk*
Don't forget eggs or you can't have French Toast!
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams -
My coworker just sent me this:
“I woke up SO this morning telling them that we're getting a blizzard tonight. They said, half-asleep, “from Dairy Queen?” Yes.”
-
Sean Lange (1/26/2015)
Ed Wagner (1/26/2015)
TomThomson (1/26/2015)
SQLRNNR (1/26/2015)
Grant Fritchey (1/24/2015)
Sean Lange (1/23/2015)
Oh lordy...I just handed a stored proc and was asked advice on how to make it faster. The proc is 3700 lines long, there are almost 200 parameters and another 200 variables. I copied the sql to a text file and there are 113 while loops. Hope they don't want this done quickly as a complete rewrite is in my near future.Can't you just put an index on it?
<ducking & running>
Or partition it? :hehe:
Do both, surely, and also change the performance by adding an IGNORE_CONSTRAINTS table hint on all tables being updated in any way. :w00t: Perhaps this could be combined with READPAST and NOLOCK hints wherever approriate. :sick:
Be sure to partition it on the column that changes most frequently. THEN apply NOLOCK everywhere you have a cursor.
You know, you might be able to nest some of those cursors. :hehe:
Oh the cursors are unbelievably stuipd. They are used to count!!!
set @Counter = 1
while exists(select * from StupidlyLargeHeap (nolock) where someColume = @Counter) begin
set @Counter = @Counter + 1
end
Then at the very end this proc sends a database mail. Gee I can't imagine why it is slow. :w00t:
You're going to be the hero of the day when you're done with this one. Just think - a monster stored procedure that takes hours to run reduced to seconds. Never mind that the complete wrong way was used for everything; the end result is that it's going to be blazin' fast. If it were me, I would feel obligated to tell them that it wasn't that big of deal and cite a few reasons. You'll still end up a hero. 😉
Please tell me that after using a cursor to replace a SELECT COUNT that they at least did something with the count.
-
... Mark one off, 32 days on the calendar to go. 32 days on the calendar to go, 32 days to go, ...
-
Sean Lange (1/23/2015)
Oh the cursors are unbelievably stuipd. They are used to count!!!
set @Counter = 1
while exists(select * from StupidlyLargeHeap (nolock) where someColume = @Counter) begin
set @Counter = @Counter + 1
end
It must be a way of thinking - I worked at a place where very similair code was used to make a count of certain rows, and then the count was merely checked for > 0. Replaced it with EXISTS. And the really sad thing was the Developer who wrote it had said I'd never find a way to optimise the code.
-
BrainDonor (1/27/2015)
Sean Lange (1/23/2015)
Oh the cursors are unbelievably stuipd. They are used to count!!!
set @Counter = 1
while exists(select * from StupidlyLargeHeap (nolock) where someColume = @Counter) begin
set @Counter = @Counter + 1
end
It must be a way of thinking - I worked at a place where very similair code was used to make a count of certain rows, and then the count was merely checked for > 0. Replaced it with EXISTS. And the really sad thing was the Developer who wrote it had said I'd never find a way to optimise the code.
That's not actually a counter. I know it's named that way. But it's actually looking for the first gap in a number sequence (yes, there is a MUCH easier way)
maybe something like...
SET @Counter = ISNULL((select TOP(1) slh1.someColume + 1 from StupidlyLargeHeap slh1 (nolock) LEFT OUTER JOIN StupidlyLargeHeap slh2 (nolock) ON slh1.someColume + 1 = slh2.someColume
WHERE slh2.ID is Null), 0)
-
venoym (1/27/2015)
BrainDonor (1/27/2015)
Sean Lange (1/23/2015)
Oh the cursors are unbelievably stuipd. They are used to count!!!
set @Counter = 1
while exists(select * from StupidlyLargeHeap (nolock) where someColume = @Counter) begin
set @Counter = @Counter + 1
end
It must be a way of thinking - I worked at a place where very similair code was used to make a count of certain rows, and then the count was merely checked for > 0. Replaced it with EXISTS. And the really sad thing was the Developer who wrote it had said I'd never find a way to optimise the code.
That's not actually a counter. I know it's named that way. But it's actually looking for the first gap in a number sequence (yes, there is a MUCH easier way)
maybe something like...
SET @Counter = ISNULL((select TOP(1) slh1.someColume + 1
from StupidlyLargeHeap slh1 (nolock)
LEFT OUTER JOIN StupidlyLargeHeap slh2 (nolock)
ON slh1.someColume + 1 = slh2.someColume
WHERE slh2.ID is Null), 0)
Hmmm. Venoym , that actually doesn't work. I tested it with a table I have that actually has an identity property (but has had values removed).
The WHILE loop gave me a value of 10. I reset the counter and tried it with your code and came up with 13392707. I checked the table identities and verified that 10 is indeed the first "gap" in the sequence.
EDIT: WOW. Running your code a second and a third time gave me even different values from 13392707. And I verified that I reset the counter to 1 each time with a SELECT statement. Of course, I did change the WHERE clause, so I need to fiddle a little more and see if that's the issue.
-
Brandie Tarvin (1/27/2015)
venoym (1/27/2015)
BrainDonor (1/27/2015)
Sean Lange (1/23/2015)
Oh the cursors are unbelievably stuipd. They are used to count!!!
set @Counter = 1
while exists(select * from StupidlyLargeHeap (nolock) where someColume = @Counter) begin
set @Counter = @Counter + 1
end
It must be a way of thinking - I worked at a place where very similair code was used to make a count of certain rows, and then the count was merely checked for > 0. Replaced it with EXISTS. And the really sad thing was the Developer who wrote it had said I'd never find a way to optimise the code.
That's not actually a counter. I know it's named that way. But it's actually looking for the first gap in a number sequence (yes, there is a MUCH easier way)
maybe something like...
SET @Counter = ISNULL((select TOP(1) slh1.someColume + 1
from StupidlyLargeHeap slh1 (nolock)
LEFT OUTER JOIN StupidlyLargeHeap slh2 (nolock)
ON slh1.someColume + 1 = slh2.someColume
WHERE slh2.ID is Null), 0)
Hmmm. Venoym , that actually doesn't work. I tested it with a table I have that actually has an identity property (but has had values removed).
The WHILE loop gave me a value of 10. I reset the counter and tried it with your code and came up with 13392707. I checked the table identities and verified that 10 is indeed the first "gap" in the sequence.
EDIT: WOW. Running your code a second and a third time gave me even different values from 13392707. And I verified that I reset the counter to 1 each time with a SELECT statement. Of course, I did change the WHERE clause, so I need to fiddle a little more and see if that's the issue.
HA! I just figured another use for the Tally table!!!!
SELECT t.n
FROM StupidlyLargeHeap slh
RIGHT OUTER JOIN Tally t
ON slh.ID = t.n
WHERE slh.ID IS NULL
ORDER BY t.n
Set-based delivery of ALL missing numbers. And of course, it can be changed to "Top 1" to set a variable, if you so wish.
Excuse me while I stand over in my corner gloating that I figured something out all by my lonesome without looking anything up. YES!
-
Oh, hey! Let's all go to Russia in the middle of a war!
Dear SQLSaturday Speakers,
This is a one-time system message for SQLSaturday #398. SQLSaturday #398 is coming to you on Jun 06, 2015 at Kommunarov st, 268?, Krasnodar, Russia . You are getting this email because you have spoken at previous SQLSaturday events. Please consider speaking again at SQLSaturday #398. You can submit your session by using the call for speakers page.
We look forward to hopefully seeing you as a speaker for SQLSaturday #398. If you have any questions feel free to contact the Event Admins at sqlsaturday398@sqlsaturday.com
Warm Regards,
Team SQLSaturday
Because... FUN.
(Sorry, I'm a little cynical of these SQL Saturdays that are popping up in hot zones).
-
Brandie Tarvin (1/27/2015)
Oh, hey! Let's all go to Russia in the middle of a war!Dear SQLSaturday Speakers,
This is a one-time system message for SQLSaturday #398. SQLSaturday #398 is coming to you on Jun 06, 2015 at Kommunarov st, 268?, Krasnodar, Russia . You are getting this email because you have spoken at previous SQLSaturday events. Please consider speaking again at SQLSaturday #398. You can submit your session by using the call for speakers page.
We look forward to hopefully seeing you as a speaker for SQLSaturday #398. If you have any questions feel free to contact the Event Admins at sqlsaturday398@sqlsaturday.com
Warm Regards,
Team SQLSaturday
Because... FUN.
(Sorry, I'm a little cynical of these SQL Saturdays that are popping up in hot zones).
Do I get a Kalishnikov?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Brandie Tarvin (1/27/2015)
Oh, hey! Let's all go to Russia in the middle of a war!Because... FUN.
(Sorry, I'm a little cynical of these SQL Saturdays that are popping up in hot zones).
At least it should be warm there.
I'll get my coat.
-
Brandie Tarvin (1/27/2015)
Oh, hey! Let's all go to Russia in the middle of a war!Dear SQLSaturday Speakers,
This is a one-time system message for SQLSaturday #398. SQLSaturday #398 is coming to you on Jun 06, 2015 at Kommunarov st, 268?, Krasnodar, Russia . You are getting this email because you have spoken at previous SQLSaturday events. Please consider speaking again at SQLSaturday #398. You can submit your session by using the call for speakers page.
We look forward to hopefully seeing you as a speaker for SQLSaturday #398. If you have any questions feel free to contact the Event Admins at sqlsaturday398@sqlsaturday.com
Warm Regards,
Team SQLSaturday
Because... FUN.
(Sorry, I'm a little cynical of these SQL Saturdays that are popping up in hot zones).
There hasn't been a SQL Saturday here in Afghanistan yet. Not looking to have one before I leave either.
-
Brandie Tarvin (1/27/2015)
Excuse me while I stand over in my corner gloating that I figured something out all by my lonesome without looking anything up. YES!Congrats Brandie. That sure is a great feeling.
BTW, are you using SQL 2012? If so, check out the LEAD function for getting the gap even easier.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Lynn Pettis (1/27/2015)
Brandie Tarvin (1/27/2015)
Oh, hey! Let's all go to Russia in the middle of a war!Dear SQLSaturday Speakers,
This is a one-time system message for SQLSaturday #398. SQLSaturday #398 is coming to you on Jun 06, 2015 at Kommunarov st, 268?, Krasnodar, Russia . You are getting this email because you have spoken at previous SQLSaturday events. Please consider speaking again at SQLSaturday #398. You can submit your session by using the call for speakers page.
We look forward to hopefully seeing you as a speaker for SQLSaturday #398. If you have any questions feel free to contact the Event Admins at sqlsaturday398@sqlsaturday.com
Warm Regards,
Team SQLSaturday
Because... FUN.
(Sorry, I'm a little cynical of these SQL Saturdays that are popping up in hot zones).
There hasn't been a SQL Saturday here in Afghanistan yet. Not looking to have one before I leave either.
Ah, come on Lynn. Just chat with Karla - she'll let you do it. :w00t::w00t:
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
WayneS (1/27/2015)
Brandie Tarvin (1/27/2015)
Excuse me while I stand over in my corner gloating that I figured something out all by my lonesome without looking anything up. YES!Congrats Brandie. That sure is a great feeling.
BTW, are you using SQL 2012? If so, check out the LEAD function for getting the gap even easier.
We are about to move to SQL 2012, so I will check out the LEAD function. Thanks, Wayne.
Viewing 15 posts - 47,161 through 47,175 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply