“Min in Select statement” or DMin(). Which one is

问题:

I needed to find minimum revenue from a table tbl_Revenue. I found out two methods to do that:

Method 1

Dim MinRevenueSQL As String
Dim rsMinRev As DAO.Recordset
MinRevenueSQL = "SELECT Min(tbl_Revenue.Revenue_Value)As MinRevenue FROM tbl_Revenue WHERE (((tbl_Revenue.Division_ID)=20) AND ((tbl_Revenue.Period_Type)='Annual'));"
Set rsMinRev = CurrentDb.OpenRecordset(MinRevenueSQL)
MinRev = rsMinRev!MinRevenue

Method 2

MinRev2 = DMin("Revenue_Value", "tbl_Revenue", "(((tbl_Revenue.Division_ID)=20) AND ((tbl_Revenue.Period_Type)='Annual'))")

I have following questions:

1. which one of them is computationally more efficient? Is there a lot of difference in computational efficiency if instead of tbl_Revenue table there is a select statment using joins?
2. Is there a problem with accuracy of DMin fundtion? (By accuracy I mean are there any loopholes that I need to be aware of before using DMin.)

回答1:

In your specific code, you are running it once so it doesn't make much of a difference. If it's in a loop or a query and you are combining hundreds or thousands of iterations, then you will run into issues.

If performance over thousands of iterations is important to you, I would write something like the following:

Sub runDMin()
    x = Timer

    For i = 1 To 10000
        MinRev2 = DMin("Revenue_Value", "tbl_Revenue", "(((tbl_Revenue.Division_ID)=20) AND ((tbl_Revenue.Period_Type)='Annual'))")
    Next

    Debug.Print "Total runtime seconds:" & Timer - x
End Sub

Then implement the same for the DAO query, replacing the MinRev2 part. Run them both several times and take an average. Try your best to simulate the conditions it will be run under; for example if you will be changing the parameters within each query, do the same, because that will most likely have an effect on the performance of both methods. I have done something similar with DAO and ADO in Access and was surprised to find out that under my conditions, DAO was running faster (this was a few years ago, so perhaps things have changed since then).

There is definitely a difference when it comes to using DMin in a query to get a minimum from a foreign table. From the Access docs:

Tip: Although you can use the DMin function to find the minimum value from a field in a foreign table, it may be more efficient to create a query that contains the fields that you need from both tables, and base your form or report on that query.

However, this is slightly different than your situation, in which you are running both from a VBA method.

I have tended to believe (maybe erroneously because I don't have any evidence) that the domain functions (DMin, DMax, etc.) are slower than using SQL. Perhaps if you run the code above you could let us know how it turns out.

If you write the DMin call correctly, there are no accuracy issues that I am aware of. Have you heard that there were? Essentially, the call should be: DMin("<Field Name>", "<Table Name>", "<Where Clause>")

Good luck!

回答2:

I suspect that the answer may vary depending on your situation.
In a single user situation, @transistor1 testing method will give you a good answer for an isolated lookup.

But on a db that's shared on a network, IF you already Set db = CurrentDb, then the SELECT method should be faster, since it does not require opening a second connection to the db, which is slow.

The same way, it is more efficient to Set db = CurrentDb and reuse that db everywhere.
In situations where I want to make sure I have the best speed, I use Public db as DAO.Database when opening the app. Then in every module where it is required, I use
If db is Nothing Then set db = CurrentDb.