speeding up database access

[simon_w3]

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(CDBPath &
TacCDBDB)

VMSQuery = myDB.CreateQueryDef("",
"SELECTtblPlanVmsSetting.strVmsId,tblPlanVmsSetting.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.LngDestinationDiversionNum)
as Diversionnumber," & " (tblVmsResponse.strIncidentLink) as
Incidentlink ,(tblVmsResponse.bytMessageResponse) as response,
tblVmsResponse.strPlan" & " FROM (tblIncidentResponse INNER JOIN
tblVmsResponse ON
tblIncidentResponse.strLinkNum=tblVmsResponse.strIncidentLink) INNER
JOIN" & " tblPlanVmsSetting ON tblPlanVmsSetting.strVmsId
=tblVmsResponse.strVmsId" & " WHERE ((tblVmsResponse.strVmsId = [Enter
VMSID:]) AND (tblPlanVmsSetting.strPlan =[Enter PLANID:]) AND" & "
(tblPlanVmsSetting.strPlan=tblIncidentResponse.strPlan))" & " GROUP BY
tblVmsResponse.intDestZone,tblVmsResponse.LngDestinationDiversionNum,"
& " tblVmsResponse.strIncidentLink,tblVmsResponse.bytMessageResponse,
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.Fields("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("diversionnumber").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()

 

[Cor Ligthert]

Just to prevent more times answer for the same question

 

[SBFF]

The loop within a loop is going to make it slow. Every time you query a
database, it is a performance hit (think 1 sec). Querying a DB within a
loop will murder your performance for sure.
Also, I noticed a movefirst, movelast early-on in your code. That is an old
DAO technique usually used to determine your dataset size. I might skip
that because it causes DAO to traverse the entire result set.
I have also heard that adding optimizer hints to queries can sometimes work
against you if the DB is changed so that the hints are no longer valid. You
might want to look into that.
For sure, the query in a loop is going to be your biggest cpu eater.
Good luck