Optimizing Complex Query 2

[rclawson]

I'm needing some help optimizing a query. The following simplified query returns the results that I need, but the performance is terrible. Here is a simplified view of my data:

Table: BillRequests
BillRequestID
-------------
1
2
3
4

Table: ActivityHistory
BillRequestID ActivityCode Position
------------- ------------ --------
1 1 1
1 6 2
1 8 3
2 3 1
2 6 2


So, I have a Master/Detail relationship and I want a single row for each record in the BillRequests table, even if there is no record with the specified ActivityCode in the Activity History table. I think if I can update my WHERE clause to prune more records out earlier, my performance will improve and I can get rid of the DISTINCT keyword. I have about 30,000 records in the BillRequests table and about 350,000 in the ActivityHistory table and the ultimate use of the query will be to define a View. Once I have created the View, the queries on it take a VERY long time. I have not really used the NOT EXISTS functionality before, so I don't know if I am using it properly or if there is some better way.

Here is my simplified query:

select distinct BR.BillRequestID,
case when Exists(select * from ActivityHistory AH
where (AH.BillRequestID = BR.BillRequestID) and (ActivityCode = 6))
then AH2.SentToID
else NULL
end as EditorID

from BillRequests BR
left outer join ActivityHistory AH2 on AH2.BillRequestID = BR.BillRequestID
where
(
(Not Exists(select * from ActivityHistory AH
where (AH.BillRequestID = BR.BillRequestID) and (AH.ActivityCode = 6)))
or
(AH2.Position = ( select max(AH.Position) from ActivityHistory AH
where (AH.BillRequestID = BR.BillRequestID) and (AH.ActivityCode = 6)))
)

Any ideas on how I can get rid of the DISTINCT and still only have 1 row per record in my BillRequest table?

Thanks - RC

 

[PHV]

Take a look at LEFT OUTER JOIN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dnoeth]

This is a though one
If i got it right you're trying to exclude any "BillRequestIDs" with an "ActivityCode 6" unless it's at the highest "Position"?

I added some rows for a BillRequestID 3 without any ActivityCode 6:

create table BillRequests(
  BillRequestID int not null primary key);

insert into BillRequests values(1);
insert into BillRequests values(2);
insert into BillRequests values(3);
insert into BillRequests values(4);

create table ActivityHistory(
  BillRequestID int not null, 
  ActivityCode int not null, 
  Pos int not null, 
  SentToID int not null,
  primary key (BillRequestID, pos));

insert into ActivityHistory values(1,1,1,11);
insert into ActivityHistory values(1,6,2,12);
insert into ActivityHistory values(1,8,3,13);
insert into ActivityHistory values(2,3,1,21);
insert into ActivityHistory values(2,6,2,22);
insert into ActivityHistory values(3,1,1,31);
insert into ActivityHistory values(3,7,2,32);
insert into ActivityHistory values(3,8,3,33);

select 
  br.BillRequestID,
  ah.SentToID as EditorID
from 
  BillRequests br
left join 
 (
  select 
    BillRequestID, 
    max(case when ActivityCode = 6 then SentToID end) as SentToID,
    case 
      when coalesce(max(case when ActivityCode = 6 then Pos end), max(Pos)) <> max(Pos) then 1 
      else null
    end as ExcludeMe
  from ActivityHistory
  group by BillRequestID
 ) ah
on br.BillRequestID = ah.BillRequestID
where ah.ExcludeMe is null;

BillRequestID  EditorID
            2        22
            3      NULL
            4      NULL

Dieter

 

[rclawson]

PH,

Thanks for the reply. I have tried the simple inner join, but because of the complexity of the where clause, the inner join is not sufficient.

Thanks,
RC

 

[rclawson]

Dieter,

This is close. Let me clarify a few things to better define the problem. Here are some updated sql statements:

create table ActivityHistory(
  BillRequestID int not null,
  ActivityCode int not null,
  Pos int not null,
[b]  SentToID varchar(15) not null,[/b]
  primary key (BillRequestID, pos));

insert into ActivityHistory values(1,1,1,'rclawson');
insert into ActivityHistory values(1,6,2,'rclawson');
insert into ActivityHistory values(1,8,3,'dnoeth');
[b]insert into ActivityHistory values(1,6,4,'jsmith');
insert into ActivityHistory values(1,8,5,'dnoeth');[/b]
insert into ActivityHistory values(2,3,1,'mtwain');
insert into ActivityHistory values(2,6,2,'tjefferson');
insert into ActivityHistory values(3,1,1,'gwashington');
insert into ActivityHistory values(3,7,2,'rclawson');
insert into ActivityHistory values(3,8,3,'dnoeth');

Here is the output that I'm seeking:

BillRequestID  EditorID
            1   jsmith
            2   tjefferson
            3   NULL
            4   NULL

So, I don't want to exclude any rows from the BillRequests table in the result set, and if there are rows with an ActivityCode of 6 I want the one with the highest position, even if there are other ActivityHistory records with differect ActivityCodes that have a higher position. If no ActivityCode of 6, just return NULL for the EditorID.

Sorry I didn't put my expected results out the first time. Hope this is clearer.

Rob

 

[PHV]

Something like this ?
SELECT BR.BillRequestID, AH.SentToID EditorID
FROM BillRequests BR LEFT JOIN (
SELECT A.BillRequestID, A.SentToID
FROM ActivityHistory A INNER JOIN (
SELECT BillRequestID, ActivityCode, Max(Pos) MaxPos
FROM ActivityHistory WHERE ActivityCode=6
GROUP BY BillRequestID, ActivityCode
) B ON A.BillRequestID=B.BillRequestID AND A.ActivityCode=B.ActivityCode AND A.Pos=B.MaxPos
) AH ON BR.BillRequestID=AH.BillRequestID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dnoeth]

Hi Rob,
this it's much easier now ;-)

select 
  br.BillRequestID,
  ah.SentToID as EditorID
from 
  BillRequests br
left join 
 (
  select * from ActivityHistory ah
  where pos = 
   (select max(pos) from ActivityHistory 
    where BillRequestID = ah.BillRequestID
    and ActivityCode = 6
   )
 ) ah
on br.BillRequestID = ah.BillRequestID;

Dieter

 

[rclawson]

PH,

Thanks! That approach works great. It took my full query down from 1:22 to only 9 seconds. This should work great in my view. I have not worked much with derived tables in the FROM clause. I see it definitly bears further investigation.

Thanks again.
Rob

 

[rclawson]

Dieter,

Thanks for the response. This query also works great. It took my full query down from 1:22 to 18 seconds; much better than before. I will probably go with PH's query as the performance gain is better.

Thanks again.
Rob