quote:

---

Originally posted by Peso

Every single day

Not a day pass by before I have to rewrite a procedure because of performance problems.

The last few months, I have rewritten some procedures (which all uses cursors) so they now is much faster and uses less resources.

To one procedure I added a function and that procedure is now 3500 times faster. Before 2.25 hours, now 2 seconds.

One procedure took (yes, with cursors) about 2.5-3.0 hours to complete. Rewrote that so it now runs in under 2.25 minutes.
60 times faster.
And so on, and on, and on... I am in cursor hell right now.

I don't say cursors are all bad, but it's rare that cursors outperforms set-based logic. I posted some example just the other day with an example of WHERE CURRENT OF.

I have used a cursor-based solution twice the last five year in favor of set-based solution. Twice out of some thousands.

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by Peso

Every single day

Not a day pass by before I have to rewrite a procedure because of performance problems.

---

quote:

---

Originally posted by DavidChel

Ever take a new job and look at the code your predecessor wrote and ask, "Who wrote this crap?"

---

quote:

---

Originally posted by Transact Charlie

I probably will look back to the stuff I'm writing now and think the same

-------------
Charlie

---

quote:

---

Originally posted by Transact Charlie

quote:

---

Originally posted by DavidChel

Ever take a new job and look at the code your predecessor wrote and ask, "Who wrote this crap?"

---

And, I have thought this then realised it was code that *I* wrote about 2 years ago. Things like that make you realise how much you've learned over time. I probably will look back to the stuff I'm writing now and think the same

-------------
Charlie

---

DECLARE @Id INT
DECLARE @staffNumber VARCHAR(255)

/* MASSES OF SPAGHETTI HERE */

DECLARE zempInnerCur CURSOR FOR
SELECT
	e.[staffNumber]
FROM
	employee e
WHERE
	e.[Id] = @Id

/* MASSES OF SPAGHETTI HERE */

DECLARE empOuterCur CURSOR FOR
SELECT
	[Id]
FROM
	employee

OPEN empOuterCur

	FETCH NEXT FROM empOuterCur INTO @Id

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN

		/* MASSES OF SPAGHETTI HERE */

		OPEN zempInnerCur

		FETCH NEXT FROM zempInnerCur INTO @staffNumber

			WHILE ( @@FETCH_STATUS = 0 ) BEGIN				

				/* MASSES OF SPAGHETTI HERE */

				FETCH NEXT FROM zempInnerCur INTO @staffNumber
			END

		CLOSE zempInnerCur

		FETCH NEXT FROM empOuterCur INTO @Id

	END

CLOSE empOuterCur

DEALLOCATE empOuterCur
DEALLOCATE zempInnerCur

quote:

---

Originally posted by Lumbago

I recently had the opposite experience actually...I came across a piece of code I wrote several years back and I thought "da**, this is good shi*!!"

- Lumbago

---

quote:

---

Originally posted by Michael Valentine Jones

Some years ago I worked for a software company that purchased another software company. Almost all the stored procedures for that system were written using cursors and often multiple layers of nested cursors. It was almost impossible for me to work on this code without producing huge performance improvements. There was a weekly purchasing job that went from 14 hours to 2 minutes, a physical inventory job that went from 16 hours to 1 minute, and a month-end purchasing job that went from 35 hours to 20 seconds.
When I ran the month-end purchasing job in production for the first time it took me some time to convince the end-users that the job had actually run, and not failed. They had been so used to the month-end process being this terrible ordeal that left the system down for over a day that it was hard for them to believe the nightmare was over.

I often thought about trying to track down the consultants that wrote the original code to get a list of their former clients. I figured that I could earn an easy living coming in behind them to clean up the messes that they left behind.




CODO ERGO SUM

---