This is why SQL Server Central Forums rock!
-
Found out about an SQL Job the fun way of course which is to say it failed and I received an email alert. It was easy to see why the job failed as it was trying to insert a string too large for the destination table. However, that's not what caught my eye. It was the job duration which took several hours!
The code was a rat's nest but at least it had some comments including the author's name. I asked around and it turns out it wasn't even my predecessor but it was actually the 4th DBA before me. The only reason I mention this is because there's a lesson in there. 😉
I'm sure once upon a time it likely ran somewhat better when the table was much smaller but got worse over time. Yet, it remained like that for quite awhile. One of the first things I did at my current job was create a daily report of backups, jobs and data files. Unfortunately that didn't include the job duration at the time...but it does now!
Anyway, I couldn't let that job go as is, even if it only ran during early morning hours. So challenge accepted...
First thing I did was format the code to make it more legible because that was driving me crazy. Next was just breaking it down piece by piece and looking for the hotspots. I used techniques I have learned from these forums over the years in regards to performance. After everything was said and done the job duration changed from hours to seconds! The proof is in the pudding as they say. Check out the image from the job history. 😎
With that I'd like to say two things:
1. For those who are willing to learn and are new to these forums...you won't find a better resource and community online.
2. For those in this community that have been at if for a long time and helping others...thank you for making these forums what they are.
Oh and Happy Thanksgiving to my neighbors to the south!
SELECT quote FROM brain WHERE original = 1
0 rows returned -
Congrats, and glad you made such a big change and meet your challenge
-
So satisfying when you can improve things like that, well done.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Phil Parkin (11/26/2015)
So satisfying when you can improve things like that, well done.Oh man is it ever! LOL
SELECT quote FROM brain WHERE original = 1
0 rows returned -
yb751 (11/26/2015)
Phil Parkin (11/26/2015)
So satisfying when you can improve things like that, well done.Oh man is it ever! LOL
I know precisely what you mean. Taking something down to its wires and rewriting it to get a performance increase like that yields incredible amounts of job satisfaction. Very well done.
-
Absolutely awesome job, YB! Congratulations!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Nice!
Good job and congrats.
"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 -
Good job. Most of the problems here I created. :hehe:
I had a job like that once. Ran for a couple hours and when it bombed on Sunday night being mission critical, had to be run even if it meant locking production out! I was not as lucky as you thou as I only got it down to 6 minutes.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply