DLookUp较多影响速度问题求解决方案-论证

蒋海兵　发表于：2009-03-17 14:06:48 　

我有许多文本框通过DLookUp引用某表数据。当窗体记录变化时这些文本框会自动刷新数据，这些数据相对固定并不要即时刷新，由于此类文本框较多，影响了速度。请教各位可有良策解决这一问题？

通过网上查询得出如下解决方案：（但是我将此代码写在新建模块上之后使用MLOOKUP函数没有反应，想请高人指点）

用 ADO 寫的相應的替代函數.

Public Conn As New ADODB.Connection    'Conn

'mLookUp  替代  AC中的  Dlookup
Public Function mLookUp(ByVal strFieldName As String, ByVal strTableName As String, Optional ByVal strWhere As String) As Variant
Dim sql As String
sql = "select " & strFieldName & " From " & strTableName
If Len(strWhere) > 0 Then sql = sql & " where " & strWhere
On Error Resume Next
mLookUp = Conn.Execute(sql)(0)
If Err <> 0 Then
      Err.Clear
      mLookUp = Null
End If
End Function

'mMax  替代  AC中的  DMax
Public Function mMax(ByVal strFieldName As String, ByVal strTableName As String, Optional ByVal strWhere As String) As Variant
Dim sql As String
sql = "select Max(" & strFieldName & ") From " & strTableName
If Len(strWhere) > 0 Then sql = sql & " where " & strWhere
On Error Resume Next
mMax = Conn.Execute(sql)(0)
If Err <> 0 Then
      Err.Clear
      mMax = Null
End If
End Function

Public Function OpenConn() As Boolean
On Error Resume Next
Set Conn = CurrentProject.Connection
'Conn.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = \\Server\all_user\TOFuyvn\db1.mdb"
If Conn.State = adStateClosed Then
      OpenConn = False
Else
      OpenConn = True
End If
End Function

測度結果:
   具體數據就不一一例出.    我在群中都有發過.

結論:

如果數據庫在本機上 :    Dlookup 及 DMax  比 mLookup  及 mMax 要快  70% 左右.

如果數據庫在  局域網中的其它電腦上,  將表連接到本機上:    mLookup 及 mMax 比  Dlookup 及 DMax  快5 倍以上.

如果是 ADP  遠程連接 SQL 數據庫:    mLookup 及 mMax 比  Dlookup 及 DMax  快 4 倍以上.

以上只是本人自己測試的結論, 不代表其它人的意見.  只做參考用.

Top

竹笛发表于：2009-03-17 15:47:52

当窗体记录变化时，要重新刷新一下窗体，你的mLookUp函数才会去计算一次，可以用：

me.refresh

Top

蒋海兵发表于：2009-03-17 17:04:50

跟小金鱼帅哥沟通后，使用以下代码可以实现用mlookup替代dlookup的功能，但是速度好像依旧很慢，不知道具体的原因是什么？希望高手指点。

'*******************************************
'函数名:mlookup
'参数:strFieldName-字段名称,strTableName-表名称,strWhere-查询条件
'作用:自定义mlookup函数，用来查找某个字段的值
'示例:mlookup("username","loginuser","userid=12")
'*******************************************
Public Function mLookUp(ByVal strFieldName As String, ByVal strTableName As String, Optional ByVal strWhere As String) As Variant
    Dim sql As String
    sql = "select " & strFieldName & " From " & strTableName
    If Len(strWhere) > 0 Then sql = sql & " where " & strWhere
    Set conn = CurrentProject.Connection
    On Error Resume Next
    mLookUp = conn.Execute(sql)(0)
    If Err <> 0 Then
        Err.Clear
        mLookUp = Null
    End If
    conn.Close
    Set conn = Nothing
End Function

Top

ACMAIN.CHM 发表于：2009-03-17 23:17:54

好奇，于是便也做了个测试。

测试步骤：
1. Windows 2000 SP4 ,打开ACCESS 2003 SP3, 新建空z.mdb

2. 新建module，代码如下,运行createEnv() 建立测试表及记录

