Don't you hate it when you've finally sunk into the supreme, Über-komfortable position on the couch, only to have a rotisserie cooker infomercial come on and the remote control mock you from an unreachable distance? I'm sure, among us, there have been many solutions to this dilemma, from using a shoelace lasso to snag a broom to press the buttons on the TV, to jamming keys in the electric socket to cause a short, throw a breaker, and make the audience finally stop yelling "Set It and Forget It!"

Now that's perfectly rational and understandable. But, I can never picture myself saying:

Boy, I really need to know the day of the week, but I can never remember the arguments to that DatePart  function. I suppose I could go look in the Books On-Line or do a google search. Nahh ... I'll just create a table that stores every day, from 1900 to 2100. That's what, only ... uhh ... 365.25 times ... 2100 ... minus ... 1900 ... eh, a few hundred.

Apparently, that's what Tom Cannaerts' colleague did:

Tom found out of this as a result of accounting complaining about queries taking minutes or timing out all together. Apparently, sub querying tblDateYearWeekDay in queries with large tables doesn't work so well:

CREATE PROCEDURE spAkkoordRegCtrl (
   @RegWerknemer int,
   @WEEK tinyint,
   @JAAR smallint
) AS
     UPDATE tblTijdregistratie
     SET
       RegAkkoord = 1,
       RegFix = 1
     WHERE
       tblTijdregistratie.RegWerknemer = @RegWerknemer
        AND (SELECT DateWeek FROM tblDateYearWeekDay
             WHERE (tblTijdregistratie.RegDatum >= DateValue)
             AND (dateadd(dd, -1,tblTijdregistratie.RegDatum) < DateValue))
               = @WEEK
        AND (SELECT DateYear FROM tblDateYearWeekDay
           WHERE (tblTijdregistratie.RegDatum >= DateValue)
             AND (dateadd(dd, -1,tblTijdregistratie.RegDatum) < DateValue))
               = @JAAR

Tom was able to reduce that query from over 30 seconds to less than a second with a simple change to the WHERE clause:

  tblTijdregistratie.RegWerknemer = @RegWerknemer
    AND DatePart(WEEK,regdatum) = @WEEK
    AND DatePart(YEAR,regdatum) = @JAAR

I wonder if he could have gotten away with charging a lot like our favorite DB Consultant.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!