56,618
社区成员




大侠们,我用VB做的一个MYSQL信息系统,在X表中查询在职人员,列出所有名字,有2000个,然后在Y表中查询每个人的出勤记录,Y表中有10万条数据,我需要查询每个人在Y表中符合要求的一条数据,我使用了以下代码,查询的速度非常慢,这个语句是否可以优化,从网上找了一些资料,比如建立索引之类的,我都不知道该怎么做,请大侠解答疑惑!
Set myconn = New ADODB.Connection
myconn.ConnectionString = strZCSJK
myconn.Open
Set myrecord = New ADODB.Recordset
mysql = "select * from dataX"
myrecord.Open mysql, myconn, adOpenDynamic, adLockOptimistic, adCmdText
If myrecord.EOF = False Then
strText = "在职"
Set myrecord = New ADODB.Recordset
mysql = "select * from dataX WHERE (dataX.X1 like " & Chr(34) & strText & Chr(34) & ") order by ID" ‘在X表查询在职人员
myrecord.CursorLocation = 3
myrecord.Open mysql, myconn, adOpenDynamic, adLockOptimistic, adCmdText
If myrecord.EOF = False Then
j = myrecord.Fields.Count
i = myrecord.RecordCount
Dim a As Integer: Dim b As Integer
myrecord.MoveFirst
For a = 1 To i
For b = 1 To j
MSHFlexGrid1.TextMatrix(a, 1) = myrecord.Fields(0)
strText = myrecord.Fields(2) ‘每一个在职人员的姓名
Set myrecord1 = New ADODB.Recordset
mysql1 = "select * from dataY where (dataY.Y1 like " & Chr(34) & strText & Chr(34) & ") order by ID" ‘在Y表中查询每个在职人员的所有出勤记录
myrecord1.Open mysql1, myconn, adOpenDynamic, adLockOptimistic, adCmdText
myrecord1.MoveLast ‘找到最后一条出勤记录或者是是中间某条符合要求的出勤记录,此处会有其他变化
MSHFlexGrid1.TextMatrix(a, 2) = myrecord1.Fields(2) ‘显示最后一条出勤记录的某个事件
Next b
myrecord.MoveNext
Next a
End If
End If