Optimising correlations through denormalisation

Now we can improve the efficiency of our searches substantially. We add two columns to the main "JOBS" table:

statusdate (datetime or smalldatetime)
technician (char(4))
(-- can lookup another table of technicians)

Now every time we make a change to status, we run a stored procedure which inserts a new row in the JOBSTATUS table and simultaneously updates the JOBS table. Remember to keep the two phases (insert new row, update JOBS table) inside a transaction.

create proc NewStatus
@jobid int, @status int, @tech char(4)
as
begin tran
insert jobprogress
(jobid, statusid, technician)
values
(@jobid, @status, @tech)

update jobs
set
jobstatus=@status,
tech=@tech,
statusdate=getdate()
where jobid=@jobid
commit tran

Then run the query

select
jobid ID
, jobstatus "STATUS"
, convert(varchar(20), statusdate, 113) "WHEN"
, TECH
from jobs
order by jobid

and we get the same result as before:

ID	STATUS	WHEN	TECH
1	5	02/12/03 13:58	C
2	4	02/12/03 22:01	C
3	2	04/12/03 11:59	A
4	2	04/12/03 12:55	C
5	5	02/12/03 14:30	D
6	2	02/12/03 15:36	D
7	3	03/12/03 13:30	A
8	4	03/12/03 13:35	B

Problem resolved

This time we have cut down the I/O to the number of results + 1 (because we are using a non-clustered index). We have an additional I/O on insert however.

Table: jobs scan count 1, logical reads: 9 Insert/update: 1 or 2 additional I/O Total I/O: 11, down from 109 +.

Consider denormalisation when frequent searches would give expensive correlations or joins

Location Map

Updates

Denormalisation

Optimising correlations through denormalisation

Problem resolved

Copyright DBU 2000. Last updated 24 May 2004

Copyright DBU 2000.
Last updated 24 May 2004