测试查询速度

t小宝 · 发表于 2008-1-14 20:58:28

我们通常需要在一个有分类字段的表中，查询每个分类的最大数量或最小数量，有一个办法是在查询条件中使用域聚合函数，但大家都知道，域聚合函数效率很低，如果有其它可代替的办法就会首先使用其它办法，象这里就可以在条件中使用子查询。而子查询又有两种办法，一种是用SQL 聚合函数max或min，另一种对记录排序然后用TOP 1取第一条。
还是举个例子比较直观，表1中有分类和数量两个字段，有20个分类，共200条记录，要查询每个分类的最大数量，使用子查询有两个方法，SQL语句分别如下：
SELECT 分类, 数量 FROM 表1 AS a WHERE 数量=(Select max(数量) From 表1 Where 分类=a.分类)
SELECT 分类, 数量 FROM 表1 AS a WHERE 数量=(Select top 1 数量 From 表1 Where 分类=a.分类 Order by 数量 desc)
第一句使用max函数，第二句对记录排序后用TOP取第一条。那么这两种方法哪一种更快一点呢（哈，我是个好奇心很重的人）？我进行了测试，分别用上面两个语句打开记录集10000次，看看哪个用时更多一点。

代码如下：
Public Sub max_pk_top()
Dim dbs As DAO.Database
Dim rsmax As DAO.Recordset
Dim rstop As DAO.Recordset

Dim stmax As String
Dim sttop As String
Dim dmx As Date
Dim dtp As Date

Dim i As Integer

Set dbs = CurrentDb

stmax = "SELECT 分类, 数量 FROM 表1 AS a WHERE 数量=(Select max(数量) From 表1 Where 分类=a.分类)"
sttop = "SELECT 分类, 数量 FROM 表1 AS a WHERE 数量=(Select top 1 数量 From 表1 Where 分类=a.分类 Order by 数量 desc)"
dtp = Now()
For i = 0 To 10000
      Set rstop = dbs.OpenRecordset(sttop)
      rstop.Close
Next
dtp = Now() - dtp

dmx = Now()
For i = 0 To 10000
      Set rsmax = dbs.OpenRecordset(stmax)
      rsmax.Close
Next
dmx = Now() - dmx

Debug.Print "max: " & dmx
Debug.Print "mtp: " & dtp

Set rsmax = Nothing
Set rstop = Nothing
Set dbs = Nothing
End Sub

结果是：第一句max用时58秒，第二句top...desc用时67秒。
说明了max比top...desc快一点。
10000次才快那么一点，收获不大。

再来，这次取每个分类的最小数量。
第一句用min代替max，第二句取消desc。
结果是：第一句min用时40秒，第二句top用时45秒。
哈，这次收获多一点了。
min比top快那么一点，
再对比前面，发现取最小值(min)比取最大值(max)快18秒，升序语句(top)比降序语句(top...desc)快22秒。

好象收获还不是很大哦。再继续。。。
把那两个SQL语句生成固定查询，在测试代码中打开固定查询作为记录集。

得到取分类最大数量结果是：max用时30秒，top...desc用时35秒。
分别比用SQL语句打开记录集快了差不多一倍。
取分类最小数量，结果是：min用时13秒，top用时14秒。
竟然分别比用SQL语句快了两倍多。

准备结束了，在这里以提供测试结果为主，大家还可以进一步测试。

也简单总结一下我的测试结果：取最小最大值用SQL聚合函数max和min比用top加排序快；取最小值比取最大值快；使用固定查询比用SQL语句快。