1

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.)
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
Heman
  • 118
  • 1
  • 3
  • 8

2 Answers2

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.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • Just curious - why would it matter whether you use Set db=CurrentDb or just use the CurrentDb object everywhere (unless you need the flexibility to change it to CodeDB or something else)? OP is using CurrentDB. Also how do you know DMin is opening a seperate connection, rather than internally using the CurrentDB object? – transistor1 Mar 23 '12 at 16:47
  • 2
    In *some* cases I experienced that using CurrentDb does not work properly, specially when you refer to it several times - but frankly I don't remember the details clearly. i think there's an example here: http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/7ea9506f-5e91-4896-80b9-6712762388ea/ – iDevlop Mar 23 '12 at 16:58
  • 2
    +1 very interesting, thanks for the link. I had wondered why that particular issue didn't work, and just side-stepped it using OpenRecordset(). I must admit I am inclined to think of CurrentDb() as an object though I did know it was a method (through IntelliSense). I always falsely assumed that CurrentDb() returned the same reference each time. – transistor1 Mar 23 '12 at 17:21
  • 2
    FWIW - to confirm, I just called `?objptr(CurrentDb())` twice in the immediate window, and got 2 different responses: `2305410` `2306366`. After that, every subsequent call gave me `2306366`. Changing some code and recompiling and saving the database gave a new value: `2303254`. I wonder why Microsoft implemented it that way - there must be a valid reason why the reference changes. – transistor1 Mar 23 '12 at 17:31
  • +1 on your answer - the thread you linked to was absolutely fascinating and had some great suggestions. Learned a great deal - Thanks! – transistor1 Mar 23 '12 at 18:26
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!

transistor1
  • 2,915
  • 26
  • 42