其实操作很简单,但是数据量很大,所以必须考虑操作的效率问题。
程序的目的是为了从前一天的纪录中取出BALANCEDAY值,经过规定计算把结果插入当天的纪录中。每天的数据量超过12000条,需要1个小时才能算完。效率很低。而且开始很快,头2000条纪录不到1分钟,后来越来越慢。这是为什么?
代码如下:(其中考虑了跨表操作的问题)Dim Cnn As New ADODB.Connection
Dim Cnnf As New ADODB.Connection
Dim Cnnd As New ADODB.Connection
Dim yesterdayye As Double '上日BALANCEDAY值
Dim x As Long'计算BALANCEDAY值函数
Private Function addrjye(ByVal a1 As Double, ByVal a2 As Double, ByVal a3 As Double, ByVal a4 As String, ByVal a5 As Integer) As Double
If a4 = "1" Then
yue = (a1 + (a2 - a3 - a1) / (a5))
Else
yue = (a1 + (a3 - a2 - a1) / (a5))
End If
addrjye = yue
End Function
Private Sub Command1_Click()
x = 1
y = 1
Dim N As Long
Dim LineText As String
Dim LineTexts As String
Dim LineTexte As String
Dim AllText As String
Open (App.Path + "/start.ini") For Input As #1N = 1
Do Until EOF(1)
Line Input #1, LineText
If N = 1 Then
LineTexts = LineText
End If
If N = 2 Then
LineTexte = LineText
End If
N = N + 1
Loop
Close #1'再此处加日期合法性的判断
bj = 0If (Combo1 = "起始年") Or (Combo2 = "起始月") Or (Combo3 = "起始日") Or (Combo4 = "截止年") Or (Combo5 = "截止月") Or (Combo6 = "截止日") Then
bj = 1
ElseIf (CDate(Combo1 & "-" & Combo2 & "-" & Combo3) < CDate(LineTexts)) Or (CDate(Combo1 & "-" & Combo2 & "-" & Combo3) > CDate(LineTexte)) Then
bj = 1
ElseIf (CDate(Combo1 & "-" & Combo2 & "-" & Combo3) > CDate(Combo4 & "-" & Combo5 & "-" & Combo6)) Then
bj = 1
End IfIf bj = 1 Then
MsgBox ("请选择正确日期!")
Else
'取得起始日期和截止日期firstday = (Combo1 & "-" & Combo2 & "-" & Combo3)
'特定的起始日期,作特殊的处理
'------------------------------------------------
If firstday = "2002-12-01" Then
bj = 2
End If
'------------------------------------------------
nextday = firstday
lastday = (Combo4 & "-" & Combo5 & "-" & Combo6)Forwardy = CStr(CDate(nextday) - 1)If Len(Forwardy) <> 10 Then
fm = Right("0" + CStr(Month(CDate(nextday))), 2)
fd = Right("0" + CStr(Day(CDate(nextday))), 2)
fy = CStr(Year(CDate(nextday) + 1))
Forwardy = fy + "-" + fm + "-" + fd
End Ifi = CDate(lastday) - CDate(firstday)
'求出共有多少天需要循环For j = 0 To iY1 = 0
'在此处进行日期的循环
'-------------------------------------------------------------------
'打开数据库
'-------------------------------------------------------------------
If bj <> 2 Then
t = Left(Forwardy, 7)
yea = Left(t, 4)
mon = Right(t, 2)
SQLf = "select WORKDATE,BRNO,CURRTYPE,SUBNO,SUBNATURE,TDDRBAL,TDCRBAL,WORKDAY,BALANCEDAY From NFGNLED" & yea + mon & " where WORKDATE='" & Forwardy & "' order by BRNO,CURRTYPE,SUBNO desc "
Dim rsf As New ADODB.Recordset
Cnnf.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=icbc;Persist Security Info=True;User ID=icbc;Data Source=meyes"
Cnnf.Open
rsf.Open SQLf, Cnnf, 1, 1SQLd = "select WORKDATE,BRNO,CURRTYPE,SUBNO,SUBNATURE,TDDRBAL,TDCRBAL,WORKDAY,BALANCEDAY From NFGNLED" & yea + mon & " where WORKDATE='" & Forwardy & "' order by BRNO,CURRTYPE,SUBNO asc "
Dim rsd As New ADODB.Recordset
Cnnd.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=icbc;Persist Security Info=True;User ID=icbc;Data Source=meyes"
Cnnd.Open
rsd.Open SQLd, Cnnd, 1, 1End If
'-------------------------------------------------------------------
t = Left(nextday, 7)
yea = Left(t, 4)
mon = Right(t, 2)
SQL = "select WORKDATE,BRNO,CURRTYPE,SUBNO,SUBNATURE,TDDRBAL,TDCRBAL,WORKDAY,BALANCEDAY From NFGNLED" & yea + mon & " where WORKDATE='" & nextday & "' order by BRNO,CURRTYPE,SUBNO desc "
Dim rs As New ADODB.Recordset
Cnn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=icbc;Persist Security Info=True;User ID=icbc;Data Source=meyes"
Cnn.Open
rs.Open SQL, Cnn, 1, 2If rs.EOF Then
MsgBox ("没有符合条件的数据!")Else'处理某一天的所有数据
List1.AddItem ("处理日期:" & nextday & "开始")
List1.ListIndex = x
x = x + 1
'mm = 0
While Not rs.EOF 'And mm < 10
WORKDATE = rs.Fields("WORKDATE")
BRNO = rs.Fields("BRNO")
CURRTYPE = rs.Fields("CURRTYPE")
SUBNO = rs.Fields("SUBNO")
SUBNATURE = rs.Fields("SUBNATURE")
TDDRBAL = rs.Fields("TDDRBAL")
TDCRBAL = rs.Fields("TDCRBAL")
Forwarddays = rs.Fields("WORKDAY") '------------------------------------------------------------
'搜索上日的
yesterdayye = 0
'加上了对特殊日期的判断
If bj <> 2 And y <= 6000 ThenDo While Not rsf.EOF
If rsf.Fields("BRNO") = BRNO And rsf.Fields("CURRTYPE") = CURRTYPE And rsf.Fields("SUBNO") = SUBNO Then
yesterdayye = rsf.Fields("BALANCEDAY")
Exit Do
Else
yesterdayye = 0
End If
rsf.MoveNext
Loop
rsf.MoveFirstElseIf bj <> 2 And y > 6000 ThenDo While Not rsd.EOF
If rsd.Fields("BRNO") = BRNO And rsd.Fields("CURRTYPE") = CURRTYPE And rsd.Fields("SUBNO") = SUBNO Then
yesterdayye = rsd.Fields("BALANCEDAY")
Exit Do
Else
yesterdayye = 0
End If
rsd.MoveNext
Loop
rsd.MoveFirstEnd If
'------------------------------------------------------------
rs.Fields("BALANCEDAY") = addrjye(yesterdayye, TDCRBAL, TDDRBAL, SUBNATURE, Forwarddays)
rs.Update
List1.AddItem ("处理纪录数=" & Y1)
Y1 = Y1 + 1
List1.ListIndex = x
x = x + 1
rs.MoveNext
'mm = mm + 1
WendEnd IfList1.AddItem ("处理日期:" & nextday & "完毕")
List1.ListIndex = x
x = x + 1
LineTexte = nextday'求下一天的日期(有问题)
Forwardy = nextday
nextday = CStr(CDate(nextday) + 1)If Len(nextday) <> 10 Then
m = Right("0" + CStr(Month(CDate(nextday))), 2)
d = Right("0" + CStr(Day(CDate(nextday))), 2)
y = CStr(Year(CDate(nextday) + 1))
nextday = y + "-" + m + "-" + d
End If
Set rs = Nothing
Cnn.Close
'加上了对特殊日期的判断-------------------
If bj <> 2 Then
Set rsf = Nothing
Cnnf.Close
Set rsd = Nothing
Cnnd.Close
End If
bj = 0 '将特殊日期的标志修改过来
'----------------------------------------------------
'此处为日期循环的结束
NextEnd If
AllText = LineTexts & Chr(13) & LineTexte
List1.AddItem ("处理完毕!")
List1.ListIndex = x
x = x + 1
Open (App.Path & "/start.ini") For Output As #2
Print #2, AllText
Close #2
End Sub-------------------------------------------------------------
先给100分,不够再开帖子。
程序的目的是为了从前一天的纪录中取出BALANCEDAY值,经过规定计算把结果插入当天的纪录中。每天的数据量超过12000条,需要1个小时才能算完。效率很低。而且开始很快,头2000条纪录不到1分钟,后来越来越慢。这是为什么?
代码如下:(其中考虑了跨表操作的问题)Dim Cnn As New ADODB.Connection
Dim Cnnf As New ADODB.Connection
Dim Cnnd As New ADODB.Connection
Dim yesterdayye As Double '上日BALANCEDAY值
Dim x As Long'计算BALANCEDAY值函数
Private Function addrjye(ByVal a1 As Double, ByVal a2 As Double, ByVal a3 As Double, ByVal a4 As String, ByVal a5 As Integer) As Double
If a4 = "1" Then
yue = (a1 + (a2 - a3 - a1) / (a5))
Else
yue = (a1 + (a3 - a2 - a1) / (a5))
End If
addrjye = yue
End Function
Private Sub Command1_Click()
x = 1
y = 1
Dim N As Long
Dim LineText As String
Dim LineTexts As String
Dim LineTexte As String
Dim AllText As String
Open (App.Path + "/start.ini") For Input As #1N = 1
Do Until EOF(1)
Line Input #1, LineText
If N = 1 Then
LineTexts = LineText
End If
If N = 2 Then
LineTexte = LineText
End If
N = N + 1
Loop
Close #1'再此处加日期合法性的判断
bj = 0If (Combo1 = "起始年") Or (Combo2 = "起始月") Or (Combo3 = "起始日") Or (Combo4 = "截止年") Or (Combo5 = "截止月") Or (Combo6 = "截止日") Then
bj = 1
ElseIf (CDate(Combo1 & "-" & Combo2 & "-" & Combo3) < CDate(LineTexts)) Or (CDate(Combo1 & "-" & Combo2 & "-" & Combo3) > CDate(LineTexte)) Then
bj = 1
ElseIf (CDate(Combo1 & "-" & Combo2 & "-" & Combo3) > CDate(Combo4 & "-" & Combo5 & "-" & Combo6)) Then
bj = 1
End IfIf bj = 1 Then
MsgBox ("请选择正确日期!")
Else
'取得起始日期和截止日期firstday = (Combo1 & "-" & Combo2 & "-" & Combo3)
'特定的起始日期,作特殊的处理
'------------------------------------------------
If firstday = "2002-12-01" Then
bj = 2
End If
'------------------------------------------------
nextday = firstday
lastday = (Combo4 & "-" & Combo5 & "-" & Combo6)Forwardy = CStr(CDate(nextday) - 1)If Len(Forwardy) <> 10 Then
fm = Right("0" + CStr(Month(CDate(nextday))), 2)
fd = Right("0" + CStr(Day(CDate(nextday))), 2)
fy = CStr(Year(CDate(nextday) + 1))
Forwardy = fy + "-" + fm + "-" + fd
End Ifi = CDate(lastday) - CDate(firstday)
'求出共有多少天需要循环For j = 0 To iY1 = 0
'在此处进行日期的循环
'-------------------------------------------------------------------
'打开数据库
'-------------------------------------------------------------------
If bj <> 2 Then
t = Left(Forwardy, 7)
yea = Left(t, 4)
mon = Right(t, 2)
SQLf = "select WORKDATE,BRNO,CURRTYPE,SUBNO,SUBNATURE,TDDRBAL,TDCRBAL,WORKDAY,BALANCEDAY From NFGNLED" & yea + mon & " where WORKDATE='" & Forwardy & "' order by BRNO,CURRTYPE,SUBNO desc "
Dim rsf As New ADODB.Recordset
Cnnf.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=icbc;Persist Security Info=True;User ID=icbc;Data Source=meyes"
Cnnf.Open
rsf.Open SQLf, Cnnf, 1, 1SQLd = "select WORKDATE,BRNO,CURRTYPE,SUBNO,SUBNATURE,TDDRBAL,TDCRBAL,WORKDAY,BALANCEDAY From NFGNLED" & yea + mon & " where WORKDATE='" & Forwardy & "' order by BRNO,CURRTYPE,SUBNO asc "
Dim rsd As New ADODB.Recordset
Cnnd.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=icbc;Persist Security Info=True;User ID=icbc;Data Source=meyes"
Cnnd.Open
rsd.Open SQLd, Cnnd, 1, 1End If
'-------------------------------------------------------------------
t = Left(nextday, 7)
yea = Left(t, 4)
mon = Right(t, 2)
SQL = "select WORKDATE,BRNO,CURRTYPE,SUBNO,SUBNATURE,TDDRBAL,TDCRBAL,WORKDAY,BALANCEDAY From NFGNLED" & yea + mon & " where WORKDATE='" & nextday & "' order by BRNO,CURRTYPE,SUBNO desc "
Dim rs As New ADODB.Recordset
Cnn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=icbc;Persist Security Info=True;User ID=icbc;Data Source=meyes"
Cnn.Open
rs.Open SQL, Cnn, 1, 2If rs.EOF Then
MsgBox ("没有符合条件的数据!")Else'处理某一天的所有数据
List1.AddItem ("处理日期:" & nextday & "开始")
List1.ListIndex = x
x = x + 1
'mm = 0
While Not rs.EOF 'And mm < 10
WORKDATE = rs.Fields("WORKDATE")
BRNO = rs.Fields("BRNO")
CURRTYPE = rs.Fields("CURRTYPE")
SUBNO = rs.Fields("SUBNO")
SUBNATURE = rs.Fields("SUBNATURE")
TDDRBAL = rs.Fields("TDDRBAL")
TDCRBAL = rs.Fields("TDCRBAL")
Forwarddays = rs.Fields("WORKDAY") '------------------------------------------------------------
'搜索上日的
yesterdayye = 0
'加上了对特殊日期的判断
If bj <> 2 And y <= 6000 ThenDo While Not rsf.EOF
If rsf.Fields("BRNO") = BRNO And rsf.Fields("CURRTYPE") = CURRTYPE And rsf.Fields("SUBNO") = SUBNO Then
yesterdayye = rsf.Fields("BALANCEDAY")
Exit Do
Else
yesterdayye = 0
End If
rsf.MoveNext
Loop
rsf.MoveFirstElseIf bj <> 2 And y > 6000 ThenDo While Not rsd.EOF
If rsd.Fields("BRNO") = BRNO And rsd.Fields("CURRTYPE") = CURRTYPE And rsd.Fields("SUBNO") = SUBNO Then
yesterdayye = rsd.Fields("BALANCEDAY")
Exit Do
Else
yesterdayye = 0
End If
rsd.MoveNext
Loop
rsd.MoveFirstEnd If
'------------------------------------------------------------
rs.Fields("BALANCEDAY") = addrjye(yesterdayye, TDCRBAL, TDDRBAL, SUBNATURE, Forwarddays)
rs.Update
List1.AddItem ("处理纪录数=" & Y1)
Y1 = Y1 + 1
List1.ListIndex = x
x = x + 1
rs.MoveNext
'mm = mm + 1
WendEnd IfList1.AddItem ("处理日期:" & nextday & "完毕")
List1.ListIndex = x
x = x + 1
LineTexte = nextday'求下一天的日期(有问题)
Forwardy = nextday
nextday = CStr(CDate(nextday) + 1)If Len(nextday) <> 10 Then
m = Right("0" + CStr(Month(CDate(nextday))), 2)
d = Right("0" + CStr(Day(CDate(nextday))), 2)
y = CStr(Year(CDate(nextday) + 1))
nextday = y + "-" + m + "-" + d
End If
Set rs = Nothing
Cnn.Close
'加上了对特殊日期的判断-------------------
If bj <> 2 Then
Set rsf = Nothing
Cnnf.Close
Set rsd = Nothing
Cnnd.Close
End If
bj = 0 '将特殊日期的标志修改过来
'----------------------------------------------------
'此处为日期循环的结束
NextEnd If
AllText = LineTexts & Chr(13) & LineTexte
List1.AddItem ("处理完毕!")
List1.ListIndex = x
x = x + 1
Open (App.Path & "/start.ini") For Output As #2
Print #2, AllText
Close #2
End Sub-------------------------------------------------------------
先给100分,不够再开帖子。
把
Do While Not rsf.EOF
If rsf.Fields("BRNO") = BRNO And rsf.Fields("CURRTYPE") = CURRTYPE And rsf.Fields("SUBNO") = SUBNO Then
yesterdayye = rsf.Fields("BALANCEDAY")
Exit Do
Else
yesterdayye = 0
End If
rsf.MoveNext
Loop
rsf.MoveFirst改
为:
set rsf=cnn.Excute ( "select BALANCEDAY from NFGNLED" & yea + mon & " where BRNO=" & BRNO & " CURRTYPE=" & CURRTYPE &.......
if rsf.eof then
yesterdayye = 0
else
yesterdayye = rsf!BALANCEDAY
end if
set rsf=nothing
If bj <> 2 Then
' 这里是对所查询的表名选择,也不必理会
t = Left(Forwardy, 7)
yea = Left(t, 4)
mon = Right(t, 2)
' 这里是对前一日所有数据的查询,并依据 BRNO,CURRTYPE,SUBNO ,分别以逆序和正序而排序。建立结果集rsf建立结果集rsdEnd If
'-------------------------------------------------------------------
'这里也是依据表明选择建立当日的结果集建立结果集rsIf rs.EOF Then
MsgBox ("没有符合条件的数据!")Else'处理某一天的所有数据While Not rs.EOF '从每一条数据中取出相应字段,作为比较和计算的参数WORKDATE = rs.Fields("WORKDATE")
BRNO = rs.Fields("BRNO")
CURRTYPE = rs.Fields("CURRTYPE")
SUBNO = rs.Fields("SUBNO")
SUBNATURE = rs.Fields("SUBNATURE")
TDDRBAL = rs.Fields("TDDRBAL")
TDCRBAL = rs.Fields("TDCRBAL")
Forwarddays = rs.Fields("WORKDAY") '------------------------------------------------------------
'搜索上日的
yesterdayye = 0
'加上了对特殊日期的判断,不必理会
'6000是因为每一天的数据大约是12000——14000条左右,前面建立的rsf和rsd就是为了在超过6000条时从顺序相反的另一个结果集查找。这是我自作聪明的结果,其实没什么效果。郁闷。
If bj <> 2 And y <= 6000 Then'在rsf中循环比较Do While Not rsf.EOF
If rsf.Fields("BRNO") = BRNO And rsf.Fields("CURRTYPE") = CURRTYPE And rsf.Fields("SUBNO") = SUBNO Then
yesterdayye = rsf.Fields("BALANCEDAY")
Exit Do
Else
yesterdayye = 0
End If
rsf.MoveNext
Loop
rsf.MoveFirstElseIf bj <> 2 And y > 6000 Then'在rsd中循环比较Do While Not rsd.EOF
If rsd.Fields("BRNO") = BRNO And rsd.Fields("CURRTYPE") = CURRTYPE And rsd.Fields("SUBNO") = SUBNO Then
yesterdayye = rsd.Fields("BALANCEDAY")
Exit Do
Else
yesterdayye = 0
End If
rsd.MoveNext
Loop
rsd.MoveFirstEnd If
'------------------------------------------------------------
'addrjye是计算函数,不必理会,只是一个返回的计算结果,不影响效率rs.Fields("BALANCEDAY") = addrjye(yesterdayye, TDCRBAL, TDDRBAL, SUBNATURE, Forwarddays)
rs.Update
rs.MoveNext
WendEnd If
'求下一天的日期,作为循环依据,下面的代码只是为了处理日期的循环,可以不必理会Forwardy = nextday
nextday = CStr(CDate(nextday) + 1)If Len(nextday) <> 10 Then
m = Right("0" + CStr(Month(CDate(nextday))), 2)
d = Right("0" + CStr(Day(CDate(nextday))), 2)
y = CStr(Year(CDate(nextday) + 1))
nextday = y + "-" + m + "-" + d
End If
Set rs = Nothing
Cnn.Close
'加上了对特殊日期的判断-------------------
If bj <> 2 Then
Set rsf = Nothing
Cnnf.Close
Set rsd = Nothing
Cnnd.Close
End If
bj = 0 '将特殊日期的标志修改过来
'----------------------------------------------------
'此处为日期循环的结束