Ranking and Subquery - Performance ...

[jacko69]

Hi there,

By way of intro I'm no expert but have managed to look at this and other forums to try to find a way to achieve what I need to (in Access 2007).

Essentially I'm trying to rank rows based on values (x) for each value of another field . So the outcome will be an order ranking by x for each discrete value of y.

I have tried two methods to do this, but frankly they suck from a performance angle. They do both work and I have included them below.

My question is whether there are any suggestions that will speed things up to the point where they are usable. One thing to note is that in the code below the 'table' SubV12RFO is actually a query that draws from another 6 or so tables / queries. As I write this I'm thinking it may help to dump the output into a temporary table before running the queries below.

Anyway, any thoughts or suggestions appreciated.

SELECT SubV12RFO.RID, SubV12RFO.FLD_TNO, SubV12RFO.MRID, IIf([RID]=[RID],(select count(*) from SubV12RFO AS D12RFO where
D12RFO.RID = SubV12RFO.RID and D12RFO.NPR > SubV12RFO.NPR)+1,"") AS FRank
FROM SubV12RFO
WHERE (((SubV12RFO.MID)=27857));

ABOVE .... Works, but very slow!

SELECT SubV12RFO.RID, SubV12RFO.FLD_TNO, SubV12RFO.MRID, 
    IIf(([RID]=[RID]),(SELECT COUNT([D12RFO].[NPR])
    FROM SubV12RFO AS D12RFO
    WHERE ([D12RFO].[NPR] > [SubV12RFO].[NPR]) And ([D12RFO].[RID]=[SubV12RFO].[RID])),"")+1 AS FRANK
FROM SubV12RFO
WHERE (((SubV12RFO.MID)=27857));

ABOVE .... Also works, but very slow

Thanks,
Jacko

 

[Brianwarnock]

When you run a query that is end of a stack all of the other queries are run, and therefore in this case they will presumably be run more than once, so it may be worthwhile trying your suggestion of a temporary table to see if that improves the situation.

If it does then you will need to think of making that simple to run either vis a code script or macro which is the only way I have ever done it but believe the use of VBA is the recommended method.

Brian

 

[jacko69]

Thanks Brian,

Will have a go at that using a macro ... think VBA may be a bit beyond my capabilities!

Regards,
Jacko

 

[jacko69]

OK, so have tested that approach.

On the positive side it worked with the correct output including rankings after running the query on a table.

On the negative side it still took a long while - about 2hrs 15mins with the table having approx 85K records and approx 8.5K 'groups' within that. The hardware is reasonable, Win 7 64-bit running on quad core (not that Access seems to use more than one) with 8GB RAM.

Ideally I need to rank about 10 different measurements for each 'group', on a total of 250K plus records. It's only a periodical requirement, and can run overnight, but any suggestions on improving performance would be appreciated!

Thanks,
Jacko