the Sweet Smell of Burning Fur (plonq) wrote,
the Sweet Smell of Burning Fur

  • Location:
  • Mood:
  • Music:

Slightly more efficient than a block of granite

One of my counter-parts out in the head office emailed me today, asking if I could help her pinpoint a problem with one of our databases. She was watching it while the regular person is away on vacation, and it was taking (in her words) forever to run. She though that the query might have hung because it had been sitting for two hours without any change.

I had a look at my own copy of the database (I backed up that folder to a local drive a couple of days ago), and I could not find anything technically broken, so I assured her that it was just a slow network being slow.

Then I started digging into some of the queries this thing was running, and based on what I am looking at here, I would be surprised if it is finished running by this time tomorrow.

I may be misremembering, but I vaguely recall a cartoon that centred around a tree (belonging to either Bugs Bunny or Chip & Dale) getting cut down. The mighty tree got transported to a mill where it went through a number of steps, saws, and milling machines until it was finally used to produce a single toothpick. This database reminds me a bit of that.

This thing connects to a couple of tables in a source database I created for them, and its first query connects to those tables with no limit on the scope. This query is then queried by a second query which cross-references it against the second table to find matching records. Then that query is queried by about eight more queries, each filtering on a single element. Finally all of those queries are joined in a final query, which is the only place she put a limiter on the date (because she only wants the previous day).

Now I have to decide if I want to let this abomination live, or if I am going to quietly re-write it. On the plus side I think that I could probably reduce its run time from hours to minutes, but on the flip side the creator of this database does not handle change well. If I re-wrote this, it would be like walking up and flipping her puzzle table, scattering the pieces everywhere. It's not that I don't want to flip her table, but I'd kind of like to be there when it's flipped.
Tags: access
  • Post a new comment


    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.