Help with Ranking SQL/VBA/Macros/Modules

[Dave Smith]

I’m looking for help on being able to speed up my Ranking query. Below are a couple of my Rank formulas. And way below is what I copied from access sql.
How would I clean it up so my query will run faster?
How would I make this a actually sql statement, so that it will run faster?

Or should I look at doing a macro or modules?

I have also attached the database that uses these queries to help better understand what I'm trying to do. Please look at query titled:” qryRank_and_Wei ghtedRank”

If anyone has any advice help or recommendations , please don’t hesitant to tell me. I'm still new and learning and any time I get help or advice it helps me learn.

3MonthRank: (Select count(*) from GP1_Master_Table_Count_Apps as B where
GP1_Master_Table_Count_Apps.[3Month_App_Count] > B.[3Month_App_Count])

6MonthRank: (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[6Month_App_Count] > B.[6Month_App_Count])

9MonthRank: (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[9Month_App_Count] > B.[9Month_App_Count])

SELECT GP1_Master_Table_Count_Apps.OMNI_Number, GP1_Master_Table_Count_Apps.[3Month_App_Count], (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[3Month_App_Count] > B.[3Month_App_Count]) AS 3MonthRank, (Select count(*) from GP1_Master_Table_Count_Apps) AS TotalRecords, [3MonthRank]/([TotalRecords]-1)*5 AS 3Month_0to5Rank, CInt([3Month_0to5Rank]*10)/10 AS 3Month_Star_Rating, (Select sum([3Month_App_Count])from GP1_Master_Table_Count_Apps) AS 3MonthGrandTotal, (select min([3Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 3MonthMinTotal, (select max([3Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 3MonthMaxTotal, 5*(([3Month_App_Count]-[3MonthMinTotal])/([3MonthMaxTotal]-[3MonthMinTotal])) AS 3MonthWeightedRank, GP1_Master_Table_Count_Apps.[6Month_App_Count], (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[6Month_App_Count] > B.[6Month_App_Count]) AS 6MonthRank, [6MonthRank]/([TotalRecords]-1)*5 AS 6Month_0to5Rank, CInt([6Month_0to5Rank]*10)/10 AS 6Month_Star_Rating, (Select sum([6Month_App_Count])from GP1_Master_Table_Count_Apps) AS 6MonthGrandTotal, (select min([6Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 6MonthMinTotal, (select max([6Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 6MonthMaxTotal, 5*(([6Month_App_Count]-[6MonthMinTotal])/([6MonthMaxTotal]-[6MonthMinTotal])) AS 6MonthWeightedRank, GP1_Master_Table_Count_Apps.[9Month_App_Count], (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[9Month_App_Count] > B.[9Month_App_Count]) AS 9MonthRank, [9MonthRank]/([TotalRecords]-1)*5 AS 9Month_0to5Rank, CInt([9Month_0to5Rank]*10)/10 AS 9Month_Star_Rating, (Select sum([9Month_App_Count])from GP1_Master_Table_Count_Apps) AS 9MonthGrandTotal, (select min([9Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 9MonthMinTotal, (select max([9Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 9MonthMaxTotal, 5*(([9Month_App_Count]-[9MonthMinTotal])/([9MonthMaxTotal]-[9MonthMinTotal])) AS 9MonthWeightedRank, GP1_Master_Table_Count_Apps.[12Month_App_Count], (Select count(*) from GP1_Master_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.[12Month_App_Count] > B.[12Month_App_Count]) AS 12MonthRank, [12MonthRank]/([TotalRecords]-1)*5 AS 12Month_0to5Rank, CInt([12Month_0to5Rank]*10)/10 AS 12Month_Star_Rating, (Select sum([12Month_App_Count])from GP1_Master_Table_Count_Apps) AS 12MonthGrandTotal, (select min([12Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 12MonthMinTotal, (select max([12Month_App_Count]) from GP1_Master_Table_Count_Apps) AS 12MonthMaxTotal, 5*(([12Month_App_Count]-[12MonthMinTotal])/([12MonthMaxTotal]-[12MonthMinTotal])) AS 12MonthWeightedRank
FROM GP1_Master_Table_Count_Apps

;

Thank you for taking helping me with this question

[NeoPa]

I can't help much I'm afraid. This is quite a long and involved piece of SQL (entirely unreadable just as it comes though, so I reproduce it in legible form below).

Ranking SQL, by its very nature, tends to be slow processing. This will always be the case when any (let alone as many as you have) subqueries need to be run over and over again (as must be the case for ranking queries). Once for each record of the master query.

One concept, which you appear to be utilising already anyway, is to refer to calculations already referenced by name so they won't need to be reprocessed. This is usually optimised anyway, but it's not always possible for the optimiser to recognise all situations you can where the dsame values are reused. It also guarantees nono is missed.

I'm afraid the main message is that this isn't such bad SQL, and bearing in mind what it's doing, you need to allow for it running pretty slowly.

SELECT [OMNI_Number]
     , [3Month_App_Count]
     , (SELECT Count(*) FROM GP1_Master_Table_Count_Apps AS [B] WHERE ([3Month_App_Count] > B.[3Month_App_Count]) AS 3MonthRank
     , (SELECT count(*) FROM GP1_Master_Table_Count_Apps) AS TotalRecords
     , [3MonthRank]/([TotalRecords]-1)*5 AS 3Month_0to5Rank
     , CInt([3Month_0to5Rank]*10)/10 AS 3Month_Star_Rating
     , (SELECT Sum([3Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 3MonthGrandTotal
     , (SELECT min([3Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 3MonthMinTotal
     , (SELECT max([3Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 3MonthMaxTotal
     , 5*(([3Month_App_Count]-[3MonthMinTotal])/([3MonthMaxTotal]-[3MonthMinTotal])) AS 3MonthWeightedRank
     , [6Month_App_Count]
     , (SELECT count(*) FROM GP1_Master_Table_Count_Apps AS B WHERE [6Month_App_Count] > B.[6Month_App_Count]) AS 6MonthRank
     , [6MonthRank]/([TotalRecords]-1)*5 AS 6Month_0to5Rank
     , CInt([6Month_0to5Rank]*10)/10 AS 6Month_Star_Rating
     , (SELECT Sum([6Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 6MonthGrandTotal
     , (SELECT min([6Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 6MonthMinTotal
     , (SELECT max([6Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 6MonthMaxTotal
     , 5*(([6Month_App_Count]-[6MonthMinTotal])/([6MonthMaxTotal]-[6MonthMinTotal])) AS 6MonthWeightedRank
     , [9Month_App_Count]
     , (SELECT count(*) FROM GP1_Master_Table_Count_Apps AS B WHERE [9Month_App_Count] > B.[9Month_App_Count]) AS 9MonthRank
     , [9MonthRank]/([TotalRecords]-1)*5 AS 9Month_0to5Rank
     , CInt([9Month_0to5Rank]*10)/10 AS 9Month_Star_Rating
     , (SELECT Sum([9Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 9MonthGrandTotal
     , (SELECT min([9Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 9MonthMinTotal
     , (SELECT max([9Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 9MonthMaxTotal
     , 5*(([9Month_App_Count]-[9MonthMinTotal])/([9MonthMaxTotal]-[9MonthMinTotal])) AS 9MonthWeightedRank
     , [12Month_App_Count]
     , (SELECT count(*) FROM GP1_Master_Table_Count_Apps AS B WHERE [12Month_App_Count] > B.[12Month_App_Count]) AS 12MonthRank
     , [12MonthRank]/([TotalRecords]-1)*5 AS 12Month_0to5Rank
     , CInt([12Month_0to5Rank]*10)/10 AS 12Month_Star_Rating
     , (SELECT Sum([12Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 12MonthGrandTotal
     , (SELECT min([12Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 12MonthMinTotal
     , (SELECT max([12Month_App_Count]) FROM GP1_Master_Table_Count_Apps) AS 12MonthMaxTotal
     , 5*(([12Month_App_Count]-[12MonthMinTotal])/([12MonthMaxTotal]-[12MonthMinTotal])) AS 12MonthWeightedRank
FROM   GP1_Master_Table_Count_Apps