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: