Hi All,
I'm trying to figure out how I can speed up a fragment of code (which
has been converted from vb6 to .NET) that reads data from a database.
My knowledge on database programming is limited. The code uses DAO
(which, I understand, is no longer supported) - would there be any
performance benefit by converting to ADO.NET?
The code I'm attempting to speed up is below - are there any obvious
flaws which could be slowing it down?
Many thanks
Simon
myDB = DAODBEngine_definst.Workspaces(0).OpenDatabase(CDB Path &
TacCDBDB)
VMSQuery = myDB.CreateQueryDef("",
"SELECTtblPlanVmsSetting.strVmsId,tblPlanVmsSettin g.IntVmsType,tblVms.ysnFunctional"
& " FROM tblPlanVmsSetting INNER JOIN tblVms ON
tblPlanVmsSetting.strVmsId=tblVms.strVmsId" & " WHERE
tblPlanVmsSetting.strPlan=[Enter plan];")
DestQuery = myDB.CreateQueryDef("", "SELECT
tblVmsResponse.IntDestZone,(tblVmsResponse.LngDest inationDiversionNum)
as Diversionnumber," & " (tblVmsResponse.strIncidentLink) as
Incidentlink ,(tblVmsResponse.bytMessageResponse) as response,
tblVmsResponse.strPlan" & " FROM (tblIncidentResponse INNER JOIN
tblVmsResponse ON
tblIncidentResponse.strLinkNum=tblVmsResponse.strI ncidentLink) INNER
JOIN" & " tblPlanVmsSetting ON tblPlanVmsSetting.strVmsId
=tblVmsResponse.strVmsId" & " WHERE ((tblVmsResponse.strVmsId = [Enter
VMSID:]) AND (tblPlanVmsSetting.strPlan =[Enter PLANID:]) AND" & "
(tblPlanVmsSetting.strPlan=tblIncidentResponse.str Plan))" & " GROUP BY
tblVmsResponse.intDestZone,tblVmsResponse.LngDesti nationDiversionNum,"
& " tblVmsResponse.strIncidentLink,tblVmsResponse.bytM essageResponse,
tblVmsResponse.strPlan;")
PlanRS = myDB.OpenRecordset("tblPlan")
PlanRS.MoveLast()
PlanRS.MoveFirst()
PlanCounter = 0
Do While Not PlanRS.EOF
If PlanRS.Fields("ysnUseable").Value Then
If PlanRS.RecordCount > MaxPlans Then
Call fatal_error(CDBDB, "Controller",
"PlanManager.BuildPlanListFromCdb", "the number of plans exceeds the
maximum allowed (" & CStr(MaxPlans) & ")", "remove some plans", "")
Else
PlanCounter = PlanCounter + 1
Plan = New clsPlan
Plan.Counter = PlanCounter
Plan.ID = PlanRS.Fields("strPlan").Value
Plan.Issue = PlanRS.Fields("LngIssueNum").Value
Plan.Description = ""
If Not
IsDBNull(PlanRS.Fields("strDescription").Value) Then
Plan.Description =
PlanRS.Fields("strDescription").Value
End If
Plan.IsRunable = True
VMSQuery.Parameters("Enter plan").Value = Plan.ID
VMSRS = VMSQuery.OpenRecordset() 'Executes query
v = 0
Do While Not VMSRS.EOF
v = v + 1
If VMSRS.Fields("ysnFunctional").Value = False
Then
If (VMSRS.Fields("IntVmsType").Value = 1 Or
VMSRS.Fields("IntVmsType").Value = 2) Then
Plan.IsRunable = False
End If
Else
Call
Plan.AddVMSIndex(IntVMSNumForMOLAVMSID(VMSRS.Field s("strVmsId").Value))
End If
VMSRS.MoveNext()
Loop
VMSRS.Close()
'Now get the destination information
For v = 1 To Plan.VMSCount
DestQuery.Parameters("Enter VMSID:").Value =
MOLAVMSID(Plan.VMSIndex(v))
DestQuery.Parameters("Enter PLANID:").Value =
Plan.ID
DestRS = DestQuery.OpenRecordset() 'Executes
query
Do While Not DestRS.EOF
Div =
Divs.AddMOLA3Diversion(DestRS.Fields("diversionnum ber").Value)
Call Plan.AddMola3Destination(v,
DestRS.Fields("IncidentLink").Value, DestRS.Fields
("intDestZone").Value, DestRS.Fields("response").Value, Div,
DestRS.Fields("strPlan").Value)
Div = Nothing
DestRS.MoveNext()
Loop
DestRS.Close()
Next v
Plan.Finalise()
mCol.Add(Plan, Plan.ID)
Plan = Nothing
End If
End If
PlanRS.MoveNext()
Loop
PlanRS.Close()