终于做成了从外网访问公司里的SQL2000,太高兴了! 但速度太慢!给个我写的程序给大家看下,能否指出慢的原因,速度差了10~20倍! 谢 不服气将每个工作的时间作了记录,如下: Private Sub UserForm_Initialize() '初始化 On Error GoTo 9 Dim arr, StrSql$, i&, j& Dim ooo!, aaa!, Msg$ aaa = Timer: ooo = Timer 单号 = "" '确保不要出现以前的情况砂 If CNN.State = 0 Then CNN.Open pthStr1 '找出最近一星期内完成的客户及单号工程及状态 Msg = Msg & "\连接时间:" & Format(Timer - ooo, "0.00") & "S" & Chr(10) ooo = Timer StrSql = " Select 客户 ,单号 ,输入日期 ,工程,总片数,总面积 ,完成日期 from 单资料 WHERE 完成日期>'" & Date - 7 & "' or 完成日期 is null " Set RS1 = Nothing Set RS1 = CreateObject("ADODB.Recordset") '目标:不用引用! Msg = Msg & "\定义记录集时间:" & Format(Timer - ooo, "0.00") & "S" & Chr(10) ooo = Timer RS1.Open StrSql, CNN, 1, 1, 1 Msg = Msg & "\打开记录集时间:" & Format(Timer - ooo, "0.00") & "S" & Chr(10) ooo = Timer Dim Mycollection As New Collection, A '将唯一性加入 Arr_Row = RS1.RecordCount Arr_Col = RS1.Fields.Count ReDim Arr_rs(0 To Arr_Row, 1 To Arr_Col) Msg = Msg & "\定义集合与数组的时间:" & Format(Timer - ooo, "0.00") & "S" & Chr(10) ooo = Timer For j = 1 To Arr_Col: Arr_rs(0, j) = RS1.Fields(j - 1).Name: Next '记录表头 Msg = Msg & "\产生表头的时间:" & Format(Timer - ooo, "0.00") & "S" & Chr(10) ooo = Timer For i = 1 To Arr_Row For j = 1 To Arr_Col If IsNull(RS1.Fields(j - 1)) Then Arr_rs(i, j) = "" Else Arr_rs(i, j) = RS1.Fields(j - 1) Next RS1.MoveNext Next Set RS1 = Nothing Msg = Msg & "\将记录集存放在数组内的时间:" & Format(Timer - ooo, "0.00") & "S" & Chr(10) ooo = Timer On Error Resume Next For i = 1 To Arr_Row A = Arr_rs(i, 1) Mycollection.Add A, CStr(A) Next Err.Clear: On Error GoTo 0 Msg = Msg & "\形成集合唯一性的时间:" & Format(Timer - ooo, "0.00") & "S" & Chr(10) ooo = Timer ReDim arr(1 To Mycollection.Count, 1 To 2) For i = 1 To UBound(arr) arr(i, 1) = Mycollection.Item(i) arr(i, 2) = 1 Next Set Mycollection = Nothing Call Initpage(arr) TreeView1.Sorted = True '排列顺序 TreeView1.SetFocus Msg = Msg & "\形成树形的时间:" & Format(Timer - ooo, "0.00") & "S" & Chr(10) MsgBox Msg & "总时间为:" & Format(Timer - aaa, "0.00") & "S OK 可以查询了!" Exit Sub 9: MsgBox Err.Description & " 多是上不了网的问题,再试试吧" End Sub
一直以为是网速的问题,于是就将所有的过程都加了一个计时器,最后发现,最耗时间的地方是在 For i = 1 To Arr_Row For j = 1 To Arr_Col If IsNull(RS1.Fields(j - 1)) Then Arr_rs(i, j) = "" Else Arr_rs(i, j) = RS1.Fields(j - 1) Next RS1.MoveNext Next Set RS1 = Nothing Msg = Msg & "\将记录集存放在数组内的时间:" & Format(Timer - ooo, "0.00") & "S" & Chr(10) ooo = Timer 总时间在28S的初始化工作,却在上面的花了23S,与想象的网速完全不是一回事!
即如下面兄弟说的用了 getrows 也不能快点点 再请高手们指示一下 多谢了
[此贴子已经被作者于2007-6-26 10:47:08编辑过] |