我的程序如下,查询一条记录居然要4秒,(表中一共有500万条记录)
Private Sub Command1_Click()
Dim cardno1, cardno2 As String
Dim balance1, balance2, cardamt1, cardamt2 As Long
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim strdb As Stringd1 = DTPicker1.Value
d2 = DTPicker2.Value
If Text1.Text <> "" Then cardno1 = Text1.Text
If Text2.Text <> "" Then cardno2 = Text2.Text Else cardno2 = cardno1
If Text3.Text <> "" Then balance1 = Text3.Text
If Text4.Text <> "" Then balance2 = Text4.Text Else balance2 = balance1
If Text5.Text <> "" Then cardamt1 = Text5.Text
If Text6.Text <> "" Then cardamt2 = Text6.Text Else cardamt2 = cardamt1'连接数据库
cn.CursorLocation = adUseClient
cn.Open "Provider=MSDAORA.1;Password=mypwd;User ID=me;Data Source=mytab;Persist Security Info=True"'选择SQL
strdb = "select substr(cardcd,8,13) as 卡号,decode(status,'00','未启用','11','启用','33','挂失',status) as 卡状态,balance as 余额,cardamt as 面值, " & vbNewLine
strdb = strdb & " issuebatch as 制卡批次, issuedate as 制卡日期,startdate as 启用日期,canceldate as 注销日期,upddate as 充值日期 from act_usercard " & vbNewLine
strdb = strdb & " where "If Text1.Text <> "" Then
strdb = strdb & " substr(cardcd,8,13) >= " & cardno1 & " and substr(cardcd,8,13) <= " & cardno2 & " and "
End If
If Text3.Text <> "" Then
strdb = strdb & " balance >= " & balance1 & " and balance<= " & balance2 & " and "
End If
If Text5.Text <> "" Then
strdb = strdb & " cardamt>=" & cardamt1 & " and cardamt<=" & cardamt2 & " and "
End If
If Text7.Text <> "" Then
issuebatch = Text7.Text
strdb = strdb & " issuebatch = " & issuebatch & " and "
End If
If Combo1.ListIndex <> 0 Then
If Combo1.ListIndex = 1 Then strdb = strdb & " status = 11 and "
If Combo1.ListIndex = 2 Then strdb = strdb & " status = 00 and "
If Combo1.ListIndex = 3 Then strdb = strdb & " status = 33 and "
End If
If Len(d1) > 0 Then
strdb = strdb & " upddate>=to_date('" & Format(d1, "yyyy-mm-dd") & "','yyyy-mm-dd') and upddate<=to_date('" & Format(d2, "yyyy-mm-dd") & "','yyyy-mm-dd') order by 卡号 "
End If
'Debug.Print strdb
If Right(strdb, 6) = "where " Then
MsgBox "至少选择一项查询条件"
Exit Sub
End If'获取数据
DoEvents
rs.Open strdb, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
If rs.EOF Then
MsgBox "没有数据"
rs.Close
cn.Close
Exit Sub
End If
Set MSHFlexGrid1.DataSource = rsrs.Close
cn.Close
End Sub
怎样优化程序,才能提高速度?如果需要建立索引,请详细说明,本人没有这方面的经验
Private Sub Command1_Click()
Dim cardno1, cardno2 As String
Dim balance1, balance2, cardamt1, cardamt2 As Long
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim strdb As Stringd1 = DTPicker1.Value
d2 = DTPicker2.Value
If Text1.Text <> "" Then cardno1 = Text1.Text
If Text2.Text <> "" Then cardno2 = Text2.Text Else cardno2 = cardno1
If Text3.Text <> "" Then balance1 = Text3.Text
If Text4.Text <> "" Then balance2 = Text4.Text Else balance2 = balance1
If Text5.Text <> "" Then cardamt1 = Text5.Text
If Text6.Text <> "" Then cardamt2 = Text6.Text Else cardamt2 = cardamt1'连接数据库
cn.CursorLocation = adUseClient
cn.Open "Provider=MSDAORA.1;Password=mypwd;User ID=me;Data Source=mytab;Persist Security Info=True"'选择SQL
strdb = "select substr(cardcd,8,13) as 卡号,decode(status,'00','未启用','11','启用','33','挂失',status) as 卡状态,balance as 余额,cardamt as 面值, " & vbNewLine
strdb = strdb & " issuebatch as 制卡批次, issuedate as 制卡日期,startdate as 启用日期,canceldate as 注销日期,upddate as 充值日期 from act_usercard " & vbNewLine
strdb = strdb & " where "If Text1.Text <> "" Then
strdb = strdb & " substr(cardcd,8,13) >= " & cardno1 & " and substr(cardcd,8,13) <= " & cardno2 & " and "
End If
If Text3.Text <> "" Then
strdb = strdb & " balance >= " & balance1 & " and balance<= " & balance2 & " and "
End If
If Text5.Text <> "" Then
strdb = strdb & " cardamt>=" & cardamt1 & " and cardamt<=" & cardamt2 & " and "
End If
If Text7.Text <> "" Then
issuebatch = Text7.Text
strdb = strdb & " issuebatch = " & issuebatch & " and "
End If
If Combo1.ListIndex <> 0 Then
If Combo1.ListIndex = 1 Then strdb = strdb & " status = 11 and "
If Combo1.ListIndex = 2 Then strdb = strdb & " status = 00 and "
If Combo1.ListIndex = 3 Then strdb = strdb & " status = 33 and "
End If
If Len(d1) > 0 Then
strdb = strdb & " upddate>=to_date('" & Format(d1, "yyyy-mm-dd") & "','yyyy-mm-dd') and upddate<=to_date('" & Format(d2, "yyyy-mm-dd") & "','yyyy-mm-dd') order by 卡号 "
End If
'Debug.Print strdb
If Right(strdb, 6) = "where " Then
MsgBox "至少选择一项查询条件"
Exit Sub
End If'获取数据
DoEvents
rs.Open strdb, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
If rs.EOF Then
MsgBox "没有数据"
rs.Close
cn.Close
Exit Sub
End If
Set MSHFlexGrid1.DataSource = rsrs.Close
cn.Close
End Sub
怎样优化程序,才能提高速度?如果需要建立索引,请详细说明,本人没有这方面的经验
解决方案 »
- 打酱油打酱油,你们就没想过酱油他妈的感受?
- 移动图片时,我用了如下代码为什么用坐标定位在图片上的shape控件的移动距离不正确??
- 字符串值"10-2"输出到Excel变成了"10月2日"?请教各位解决办法?
- 使用数据控件和数据绑定控件的问题
- 控件数组作为过程的参数,怎么不行呢?
- 请问如何学习vb中的api
- 关于 数据环境设计器 的简单问题! ~~~~~急用
- 读取二进制发生错误
- 使用ADO打开两个表(join),为什么不能update?题是错误:缺少更新或刷新的键列信息。(800040005)
- 高分求解:怎样得到一个未知对象的所有属性或方法????
- 请问,vb6如何调用oracle的存储过程,函数,请给个例子参考,谢谢
- 一个程序中要对同一个窗体使用两次
数据量大就需要冗余字段,否则每次查询数据库就要做 500万次 substr(cardcd,8,13) 运算,怎么会不慢。