1st New Job Win
-
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 -
Let me guess. You removed three unnecessary JOINs?
- I would love to change the world, but they won’t give me the source code.
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 -
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, 2013We 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?
-
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?
We have no control over infrastructure, and anything they do on the servers or to the netowrk is some big secret. Even if they did do something, they wouldn't tell us. Beyond that, if we tell them our processes are suddenly taking a lot less time, they may take steps to "fix" it, and we'll be right back up into the hours-long stuff we were seeing last week.
".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 no control over infrastructure, and anything they do on the servers or to the netowrk is some big secret. Even if they did do something, they wouldn't tell us. Beyond that, if we tell them our processes are suddenly taking a lot less time, they may take steps to "fix" it, and we'll be right back up into the hours-long stuff we were seeing last week.
".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, 2013John Simmons / outlaw programmer wrote:
if we tell them our processes are suddenly taking a lot less time, they make take steps to "fix" it,
You do realize this is a public forum, right? :laugh:
Sent from my Amstrad PC 1640 Never throw anything away, Griff Bad command or file name. Bad, bad command! Sit! Stay! Staaaay... AntiTwitter: @DalekDave is now a follower!
-
We have no control over infrastructure, and anything they do on the servers or to the netowrk is some big secret. Even if they did do something, they wouldn't tell us. Beyond that, if we tell them our processes are suddenly taking a lot less time, they may take steps to "fix" it, and we'll be right back up into the hours-long stuff we were seeing last week.
".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, 2013John Simmons / outlaw programmer wrote:
if we tell them our processes are suddenly taking a lot less time, they make take steps to "fix" it,
well more likely they won't believe you are doing it correctly: in their minds if it takes half the time then you are doing something wrong and only looking at half the data - so not so much "fix it" - but jump through hoops to prove you are not skipping some data/steps or outright cheating. Even when presented with the facts they will still look at you with some degree of distrust (i.e. that you are making it up and even your proof is somewhat fabricated to cover the truth.) Do be careful, Saving 10% is OK, 20% yeah, 30% - possible, but just like insurance companies anything over 30% sets off the alarms. 3 hours down to 20 min, 89%, no ones going to believe that ... or trust the system anymore - any mistakes [related or not] that come up in the future they going to blame your fix ... "damn hacker knew letting him play around with out sensitive system and datas would cause problems - now the whole thing cant be trusted, wonder what else he broke, wonder how much data he lost, we probably need to call in some proper experts to take a look at the whole thing again.
Message Signature (Click to edit ->)
-
John Simmons / outlaw programmer wrote:
if we tell them our processes are suddenly taking a lot less time, they make take steps to "fix" it,
well more likely they won't believe you are doing it correctly: in their minds if it takes half the time then you are doing something wrong and only looking at half the data - so not so much "fix it" - but jump through hoops to prove you are not skipping some data/steps or outright cheating. Even when presented with the facts they will still look at you with some degree of distrust (i.e. that you are making it up and even your proof is somewhat fabricated to cover the truth.) Do be careful, Saving 10% is OK, 20% yeah, 30% - possible, but just like insurance companies anything over 30% sets off the alarms. 3 hours down to 20 min, 89%, no ones going to believe that ... or trust the system anymore - any mistakes [related or not] that come up in the future they going to blame your fix ... "damn hacker knew letting him play around with out sensitive system and datas would cause problems - now the whole thing cant be trusted, wonder what else he broke, wonder how much data he lost, we probably need to call in some proper experts to take a look at the whole thing again.
Message Signature (Click to edit ->)
Well, we're all really surprised at the outcome, and I ain't sayin' anything to anyone other than the team. We don't want the network nazis coming in and sniffing around.
".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 -
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
