＆QUOT;最低的Select语句和QUOT;或DMIN（）。哪一个是preferable？ [英] "Min in Select statement" or DMin(). Which one is preferable?

问题描述

我需要找到一个表tbl_Revenue最低收益。我发现两种方法做到这一点：

方法1

 昏暗MinRevenueSQL作为字符串
昏暗rsMinRev作为DAO.Recordset
MinRevenueSQL =SELECT民（tbl_Revenue.Revenue_Value）作为MinRevenue从tbl_Revenue WHERE（（（tbl_Revenue.Division_ID）= 20），（（tbl_Revenue.Period_Type）='年度'））;
设置rsMinRev = CurrentDb.OpenRecordset（MinRevenueSQL）
MinRev = rsMinRev！MinRevenue
 

方法2

  MinRev2 = DMIN（Revenue_Value，tbl_Revenue，（（（tbl_Revenue.Division_ID）= 20），（（tbl_Revenue.Period_Type）='年度'）））
 

我有以下问题：

1. 在这其中有一个是计算效率更高？是否有很大的差异的计算效率，如果不是tbl_Revenue桌子上有使用联接一个选择说明书？
2. 是否与DMIN fundtion的精度方面的问题？ （通过精确我的意思是没有办法，我需要使用DMIN之前，需要了解的任何漏洞。）

解决方案

在特定的code，你正在运行一次，因此并没有太大的差别。如果是在一个循环或查询，并要结合数百迭代或几千，那么你会遇到的问题。

如果在几千次迭代的性能对你很重要，我会写类似如下：

 子runDMin（）
    X =定时器

    对于i = 1到10000
        MinRev2 = DMIN（Revenue_Value，tbl_Revenue，（（（tbl_Revenue.Division_ID）= 20）与（（tbl_Revenue.Period_Type）='年度'）））
    下一个

    Debug.Print总运行时间秒：＆放大器;计时器 -  X
结束小组
 

然后实现相同的DAO查询，取代了MinRev2一部分。运行他们俩好几次，取平均值。尽最大努力把模拟它会在运行条件;例如，如果你将改变参数的每个查询中，这样做，因为这将极有可能在这两种方法的性能产生影响。我在做访问使用DAO和ADO类似的东西，惊讶地发现，我的条件下，DAO正在运行速度更快（这是几年前的，所以也许事情从那以后改变）。

肯定是有区别，当涉及到使用DMIN在查询中获得最低从外部表。在访问文档：

  

提示：虽然您可以使用DMIN函数找到最小值   从在一个外国表中的字段，它可能是更有效的创建   查询包含从两个表所需要的领域，   基地窗体上或在查询报告。

不过，这是不是你的情况略有不同，在其中都从VBA方法运行。

我倾向于认为（也许是错误的，因为我没有任何证据）的域函数（DMIN，DMAX等）比使用SQL要慢。或许，如果你运行code以上，你可以让我们知道它是如何变成了。

如果你正确地写入DMIN电话，没有，我是知道的准确性问题。你有没有听说过有？从本质上讲，电话应该是： DMIN（＆LT;现场名称＆gt;，＆LT;表名称＆gt;，＆LT; Where子句＆gt;中）

祝你好运！

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.)

解决方案

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!

这篇关于＆QUOT;最低的Select语句和QUOT;或DMIN（）。哪一个是preferable？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！