Post reply

Creative Development

  • July 21, 2023 at 12:00 am

    #4231674

    Comments posted to this topic are about the item Creative Development

  • July 21, 2023 at 8:56 am

    #4240856

    I came across a DB with thousands of tables.

    A process was run where a master stored procedure was run that triggered another 10 stored procs.

    Each stored proc generated a dynamically named table, dynamic sql to populate and update the tables, index them etc.

    There was some bizarre conditional logic that populated then stripped out certain records from tables, again all dynamic.  Each proc was hundreds, if not thousands of lines long.

    It took a long time to work out what was going on and what those procs were actually trying to do. I deduced a number of things

    • Many of the tables could have been a common shared reference tables.
    • The total number of tables could have been 27 partitioned table, not 4,500 and rising.
    • The dynamic SQL was only needed because of the table generation approach
    • The approach of building orchestration using stored procs should have been handled by an orchestration tool.
    • The stored procs could have been more kindly described as "applications"
    • About 50% of the code in the stored procs did not need to exist, even if the design flaws were left in place.
    • The home built logging solution should have been taken out shot, burnt, its ashes mixed with salt and dumped into the Mariana Trench.

    The worst part was the team realised their approach guaranteed them job security and were highly resistant to anything that simplified the process.  I now know why incoming CTOs in web companies tend to burn the existing stuff to the ground rather than try and fix it.

    LinkedIn Profile

  • July 21, 2023 at 2:37 pm

    #4241093

    WOW, David! Your description makes me wonder how a generative AI would create a new database. Sounds like something similar might happen.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • July 21, 2023 at 2:42 pm

    #4241095

    Where I work my boss likes to use TOAD to help me generate the code for a new database. I don't have much experience with TOAD. It's installed on my desktop, but my boss likes to keep that part of the process to himself. That's OK, I think if I were a boss I'd want to keep some of the technical tasks, too.

    I've got experience with at designing databases, having done so for years. I can make a 3NF database. However, now that I work in a large IT group I think it is sad we don't take advantage of the DBAs we have. I'm sure that any one of your guys/gals can run circles around the database designs I come up with.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • July 21, 2023 at 8:07 pm

    #4241263

    I used to support an application that did consolidated reporting of data from remote sites.  Each remote site had a small Oracle database server which contained the data.  On the centralized SQL box there was a job running a very large SQL script that dynamically deleted and recreated a linked server to point at a specific site, then pulled back the data, manipulated it, then pushed it into the consolidated tables.  That whole process was hardcoded into a single script (no loop for sites)  in a single scheduled job step.  Just for fun, when it was first handed to me it was running on a desktop machine under somebody's desk, the guy who had written it had quit, had never had a backup, and had a log full of hard drive failure immanent warnings.  Fun times.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply