Hello All!
Just a curious question about nightly procedures and efficiency. For our organization, We have a large and HIGHLY customized procedure/nightly job that takes approximately 1 - 1.5 minutes to run every night (never more than 2 minutes). Our database is on the smaller side (relative to the Tessitura community at large), and we have basically a 4 hour window from about 2:00 AM - 6:00 AM where we can pretty much guarantee no transactions are taking place on our website, even during our busiest days of the year, and I did an assessment of sales and website activity in our database just to verify that. If anything, I could probably push that window wider if I needed to do so. But point is, it can be like that out here in Indiana; people sleep during the night time. So naturally, I schedule all of our nightly jobs in that window, and I have more than plenty of window remaining even with all the jobs I have in place.
This procedure was half-written by me, updating a bunch of stuff that was already in there prior to my taking over the database. There is a sizable chunk in there that is written with what is probably some reasonably inefficient SQL that nevertheless returns the correct results. Now, the perfectionist in me thinks "I should rewrite that so I can potentially save up to 30 seconds of runtime AND get rid of inefficient code". On the other hand, that would be a decent chunk of work, and it is currently impacting our business not at all.
Just curious if anyone else would feel the desire to rewrite that section, or if I should get put my head down and get back to work on more important things. But, as someone who DID sort of back in to this position, as many of you have as well, I am just generally curious for any general thoughts about how much you focus on efficiency in your nightly jobs, or, if it is indeed routine to leave things alone as long as they are not getting in the way of anything else. And again, yes, I know that a 1.5 minute job is hardly a large concern.
John
We used to run jobs (and scheduled reports) that took half an hour or more. I got used to scheduling nightly sales reports a half hour apart because they would step on each other and fail.
I've aggressively tried to rein all of these in during my tenure, eliminated all looping nightly jobs (imagine running a loop over all 500k constituents...), and made other major efficiency improvements in custom reporting. And I'm thrilled to say that now most of them complete in...about 2 minutes or less.
I would say, "not a top priority, but don't discount the value of learning experience." Efficiency improvements might not mean much initially in this case, but could count as important future-proofing. It's also possible that a re-write might make the code more maintainable in the future. And you might also come across an issue that has occasionally hit me, which was that writing more clear and maintainable code wound up coming at an efficiency cost.
I find it easier to have one job per schedule, and have multiple tasks in each job. For example, my nightly job has over 30 steps, and they each run consecutively so I don't have to worry about them overlapping.
Definitely recommended for jobs. Also, it's often a good idea to be sure that jobs will run in the right order (ex: apply constituencies, then compute rankings).