data base unlimited
data base unlimited
| Products | Code | Services | Resources |
SQL Correlation
Standard correlation solution
select
jobid as ID
, jobstatus as "STATUS"
, convert(varchar(20),statusdate,113) as "WHEN"
, assignedto as TECH
from jobprogress m
where statusdate =
(select max(statusdate)
from jobprogress
where jobid=m.jobid)
-- notice how we alias the outer
table
order by m.jobid
Gives the correct answer:
|
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
For every row resolved in the inner loop, we have to search under the conditions of the outer loop. The more records we have, the more searches will do.
Table: jobprogress scan count 1, logical
reads: 1
Table: jobprogress scan count 8, logical reads: 8
Table: Worktable scan count 42, logical reads: 100
Total I/O 109 on 34 records
The I/O count increases logarithmically.
Improvements
Let us try improving this through the
use of a temp table.
