Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim ObjRS As ADODB.Recordset
Dim ssql As String
Dim strCnn As String
Dim strID As String
dim x as Integer
dim total,total2 as double
'Open ADO connection
strCnn = "driver={SQL Server};database=test;server=192.168.1.252;UID=nmkspro;PWD=123;"
Set cnn = New ADODB.Connection
cnn.Open strCnn
Set ObjRS = New ADODB.Recordset
for x=0 to x<100
ssql = "SELECT sum(fs) as total2 FROM TABLE1 where 班号=x"
ObjRS.Open ssql, cnn
total1=total2+total1
ObjRS.close
set ObjRS=nothing
next x
msgbox total1
End Sub请问如果TABLE1的数据量有上百万条记录,在循环内每次 ObjRS.Open 都要ObjRS.CLOSE ,是不是速度会很慢,如果每次循环一定要做重新查询,除了重新打开表,还有别的办法解决吗?
Dim cnn As ADODB.Connection
Dim ObjRS As ADODB.Recordset
Dim ssql As String
Dim strCnn As String
Dim strID As String
dim x as Integer
dim total,total2 as double
'Open ADO connection
strCnn = "driver={SQL Server};database=test;server=192.168.1.252;UID=nmkspro;PWD=123;"
Set cnn = New ADODB.Connection
cnn.Open strCnn
Set ObjRS = New ADODB.Recordset
for x=0 to x<100
ssql = "SELECT sum(fs) as total2 FROM TABLE1 where 班号=x"
ObjRS.Open ssql, cnn
total1=total2+total1
ObjRS.close
set ObjRS=nothing
next x
msgbox total1
End Sub请问如果TABLE1的数据量有上百万条记录,在循环内每次 ObjRS.Open 都要ObjRS.CLOSE ,是不是速度会很慢,如果每次循环一定要做重新查询,除了重新打开表,还有别的办法解决吗?
看看他可以减少点
...
ssql = "SELECT sum(fs) as total2 FROM TABLE1"
ObjRS.Open ssql, cnn
msgbox ObjRS!total2
for x=0 to 99
strSQL = strSQL & "SELECT sum(fs) as total2 FROM TABLE1 where 班号=" & x & ";"
next x
rst.open strSQL, cnn, , , adCmdText
do until rst.State = adStateClosed
total1 = total1 + rst!total2
set rst = rst.NextRecordset
loop
msgbox total1