Optimizing a Query

[kieriosity]

Does anyone know a better way to do the following query?

SELECT [CCM_DETAIL_1_ACT].[FNAME],
[CCM_DETAIL_1_ACT].[LNAME],
[CCM_DETAIL_1_ACT].[ORG],
[CCM_MASTER_1_ACT].[INPUT],
[CCM_MASTER_1_ACT].[DUE],
[CCM_MASTER_1_ACT].[FILENO],
[CCM_MASTER_1_ACT].[DOCKET],
[CCM_MASTER_1_ACT].[OFFICE],
[CCM_MASTER_1_ACT].[DOCTYPE],
[CCM_MASTER_1_ACT].[SYNOP],
[CCM_MASTER_1_ACT].[SIGN],
[CCM_ROUTING_1_ACT].[ASGNDATE],
[CCM_ROUTING_1_ACT].[GROUPNAME],
[CCM_ROUTING_1_ACT].[ASGNTO],
[CCM_ROUTING_1_ACT].[TASK],
[CCM_ROUTING_1_ACT].[ROUTID],
[CCM_MASTER_1_ACT].[CLOSED]

FROM (CCM_MASTER_1_ACT INNER JOIN CCM_ROUTING_1_ACT ON [CCM_MASTER_1_ACT].[MASTERID]=[CCM_ROUTING_1_ACT].[MASTERID]) INNER JOIN CCM_DETAIL_1_ACT ON [CCM_MASTER_1_ACT].[DETAILID]=[CCM_DETAIL_1_ACT].[DETAILID]

WHERE CCM_MASTER_1_ACT.DOCTYPE NOT IN ('WI','B','BR','V','VBI','VBL','VFR','VSI','VSL','VVM') AND CCM_ROUTING_1_ACT.ASGNTO Like 'rrs%' AND CCM_ROUTING_1_ACT.ROUTID IN (SELECT Max(CCM_ROUTING_1_ACT.ROUTID) AS MaxOfROUTID FROM CCM_ROUTING_1_ACT GROUP BY CCM_ROUTING_1_ACT.MASTERID) AND CCM_MASTER_1_ACT.CLOSED Is Null;

I'm trying to select the most recent routing for a record from the table, so I used a subselect to get the max RoutID for a grouped FK and matched it to the RoutID I want to show. Seems straight forward, but I've never tried anything like this before so I would like to make sure.

Thanks.

 

[r937]

your subselect does not obtain "the max RoutID for a grouped FK"

instead, it gets the many max RoutID values for the various MASTERIDs, and while the syntax may be working, i think that this might be logically wrong

in particular, i think you should be getting the max RoutID for the particular MASTERID that the RoutID is related to

this means a correlated subquery, rather than the IN (subselect) construction you have

assign a correlation variable to the outer Routing table --

FROM ( CCM_MASTER_1_ACT
INNER JOIN CCM_ROUTING_1_ACT FOO
ON [CCM_MASTER_1_ACT].[MASTERID]
= [CCM_ROUTING_1_ACT].[MASTERID] )
INNER JOIN CCM_DETAIL_1_ACT
ON [CCM_MASTER_1_ACT].[DETAILID]
= [CCM_DETAIL_1_ACT].[DETAILID]

then change the WHERE clause as follows --

AND CCM_ROUTING_1_ACT.ROUTID
= ( SELECT Max(CCM_ROUTING_1_ACT.ROUTID)
FROM CCM_ROUTING_1_ACT
WHERE CCM_ROUTING_1_ACT.MASTERID
= FOO.MASTERID )

but i'm guessing


rudy

 

[kieriosity]

Thanks for replying.

The subselect query is getting the max routid for the each reference of MasterID in the routing table; that's what the Group By is for. I want a single RoutID for each MasterID. The MasterID is the FK in the routing table; it's the PK in the MASTER table.

I tried the method you suggested and it took three times as long to run, but I appreciate the reply. I see what you are saying though about logically wrong, and that is why I'm asking.

Thanks.

 

[r937]

The subselect query is getting the max routid for the each reference of MasterID in the routing table; that's what the Group By is for. I want a single RoutID for each MasterID. The MasterID is the FK in the routing table; it's the PK in the MASTER table.

yeah, but on a given masterid, namely the one that the outer query is sitting on, you are joining to multiple routings, and yet the subquery is saying, accept the row with this given masterid and any routing, as long as that routing is the maximum routing for any other masterid

that's why i got confused

but it's your data, you know it better than i do, eh

 

[expostfacto]

usually using a vendor extension to get only one row will be faster than matching with a subselect, e.g.

select * from foo
order by foo_id desc
limit 1

rather than

select * from foo
where foo_id = (select max(foo_id) from foo)

http://www.carnageblender.com:

Carnage Blender. Over 40 million battles served.