概述
设计算法时,可以把部分功能做成一个Function
,这样既可以使代码更易于阅读,又可以进行代码重用。但其会额外开销硬件资源,这就要测一测到底慢多少。
注:在传递参数时,若使用byval
,则要建立参数的副本,对于数据量大的数组,建立副本会很消耗时间,所以建议大家尽量使用byref
。本测试中全部用byref
,以避免建立副本带来的开销。
测试速度的代码
Private Declare Function QueryPerformanceCounter Lib "KERNEL32" (lpPerformanceCount As Currency) As Long
Private Declare Function QueryPerformanceFrequency Lib "KERNEL32" (lpFrequency As Currency) As Long
Private m_Frequency As Currency
Private m_Start As Currency
Private m_Now As Currency
Private m_Available As Boolean
Sub TestSpeet()
m_Available = (QueryPerformanceFrequency(m_Frequency) <> 0)
If Not m_Available Then
Debug.Print "Performance Counter not available"
End If
Dim i As Long
Dim a As Long, b As Long, c As Long
QueryPerformanceCounter m_Start
For i = 1 To 1000000 Step 1
' 下面五句中选一行执行
a = b + c ' 不用函数
a = ToDo0() ' 零个参数
a = ToDo1(a) ' 一个参数
a = ToDo2(a, b) ' 两个参数
a = ToDo3(a, b, c) ' 三个参数
Next i
QueryPerformanceCounter m_Now
Elapsed = 1000 * (m_Now - m_Start) / m_Frequency
Debug.Print Format(Elapsed, "#.0000")
End Sub
Function ToDo0() As Long
ToDo = ToDo + ToDo
End Function
Function ToDo1(a As Long) As Long
ToDo = ToDo + ToDo
End Function
Function ToDo2(a As Long, b As Long) As Long
ToDo = ToDo + ToDo
End Function
Function ToDo3(a As Long, b As Long, c As Long) As Long
测试结果
测试结果的时间单位是毫秒。
测试顺序 | 不用函数 | 零个参数 | 一个参数 | 两个参数 | 三个参数 |
---|---|---|---|---|---|
1 | 8.0002 | 89.9137 | 92.7463 | 98.8352 | 101.6376 |
2 | 8.0506 | 89.6261 | 93.1016 | 98.8160 | 101.3602 |
3 | 7.9891 | 89.8076 | 93.0725 | 99.5525 | 101.3064 |
4 | 8.0750 | 89.8039 | 92.8678 | 98.6769 | 101.3559 |
5 | 8.0058 | 89.9245 | 92.9476 | 99.6873 | 101.1559 |
6 | 8.1066 | 89.4638 | 93.4336 | 98.9042 | 101.2353 |
7 | 7.9712 | 89.8521 | 93.2877 | 98.7279 | 100.8427 |
8 | 8.0732 | 89.4768 | 93.2453 | 99.2764 | 101.1488 |
9 | 8.2652 | 89.7897 | 92.9952 | 98.6070 | 101.5662 |
10 | 7.9956 | 90.2504 | 93.0437 | 99.0390 | 101.6802 |
结论
Function
确实额外开销硬件资源。
100万次调用,消耗了81ms,即1亿次,消耗8s。
所以,对于调用次数不多、时间要求不苛刻的情况,完全可以考虑把整篇的代码分割成多个函数。
反之则要少用。比如两个10万行的表进行笛卡尔积对比,哪怕每次对比仅仅调用一次,那也是10万乘以10万,即一百亿次调用,也就是800s,13min。
我采用了两全其美的办法:把所有数据传入函数,而不是每条数据挨个调用函数。