"Min in Select statement" or DMin (). Which one is preferable?

Question

I needed to find the minimum income from the tbl_Revenue table. I discovered two ways to do this:

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 the following questions:

• which one is more computationally efficient? Is there a big difference in computational efficiency if, instead of the tbl_Revenue table, is there a choice of state using joins?
• Is there a problem with the correctness of the DMin fund? (Exactly, I mean, are there any loopholes I need to know about before using DMin.)

Answer 1

In your specific code, you run it once, so it doesn't really matter. If it is in a loop or query, and you combine hundreds or thousands of iterations, you will run into problems.

If performance of more than a thousand 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 do the same for the DAO request, replacing the MinRev2 part. Perform them several times and take on average. Make every effort to simulate the conditions in which he will work; for example, if you change the parameters in each request, do the same, because this is likely to affect the performance of both methods. I did something similar with DAO and ADO in Access and was surprised to find that under my conditions, DAO works faster (this was several years ago, so maybe the situation has changed since then).

There is a definite difference when it comes to using DMin in a query to get the minimum from an external table. In 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 you need from both tables and create a form or report for that query.

However, this is slightly different from your situation in which you use both VBA methods.

I tend to believe (possibly erroneously because I have no evidence) that domain functions (DMin, DMax, etc.) are slower than using SQL. Perhaps if you run the code above, you can tell us how it works.

If you spell DMin correctly, I have no questions about accuracy. Did you hear that you were? Essentially, the call should be: DMin("<Field Name>", "<Table Name>", "<Where Clause>")

Good luck

Answer 2

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

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

Similarly, Set db = CurrentDb and reusing db everywhere.
In situations where I want to make sure that I have the best speed, I use Public db as DAO.Database when opening the application. Then, in each module where required, I use If db is Nothing Then set db = CurrentDb .