Hi Dale,
I can easily see why the first query would be inefficient. When you
think about it, it is doing 10,000 summary queries in addition to the main
query.
It is strange the convolutions one has to go through to get a
sequential numbering of the results of a query. All, I realize, because
sequential numbering is not part of set theory.
An alternative to a temporary table would be a permanent ranking column
in the table. Then create a query that sorts the data as needed and includes
the ranking field. Then do an update query based off of the previous query
that updates the ranking using function. It would need to be done only
whenever the data changes. That in essence is something that I do in at
several cases. Although I usually have to do it in code due to the
complicatedness (is that a word) of how the rankings are determined. This
works when the rankings only need calculating on an occasional basis. If
they needed calcuation say every few minutes, then something else would have
to be done.
The recalculation of the function value is actually more of a problem
than just with reports. If you are displaying the query and click on one of
the rows it recalculates. And it redoes all of them if you Alt-Tab to
another application and back. This is something that I noticed with the
Rnd() function in queries. The constant recalcuation whenever something is
touched or sorted.
My thoughts, for what worth you may find in them,
Clifford Bass
Dale Fye said:
Clifford,
The query I was looking at creates a ranking based on the value of a numeric
field. I've seen the technique below used on numerous occassions, but it
returns duplicate rankings if there is no secondary field to generate a sort
order.
SELECT tbl_RankTest.SomeNumber,
(Select Count(*) FROM tbl_RankTest as T
WHERE T.[SomeNumber] <= tbl_RankTest.[SomeNumber]) AS Rank
FROM tbl_RankTest
ORDER BY tbl_RankTest.SomeNumber;
I've also used a function (fnRank) I created a while back (shown below) in
the 2nd query (also shown below). Since this function uses a static
variable, you have to call it once to reset that variable with reset set to
true, but then when you use it in a query, and pass it any field value from
the recordset, it returns sequential numbers.
SELECT tbl_RankTest.SomeNumber,
fnRank([ID]) AS Rank
FROM tbl_RankTest
ORDER BY tbl_RankTest.SomeNumber;
Public Function fnRank(SomeValue As Variant, Optional Reset As Boolean =
False) as long
Static myRank As Long
If Reset Then
myRank = 0
Else
myRank = myRank + 1
End If
fnRank = myRank
End Function
So tonight I ran a simple test,
1. created a table with 10000 records with random numbers between 0 and
5000
2. Ran the 1st query shown above through my "evaluation subroutine" (see
discussion below)
3. Ran the 2nd query shown above through my "evaluation subroutine"
4. Indexed the field, and reran query 1
5. Reran query 2
Results :
Step2 Seconds
2 94
3 0
4 16
5 0
Obviously there is a huge reduction (83%) in the time it took to generate
the of the first, but I was amazed in the performance of the function. Did
not think it would be nearly that fast.
My only concern with my function, is that when I run the query from the
query grid, and scroll from one "page" of results to the next, it
recalculates the value in the Rank column (but it only appears to do it for
the visible rows). This behavior also shows up when I used the query
results in a form and when I used them in a report. I also noted that when
I used it in a report, if I sorted by that column, it actually runs the
query twice, so the first number in the rank column ends up being the number
of records in the result set + 1. Strange behavior.
Given the speed advantage of the query using my function over the other one,
I quess I could use a make-table or an append query to get the results into
a temp table for use in my report, then delete the records later.
Dale
