Everyone knew about The Report. On a moderately sunny day, with just the right wind speed and the planets properly aligned, The Report would run in a reasonable amount of time: two, maybe three minutes. But on every other day, The Report would take an incredible amount of time (measured in hours) to run, if it decided to run at all that day.

No one had a good explanation for why The Report acted this way, nor did they want to find out. It was The Policy: questioning The Report might upset it; if The Report got upset, it might not run; and if The Report didn't run, its users would be deprived its bountiful data. The Report was good to its users most of the time, and no one wanted to change that. That is, no one, except Steven Dargal.

Going against The Policy, Steven thought he'd take a look at The Report. He noticed that The Report was powered by a view that joined together seven tables, none of which had more than 300,000 rows. Looking more closely, Steven noticed that the view had several subqueries to other views and that these views had a slew of some rather strange joins on them: 

INNER JOIN dbo.fnc_GetCojobTb(3) cojob3
        ON cojob3.jc_id = industrycodes.jc_id
INNER JOIN dbo.fnc_GetCojobTb(4) cojob4
        ON cojob4.jc_id = industrycodes.jc_id
INNER JOIN dbo.fnc_GetTaskCodeTb(8) taskcd
        ON taskcd.tc_cd = industrycodes.tc_cd

Steven counted atleast thirty different calls to six diffrent functions within the views. Presented below is only a small portion of the fnc_GetCojobTb function, which is called eleven different times for each row in the view: 

DECLARE t CURSOR FORWARD_ONLY FOR 
 SELECT job_cd FROM cojobs WHERE del_ind = 0
OPEN t
FETCH NEXT FROM t INTO @cd
WHILE @@FETCH_STATUS=0
  BEGIN
    DECLARE t1 CURSOR FORWARD_ONLY FOR
      SELECT job_id FROM jobs
       WHERE job_cd IN (@cd)
    OPEN t1
    FETCH NEXT FROM t1 INTO @id1
    IF @@FETCH_STATUS=0 FETCH NEXT FROM t1 INTO @id2
    IF @@FETCH_STATUS=0 FETCH NEXT FROM t1 INTO @id3
    IF @@FETCH_STATUS=0 FETCH NEXT FROM t1 INTO @id4
    IF @@FETCH_STATUS=0 FETCH NEXT FROM t1 INTO @id5
    CLOSE t1
    DEALLOCATE t1
    INSERT INTO @cojobs VALUES (@cd, 1, @id1)
    INSERT INTO @cojobs VALUES (@cd, 2, @id2)
    INSERT INTO @cojobs VALUES (@cd, 3, @id3)
    INSERT INTO @cojobs VALUES (@cd, 4, @id4)
    INSERT INTO @cojobs VALUES (@cd, 5, @id5)
    FETCH NEXT FROM t INTO @cd
  END
CLOSE t
DEALLOCATE t

Although I'm sure that the SQL Developers out there will appreciate the code a bit more, suffice it to say that Steven was able to replace the view and all of its sub-views and functions with a much simpler query. Once his boss got over the fact that Steven violated The Policy, he agreed to allow The Report to be upgraded with Steven's code.

Sadly, now that The Report takes five seconds to run, it has lost much of its mystique. And although users now simply refer to it as "the job aggregate summary report," it shall forever be remembered here as The Report.

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