在EXCEL中怎样提高查询速度？

kangking

同样的一个查询句，在ACCESS中执行的时候只要1到2秒，但在EXCEL中执行的时候要约4分钟，请问有什么办法能够提高在EXCEL中的查询速度吗？

pureshadow

什么查询句要4分钟呀？代码怎么写的？

roych

ADO应该也不至于这么慢呀~~

kangking

感谢楼上两位版主的回复。稍后我将示例传上。

kangking

现将实例简化后贴上。今天快一点，但也要约两分钟。
请版主关心一下，看看有没有可能提高速度。

kangking

pureshadow 发表于 2012-2-24 09:09
什么查询句要4分钟呀？代码怎么写的？

已经上传附件，请看一下。

kangking

roych 发表于 2012-2-24 11:40
ADO应该也不至于这么慢呀~~

已经上传附件，请看一下。

roych

kangking 发表于 2012-2-24 12:51
已经上传附件，请看一下。

将以下语句改成INNER JOIN会好很多：
(select sum(计次) from [CARD_DETAIL$] as B where (B.证类型='证件七' or  B.证类型='证件八') and (trim(B.单位)=trim(A.单位)) and (trim(B.个人编号)=trim(A.个人编号)) and (trim(B.姓名)=trim(A.姓名)) group by trim(b.单位),trim(b.个人编号),trim(b.姓名)) AS 分类一
例如：
SELECT [CARD_DETAIL$].个人编号, Sum([CARD_DETAIL$].计次) AS 计次, "类型一" AS 类型一
FROM [CARD_DETAIL$] INNER JOIN [CARD_DETAIL$] AS [CARD_DETAIL$]_1 ON ([CARD_DETAIL$].姓名 = [CARD_DETAIL$]_1.姓名) AND ([CARD_DETAIL$].个人编号 = [CARD_DETAIL$]_1.个人编号) AND ([CARD_DETAIL$].单位 = [CARD_DETAIL$]_1.单位)
GROUP BY [CARD_DETAIL$].个人编号, "类型一", [CARD_DETAIL$].证类型
HAVING ((([CARD_DETAIL$].证类型)="证件七" Or ([CARD_DETAIL$].证类型)="证件八"));

由于时间关系（赶车回广州），可能暂时没法帮你整了。如果仍然没法解决，周一回来再看看。

kangking

roych 发表于 2012-2-24 16:24
将以下语句改成INNER JOIN会好很多：
(select sum(计次) from [CARD_DETAIL$] as B where (B.证类型=' ...

感谢回复！
按你的思路，就是在ACCESS中，我试了一下，速度比原来的查询慢。
（我的查询句，稍有点复杂的都是先在ACCESS中进行验证的。）

roych

kangking 发表于 2012-2-28 10:13
感谢回复！
按你的思路，就是在ACCESS中，我试了一下，速度比原来的查询慢。
（我的查询句，稍有点复杂 ...

这里改成了交叉表查询后测试完成时间是1s，——因为交叉表列字段的问题，所以加上了调整字段顺序的Excel操作。
1s

1. sSql = "TRANSFORM Sum(A.计次) AS 计次 SELECT A.单位, A.个人编号,A.姓名,Sum(Int((A.人次*1.6-A.金额)/0.8)) AS 分类四," & _
   
2.         "Sum(A.人次-Int((A.人次*1.6-A.金额)/0.8)) AS 分类五, Sum(A.金额) AS 金额" & _
   
3.         " FROM [CARD_DETAIL$] AS A GROUP BY A.单位, A.个人编号, A.姓名" & _
   
4.         " PIVOT IIf(A.证类型='证件七' Or A.证类型='证件八','分类一'," & _
   
5.         " IIf(A.证类型='证件一' Or A.证类型='证件五','分类二', " & _
   
6.         " IIf(A.证类型='证件三' Or A.证类型='证件九','分类三'))) In ('分类一','分类二','分类三')"
   
7.     Set oRs = New ADODB.Recordset
   
8.     oRs.Open sSql, oConn, adOpenKeyset, adLockOptimistic
   
9.     Sheets("DataGether").Select
   
10.     For i = 0 To oRs.Fields.Count - 1
    
11.         Cells(1, i + 1) = oRs.Fields(i).Name
    
12.     Next
    
13.     Range("a2").CopyFromRecordset oRs
    
14.     Columns("D:F").Select
    
15.     Selection.Cut
    
16.     Range("J1").Select
    
17.     Selection.Insert Shift:=xlToRight

复制代码

此外，如果用记录集来调整字段位置，则时间较长（在我这里测试是11s），大体代码如下：

1.     Cells(1, 1) = oRs(0).Name
   
2.     Cells(1, 2) = oRs(1).Name
   
3.     Cells(1, 3) = oRs(2).Name
   
4.     Cells(1, 4) = oRs(6).Name
   
5.     Cells(1, 5) = oRs(7).Name
   
6.     Cells(1, 6) = oRs(8).Name
   
7.     Cells(1, 7) = oRs(3).Name
   
8.     Cells(1, 8) = oRs(4).Name
   
9.     Cells(1, 9) = oRs(5).Name
   
10.     For i = 1 To oRs.RecordCount
    
11.             Cells(i + 1, 1) = oRs(0)
    
12.             Cells(i + 1, 2) = oRs(1)
    
13.             Cells(i + 1, 3) = oRs(2)
    
14.             Cells(i + 1, 4) = oRs(6)
    
15.             Cells(i + 1, 5) = oRs(7)
    
16.             Cells(i + 1, 6) = oRs(8)
    
17.             Cells(i + 1, 7) = oRs(3)
    
18.             Cells(i + 1, 8) = oRs(4)
    
19.             Cells(i + 1, 9) = oRs(5)
    
20.         oRs.MoveNext
    
21.     Next

复制代码