• Nadrek (7/30/2010)


    ...

    When writing new SQL, why not take a little more time and do it well, instead of randomly? For existing bad SQL, most often I see that a few minutes rewriting is extremely beneficial, and has no bad side effects.

    ...

    That may be fine if you're the one in control of the code being written. However, if you're like the majority of DBA's in the workplace, quite often that is not the case. You'll have to deal with development team and/or external vendors. Maybe even get managers approval for changing work priorities and any additional charges.

    Case in point, a vendor application we have running had a report that took 10-15 minutes to run. The end-user spoke directly to the vendor who quoted 4 hours of work to fix the report. This work was done and implemented and the report took just a few minutes. In the next invoicing cycle the extra 4 hour charge was rejected as it hadn't been approved. Naturally the vendor wasn't pleased about this and they reversed their change and left the user with the slow report.

    At this stage the user decided to speak with me and within 5 minutes I had identified a lack of indexes (there were none on the reporting tables). After creating some indexes the same report took 2-3 minutes to run. Later that week, when I had some free time, I decrypted the vendors procedure, pulled apart the mess and got the report to run in less than 30 secs. Naturally being vendor code I couldn't deploy the changes I'd made, but given I have a good relationship with them, I sent the changes through their developer.

    Three months later when we performed an upgrade on the application, the report that was running in 2-3 minutes started to run in less than 30 sec.

    Nadrek (7/30/2010)


    ...

    Essentially: Why add cost in one place to reduce cost in another before you attempt to reduce cost without adding any?

    Easy answer, add the minimal (yes minimal, despite your assertions) cost in the short term (ie: getting the end-user a report they require), then see about change approval, testing, etc... of re-written code.

    --------------------
    Colt 45 - the original point and click interface