Option Compare Database

Option Explicit

Dim conn As ADODB.Connection

Public Sub createEnv()
    Dim sSQL As String
    Dim i As Integer
    
    sSQL = "create table t( id  integer constraint pk_t primary key, f1 integer)"
    CurrentProject.Connection.Execute sSQL
    
    For i = 1 To 10000
        sSQL = "insert into t values(" & i & "," & i * 2 & ")"
        CurrentProject.Connection.Execute sSQL
    Next i
    
End Sub

Public Sub t1() 'test DLOOKUP
    Dim sSQL As String
    Dim i As Integer
    Dim iResult As Integer
    
    Debug.Print Now(), "DLOOKUP Started..."
    
    For i = 1 To 10000
        iResult = DLookup("f1", "t", "id=" & i)
    Next i
    
    Debug.Print Now(), "DLOOKUP Accomplished..."
    
End Sub

Public Sub t2() 'test MLOOKUP
    Dim sSQL As String
    Dim i As Integer
    Dim iResult As Integer
    Set conn = CurrentProject.Connection
    
    Debug.Print Now(), "MLOOKUP Started..."
    
    For i = 1 To 10000
        iResult = mLookUp("f1", "t", "id=" & i)
    Next i
    
    Debug.Print Now(), "MLOOKUP Accomplished..."
End Sub

Public Function mLookUp(ByVal strFieldName As String, ByVal strTableName As String, Optional ByVal strWhere As String) As Variant
    Dim sql As String
    sql = "select " & strFieldName & " From " & strTableName
    If Len(strWhere) > 0 Then sql = sql & " where " & strWhere
    On Error Resume Next
    mLookUp = conn.Execute(sql)(0)
    If Err <> 0 Then
        Err.Clear
        mLookUp = Null
    End If
End Function

3. 关闭系统中无关程序，打开taskmgr.exe, 交叉运行两个T1(), T2() 以防止先后顺序对测试的影响。
结果

3/17/2009 11:01:42 PM       DLOOKUP Started...
3/17/2009 11:01:51 PM       DLOOKUP Accomplished...
3/17/2009 11:02:01 PM       MLOOKUP Started...
3/17/2009 11:02:18 PM       MLOOKUP Accomplished...
3/17/2009 11:02:52 PM       MLOOKUP Started...
3/17/2009 11:03:09 PM       MLOOKUP Accomplished...
3/17/2009 11:03:13 PM       DLOOKUP Started...
3/17/2009 11:03:23 PM       DLOOKUP Accomplished...
3/17/2009 11:04:20 PM       DLOOKUP Started...
3/17/2009 11:04:29 PM       DLOOKUP Accomplished...
3/17/2009 11:04:34 PM       MLOOKUP Started...
3/17/2009 11:04:52 PM       MLOOKUP Accomplished...

**************
* 一切皆有可能 *
**************

ACMAIN - Access论坛回贴准则（个人）

QQ群 48866293 / 12035577 / 7440532 / 13666209
http://www.accessbbs.cn/bbs/index.php
http://www.accessoft.com/bbs/index.asp
http://www.office-cn.net/vvb/?fromuid=141646
http://www.access-programmers.co.uk/forums

http://www.office-cn.net/home/space.php?uid=141646

Top

蒋海兵发表于：2009-03-18 08:19:23

如果按照这个测试结果的话，DLOOKUP的速度反而比MLOOKUP的速度快哦。

Top

朱亦文发表于：2009-03-24 09:54:20

建议你用D系统函数。

主要原因：

1、完善，不会出什么问题

2、它是系统开发时原生的函数，原生函数的实现是用C++实现的，本来它就非常优化，绝对比VBA的解析速度快

在特殊的情况下，要实现特殊的规则，我们才用自己编写的VBA函数。

Top

蒋海兵发表于：2009-03-24 19:02:55

谢谢朱老师，更对ACMAIN0CHM的敬业精神感动，将最佳答案给他。

Top

总记录：6篇页次：1/1 9 1 :

Access交流中心

DLookUp较多影响速度问题求解决方案-论证