Access数据库查询十万条数据速度奇慢给出代码求解答

joelongma

第一个问题：数据库数据运算过慢
本人在公司做数据库查询，用access管理基础数据资料，货物发送情况，每个月基本一万条记录，几年的数据基本上就要几十万条，用前后台数据库分的方式，main数据库链接data数据库的基础数据表。主要针对2016年数据进行查询，基本数据条数5万条，每条记录大约有十几个字段。
具体查询代码如下
SELECT *
FROM [2016-测试表] AS a
WHERE ID in (select top 2 ID from [2016-测试表] where ProCate=a.ProCate order by AmountMUSD desc )
ORDER BY a.ProCate, a.AmountMUSD DESC;
第一次查询速度30多秒，此后再查询需要20多秒，请问如何改进或者优化，是用分页，还是需要设计窗体进行改进

第二个问题：数据库合并筛选前两名
同样是这段查询代码，如何实现分组合并后再筛选前几名，比如将产品类别相同（立加、卧加等），来源与某一国别（如：日本、美国、德国等）的进行分组合并，然后针对产品类别内部，进行国别的排序，比如前两名：1、美国$XXXX;2、德国$XXXX。

tmtony

用in肯定会慢
1.尽量不用它看行不行
2.改用exist
3.字段加索引
来自: 微社区

roych

每组选Top 2，对于access来说，确实是一件比较头疼的事情。
如果用查询的话，一般可能需要建立多个查询，或者联合查询，写起来容易出错。另一种方法是使用ADO来处理。

详细见附件。
附上ADO语句

1. Function Topx_ADO(ByVal x As Long)
   
2. 
   
3.     Dim rst1 As New ADODB.Recordset
   
4.     Dim rst2 As New ADODB.Recordset
   
5.     rst1.Open "select distinct ProCate from [2016-测试表]", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   
6.    
   
7.     Do Until rst1.EOF
   
8.         rst2.Open "select top2 from [2016-测试表] where ProCate='" & rst1(0) & "' order by AmountMUSD desc", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
   
9.         
   
10.         Do Until rst2.AbsolutePosition > x
    
11.             top2 = True
    
12.             rst2.MoveNext
    
13.         Loop
    
14.         Debug.Print rst1(0)
    
15.         rst2.Close
    
16.         rst1.MoveNext
    
17.     Loop
    
18.     rst1.Close
    
19. End Function
    
20. 
    
21. '运行test后再筛选top2为True的数据即可。
    
22. 
    
23. Sub test()
    
24.     Call Topx_ADO(2)
    
25. End Sub

复制代码

joelongma

谢谢楼上，还有一个问题没有解决，top2是我为了说明问题简化的，事实上公司操作需要top5,而且还不能有重复项，
就是说
产品为立加的话，按照国别筛选，比如现在就会出现top2都是德国的两个项目，而实践工作中，要把同样国别的进行合并，统一AmountMUSD进行加总，计算立加产品，在德国的合计累计金额，然后再和其他国家进行比较，选择出前几名（可以是5，本例子中假设为2是为了运算方便）

roych

1、这里其实可以top2，也可以top5或者10什么的。主要是读取最前面几条记录，ADO相对快一些。
2、如果真要做成通用模块，则需要更多参数，例如，分组变量——本例里用的procate，排序变量——AmountMUSD，此外可能还需要根据不同的字段类型进行处理。例如，日期格式该怎么写语句，文本格式改怎么写等等。
3、如果要求不能出现重复的，应该先将重复值进行分组。但这是一个很麻烦的事情，可能需要先按排序变量排好，然后每个分组变量只选择一个追加到临时表再进行处理。
4、最后，没有谁会按你个性化的需要做一个例子的。我们更多的时候是给出一个方案，而你，需要做的是，在这思路的基础上进一步完善，以解决你自己的问题。否则即便这次做好了，下次换了一个类似的问题，你一样还是会发帖，对你冰没有什么帮助。