1st New Job Win
-
Nope. We simply stopped pulling inactive records. Honestly, this reduced the number of records we were pulling from 800k to 500k, and I expected the data pull to only take 4.5 hours, but for the last two days, it's only been 20 minutes, and we even verified that we did indeed pull all 500k records. I suspect that the data owners also updated their queries that give us the data, but they claim they didn't. It's weird, but I'll take the win regardless.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
Nope. We simply stopped pulling inactive records. Honestly, this reduced the number of records we were pulling from 800k to 500k, and I expected the data pull to only take 4.5 hours, but for the last two days, it's only been 20 minutes, and we even verified that we did indeed pull all 500k records. I suspect that the data owners also updated their queries that give us the data, but they claim they didn't. It's weird, but I'll take the win regardless.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013Hmm, I might be tempted to check those 300K records you are no longer accessing to see if there is any null data/duplicates or any other strange data that could be causing SQL to chug along.
-
Everyone seems happy (on the outside, at least). It was an onerous operation that we had taken steps to do once a week until someone complained that they needed the data refreshed every day. Me and a DBA sat down and started looking hard at what was going on, and that's when I noticed that we were pulling almost twice as many records as we needed to. I'm a hero today, but tomorrow, I'll revert back to being the FNG. Glory only lasts until the next bug surfaces. :)
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
Nope. We simply stopped pulling inactive records. Honestly, this reduced the number of records we were pulling from 800k to 500k, and I expected the data pull to only take 4.5 hours, but for the last two days, it's only been 20 minutes, and we even verified that we did indeed pull all 500k records. I suspect that the data owners also updated their queries that give us the data, but they claim they didn't. It's weird, but I'll take the win regardless.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
We have a weekly report that takes 15 minutes... been running it for a while now... then the other day, suddenly, without any changes to the query, it starts consistently taking 2 seconds. Sometimes magic just happens! Although we did also get rid of Kaspersky recently, coincidence?
musefan wrote:
get rid of Kaspersky
:thumbsup: Never trust the Russians for security software. :-\ At my old job we used Kaspersky and I had to kill it all the time because it caused my machine to be incredibly slow.
Everyone is born right handed. Only the strongest overcome it. Fight for left-handed rights and hand equality.
-
Wonder if indices were refreshed. That has helped a bunch of our queries.
I could ask the dba.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
Everyone seems happy (on the outside, at least). It was an onerous operation that we had taken steps to do once a week until someone complained that they needed the data refreshed every day. Me and a DBA sat down and started looking hard at what was going on, and that's when I noticed that we were pulling almost twice as many records as we needed to. I'm a hero today, but tomorrow, I'll revert back to being the FNG. Glory only lasts until the next bug surfaces. :)
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013I once converted a serial string of jobs that took 14.5 hours end to end on average (and was run once per day) into a single job that took 40-45 minutes. Day 1: Party! Congratulations and back-slapping all around. :-D Day 2: "Can we run this multiple times per day?" :doh: Day 3: Set it up to run hourly so data was very current. :) Day 4: The users complained that the data was often up to an hour out-of-date - despite having dealt with day-old data for several years prior. :omg: Day 5: Took all the users out the back and shot them. Hired new users. (In my mind.) :mad: Day 6: Delegated project to new guy. :cool:
- I would love to change the world, but they won’t give me the source code.
-
Reduced a 7.5 hour data pull/import SQL job from 7.5 hours to 20 minutes.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013A million years ago when I was an IT guy and DB admin we used a 4GL DB called Progress for our MRP system. I had a custom accounts payable report that ran daily (sometimes 2x a day) that took nearly an hour. I went to the annual user's conference and attended a session held by a Progress guru. I learned a crazy 4GL Progress trick that got the report to run in... ...wait for it... 12 seconds. :doh:
-
I once converted a serial string of jobs that took 14.5 hours end to end on average (and was run once per day) into a single job that took 40-45 minutes. Day 1: Party! Congratulations and back-slapping all around. :-D Day 2: "Can we run this multiple times per day?" :doh: Day 3: Set it up to run hourly so data was very current. :) Day 4: The users complained that the data was often up to an hour out-of-date - despite having dealt with day-old data for several years prior. :omg: Day 5: Took all the users out the back and shot them. Hired new users. (In my mind.) :mad: Day 6: Delegated project to new guy. :cool:
- I would love to change the world, but they won’t give me the source code.
:thumbsup: Great story! ..and one that I am currently dealing with going from a nightly scheduled task to a filesystemwatcher solution...which is working fine, but now I've got to handle the new problem of what to show the user in that 5 minute window where their datafiles are being imported/processed. :sigh: It's a certainty that they will expect it immediately and I'd rather deny access/warn than show incomplete data. I'm thinking a quick redirect to a page titled 'Impatient User Warning' should do the trick! :laugh: Can I just skip to Day 5?
"Go forth into the source" - Neal Morse
-
Reduced a 7.5 hour data pull/import SQL job from 7.5 hours to 20 minutes.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
