Are the posted questions getting worse?
-
GilaMonster (1/26/2015)
Sean Lange (1/26/2015)
Jack Corbett (1/26/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.I can almost understand 3700 lines, but 113 loops! Wow. I'm sure you'll be able to get it to run much faster, but I'd need a couple of days just to figure out what it is doing. You don't happen to have access to a spec for the original development.
BWAAHAA!!!! Spec docs???
What's a spec?
I know I've heard people talk about them, can't recall ever seeing one. Must be some mythical thing like a unicorn.
It shares a flat with the documentation. 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (1/26/2015)
GilaMonster (1/26/2015)
Sean Lange (1/26/2015)
Jack Corbett (1/26/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.I can almost understand 3700 lines, but 113 loops! Wow. I'm sure you'll be able to get it to run much faster, but I'd need a couple of days just to figure out what it is doing. You don't happen to have access to a spec for the original development.
BWAAHAA!!!! Spec docs???
What's a spec?
I know I've heard people talk about them, can't recall ever seeing one. Must be some mythical thing like a unicorn.
It shares a flat with the documentation. 😀
I thought it was the basement, a dark dank place where nobody goes :w00t:
Far away is close at hand in the images of elsewhere.
Anon. -
HELP! I'm having a "restoring database from a NAS share" issue and the problem appears to be the server, not the NAS / network. Can someone stop by and give me some thoughts?
-
David Burrows (1/26/2015)
Koen Verbeeck (1/26/2015)
GilaMonster (1/26/2015)
Sean Lange (1/26/2015)
Jack Corbett (1/26/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.I can almost understand 3700 lines, but 113 loops! Wow. I'm sure you'll be able to get it to run much faster, but I'd need a couple of days just to figure out what it is doing. You don't happen to have access to a spec for the original development.
BWAAHAA!!!! Spec docs???
What's a spec?
I know I've heard people talk about them, can't recall ever seeing one. Must be some mythical thing like a unicorn.
It shares a flat with the documentation. 😀
I thought it was the basement, a dark dank place where nobody goes :w00t:
There's a reason it's called a "bomb shelter".
-
Sean Lange (1/23/2015)
Jeff Moden (1/23/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.I guess I would ask them what the original requirements are for the code and just start over.
Jeff please pack up your pork chop launcher and head to Kansas City. I am going to need backup on this one. Every single one of those while loops does nothing but increment a counter. All they need to be is a simple count(*) + 1 instead of looping.
And did I mention this procedure repeatedly hits a
tableheap with 27 million rows with the exact same query. :w00t::w00t::w00t:LMBO
:w00t:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
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:
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
David Burrows (1/26/2015)
Koen Verbeeck (1/26/2015)
GilaMonster (1/26/2015)
Sean Lange (1/26/2015)
Jack Corbett (1/26/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.I can almost understand 3700 lines, but 113 loops! Wow. I'm sure you'll be able to get it to run much faster, but I'd need a couple of days just to figure out what it is doing. You don't happen to have access to a spec for the original development.
BWAAHAA!!!! Spec docs???
What's a spec?
I know I've heard people talk about them, can't recall ever seeing one. Must be some mythical thing like a unicorn.
It shares a flat with the documentation. 😀
I thought it was the basement, a dark dank place where nobody goes :w00t:
Something something, a sign saying 'Beware of the Leopard' 😀
-
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:
Tom
-
What it's like, at this exact moment, everywhere in Massachusetts.
"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 -
Grant Fritchey (1/26/2015)
What it's like, at this exact moment, everywhere in Massachusetts.Summer, glorious summer.
Might swing past my parent's place tomorrow for a swim, it's going to be a hot evening.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
... Mark one off, 33 days on the calendar to go. 33 days on the calendar to go, 33 days to go, ...
-
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:
-
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:
_______________________________________________________________
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/ -
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".
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Grant Fritchey (1/26/2015)
What it's like, at this exact moment, everywhere in Massachusetts.BWahahahahaaha.
Viewing 15 posts - 47,146 through 47,160 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply