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.