Speeding up a Ranking Formula

[TJVFree]

Does anyone know how I can speed up a Ranking query? I’m currently using the formula below:

Rank: (Select count(*) from qryCustomerTotals1 as B where qryCustomerTotals1.CustomerTotal > B.customerTotal)

From reading on the web, some people talk about doing it in a modules, they even give some examples (see below)

I’m just learning access, and have come a long way with all your help. I sure could use some advice with this one.

I don’t even one where to begin putting together the code below.

Thanks for taking the time to help a new access guy.

The complete code is:

Option Compare Database
Dim lngLastPoints As Long
Dim lngLastRank As Long
Dim lngRankInc As Long

Function RankFunction(lngPoints As Long) As Long

lngLastPoints = 0

If lngLastPoints = lngPoints Then
   RankFunction = lngLastRank
    lngRankInc = lngRankInc + 1
    lngLastPoints = lngPoints
Else
     lngRankInc = lngRankInc + 1
     RankFunction = lngRankInc
     lngLastRank = lngRankInc
     lngLastPoints = lngPoints
End If
End Function

 

[MajP]

99% of the time a sql query is faster (and often orders of magnitude faster) than writing a function. Is this query really that slow? More likely you need to look at the query and try to figure out how to make it faster. This is a subquery so is it the subquery that is slow or the rest of the query? Is it the subquery or is it the query qryCustomerTotals1?

if you had to build a function it would work like this.

... select getRank([somePKfield]) as Rank ...

public function getRank(PK as variant) as long
dim rs as dao.recordset
dim strSql as string
strSql = "some query string to help determine the Rank"
set rs = currentDB.openrecordset("strSql")
' some code to determine its rank
' without seeing the query to determine the rank it is hard to tell
'Could be as simple as find the PK and determine its absoluteposition
getRank = some value
end function

so this is why it is likely real slow. The query runs and for each record in the query it calls the function. Each call opens a recordset, does some rank calcualation, and returns the rank to a calculated control.

If I cannot get my sql optimized to work efficiently, I doubt I am going to a dynamic function. I would likely add a field to some table "Rank". Then in code I am going to open the table calculate the rank, and then write to the table. The reason this may be faster than a subquery or a function is I open a recordset once, read through it once, and write once. Of course the rank is static until I rerun the code.

 

[Andrzejek]

I may be way off here, but in your Selects statement you say:

Select count(*) 
from qryCustomerTotals1 as B 
where qryCustomerTotals1.CustomerTotal > B.customerTotal

So, are you saying: give me the count of everything from qryCustomerTotals1 (that I will call now B)
where [tt]CustomerTotal [red]>[/red] CustomerTotal[/tt] ???

I am probably missing something abvious here.....

Have fun.

---- Andy

 

[TJVFree]

MajP/Andy/Anyone else that can help.

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.

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

 

[MajP]

This really is not the appropriate forum. This should be either in the Access Modules or Access Queries. I do not think you plan to build a stand alone VB application, do you? You cannot delete a thread, but I would post in the other forum (pick one) and then just in this thread make a reference to the new thread number.

There is a lot going on here. You have about 17 subqueries, lots of aggregates, and lots of calculated fields. In my mind the query look pretty code you do not have any egregious issues such as nested iifs and complex vba functions.

So, are you sure it is the ranking subqueries slowing it down or is it just an issue with the whole thing?
What fields are indexed in your table?
How many records in your table?
How much time does it take to run?

So do some trouble shooting:
How slow is just this (the ranking query)?
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 from GP1_Master_Table_Count_App

And This (the 3 month fields)?
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