调用子件用途表的存储过程，运行很慢的原因？

cpmodel

VBA代码

1. Sub 按钮1_单击()
   
2. Dim conn As New ADODB.Connection
   
3. Dim rst As ADODB.Recordset
   
4. Dim constr As String
   
5. Dim sql As String
   
6. Dim bm As String
   
7. Dim i, j
   
8. 
   
9. On Error Resume Next
   
10. 
    
11. constr = "Provider = SQLOLEDB;Data Source =JIN;Initial Catalog =UFDATA_666_2013;User ID =sa;Password =jin1668;"
    
12. conn.Open (constr)
    
13. 
    
14. Range("a6:j5000").ClearContents
    
15. 
    
16. Randomize
    
17. 
    
18. s = Int(Rnd * 10000 + 1000)
    
19. ds = "NBUF_TmpReportA" & s
    
20. 
    
21. bm = Trim(Range("b1"))
    
22. If Len(bm) = 0 Then
    
23. Exit Sub
    
24. End If
    
25. 
    
26. sql = "select a.cinvname,a.cinvstd,b.cComUnitName from inventory a left join ComputationUnit b on a.cComUnitCode =b.cComunitcode where a.cinvcode='" & bm & "'"
    
27. 
    
28. Set rst = conn.Execute(sql)
    
29. 
    
30. If rst.BOF = 0 Then
    
31. Range("b2") = rst.Fields(0)
    
32. Range("b3") = rst.Fields(1)
    
33. Range("b4") = rst.Fields(2)
    
34. Else
    
35. MsgBox "不存在的存货编码!"
    
36. Exit Sub
    
37. End If
    
38. 
    
39. Range("e1") = Time()
    
40. 
    
41. [color=Red]sql = "exec Usp_BO_ExpandByComp 'and  1=1  And ((c.InvCode >= ''" & bm & "'') And (c.InvCode <= ''" & bm & "''))'," & " '" & "2099-12-04" & "', 0, 0, 3, 0, 0, 0,1,'', '','" & ds & " '"
    
42. 
    
43. conn.Execute (sql)[/color]
    
44. Range("e2") = Time()
    
45. 
    
46. sql = "select 级别,子件行号,母件编码,母件名称,PItemSpec,母件计量单位,版本代号,版本说明,母件属性,基本用量 from " & ds
    
47. 
    
48. Set rst = conn.Execute(sql)
    
49. 
    
50. Range("e3") = Time()
    
51. 
    
52. Cells(6, 1).CopyFromRecordset rst
    
53. 
    
54. sql = "drop table " & ds
    
55. conn.Execute (sql)
    
56. 
    
57. rst.Close
    
58. rst2.Close
    
59. conn.Close
    
60. 
    
61. Set rst = Nothing
    
62. Set rst2 = Nothing
    
63. Set conn = Nothing
    
64. 
    
65. End Sub
    

复制代码

红色部门运行效率很慢，700多条母件记录要28秒，而用友系统只要2秒，，应该都是用的这个存储过程，这是那么回事，请高手指点？

牙签戳地球

帮楼主顶一下！！！！

wkh55555

帮顶一下，不明白

cpmodel

帮顶一下，不明白