Optimising SQL correlation using a temp table

As an alternative, let us try and build a temp table and match it against records from our source table. We have also used one other trick: instead of looking for the largest date (the latest event) and then matching back against it, we have created a counter (identity) column on the source table. Since the latest record will always have a higher number than any other, we can now work with integers rather than datetime fields.

create table #progress
(jobid int, unqid int)

insert #progress
select
jobid
, max (unqid) idnum
from jobprogress
group by jobid

select
a.jobid ID
, jobstatus "STATUS"
, convert(varchar(20),statusdate,113) "WHEN"
, assignedto TECH

from jobprogress a
inner join #progress b
on b.unqid = a.unqid

drop table #progress

Again this gives us the right answers:

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

Problems improved

Table: #progress___________000000000D scan count 0, logical reads: 8
Table: jobprogress scan count 1, logical reads: 1
Table: Worktable scan count 1, logical reads: 17

Further Improvements

We have reduced the I/O count from 109 to 26. But we can do better still ... through denormalisation.

Location Map

Updates

Temp tables

Optimising SQL correlation using a temp table

Problems improved

Further Improvements

Copyright DBU 2000. Last updated 24 May 2004

Copyright DBU 2000.
Last updated 24 May 2004