Speeding up database access

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

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.strVm­sId,tblPlanVmsSetting.IntVmsTy­pe,tblVms..ysnFunctional"

& " FROM tblPlanVmsSetting INNER JOIN tblVms ON
tblPlanVmsSetting.strVmsId=tbl­Vms.strVmsId" & " WHERE
tblPlanVmsSetting.strPlan=[Ent­er plan];")


DestQuery = myDB.CreateQueryDef("", "SELECT
tblVmsResponse.IntDestZone,(tb­lVmsResponse.LngDestinationDiv­ersionNum)

as Diversionnumber," & " (tblVmsResponse.strIncidentLin­k) as
Incidentlink ,(tblVmsResponse.bytMessageRes­ponse) as response,
tblVmsResponse.strPlan" & " FROM (tblIncidentResponse INNER JOIN
tblVmsResponse ON
tblIncidentResponse.strLinkNum­=tblVmsResponse.strIncidentLin­k)
INNER
JOIN" & " tblPlanVmsSetting ON tblPlanVmsSetting.strVmsId
=tblVmsResponse.strVmsId" & " WHERE ((tblVmsResponse.strVmsId = [Enter
VMSID:]) AND (tblPlanVmsSetting.strPlan =[Enter PLANID:]) AND" & "
(tblPlanVmsSetting.strPlan=tbl­IncidentResponse.strPlan))" & " GROUP
BY
tblVmsResponse.intDestZone,tbl­VmsResponse.LngDestinationDive­rsionNum,"

& "
tblVmsResponse.strIncidentLink­,tblVmsResponse.bytMessageResp­onse,
tblVmsResponse.strPlan;")


PlanRS = myDB.OpenRecordset("tblPlan")
PlanRS.MoveLast()
PlanRS.MoveFirst()
PlanCounter = 0
Do While Not PlanRS.EOF
If PlanRS.Fields("ysnUseable").Va­lue Then
If PlanRS.RecordCount > MaxPlans Then
Call fatal_error(CDBDB, "Controller",
"PlanManager.BuildPlanListFrom­Cdb", "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").V­alue
Plan.Description = ""
If Not
IsDBNull(PlanRS.Fields("strDes­cription").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").Va­lue = 1
Or VMSRS.Fields ("IntVmsType").Val­ue = 2) Then
Plan.IsRunable = False
End If
Else


Call
Plan.AddVMSIndex(IntVMSNumForM­OLAVMSID(VMSRS.Fields("strVmsI­d").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").Valu­e)
Call Plan.AddMola3Destination(v,
DestRS.Fields("IncidentLink").­Value, DestRS.Fields
("intDestZone").Value, DestRS.Fields("response").Valu­e, 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()
 
We heard you the first time.

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.strVm­sId,tblPlanVmsSetting.IntVmsTy­pe,tblVms.ysnFunctional"

& " FROM tblPlanVmsSetting INNER JOIN tblVms ON
tblPlanVmsSetting.strVmsId=tbl­Vms.strVmsId" & " WHERE
tblPlanVmsSetting.strPlan=[Ent­er plan];")


DestQuery = myDB.CreateQueryDef("", "SELECT
tblVmsResponse.IntDestZone,(tb­lVmsResponse.LngDestinationDiv­ersionNum)

as Diversionnumber," & " (tblVmsResponse.strIncidentLin­k) as
Incidentlink ,(tblVmsResponse.bytMessageRes­ponse) as response,
tblVmsResponse.strPlan" & " FROM (tblIncidentResponse INNER JOIN
tblVmsResponse ON
tblIncidentResponse.strLinkNum­=tblVmsResponse.strIncidentLin­k)
INNER
JOIN" & " tblPlanVmsSetting ON tblPlanVmsSetting.strVmsId
=tblVmsResponse.strVmsId" & " WHERE ((tblVmsResponse.strVmsId = [Enter
VMSID:]) AND (tblPlanVmsSetting.strPlan =[Enter PLANID:]) AND" & "
(tblPlanVmsSetting.strPlan=tbl­IncidentResponse.strPlan))" & " GROUP
BY
tblVmsResponse.intDestZone,tbl­VmsResponse.LngDestinationDive­rsionNum,"

& "
tblVmsResponse.strIncidentLink­,tblVmsResponse.bytMessageResp­onse,
tblVmsResponse.strPlan;")


PlanRS = myDB.OpenRecordset("tblPlan")
PlanRS.MoveLast()
PlanRS.MoveFirst()
PlanCounter = 0
Do While Not PlanRS.EOF
If PlanRS.Fields("ysnUseable").Va­lue Then
If PlanRS.RecordCount > MaxPlans Then
Call fatal_error(CDBDB, "Controller",
"PlanManager.BuildPlanListFrom­Cdb", "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").V­alue
Plan.Description = ""
If Not
IsDBNull(PlanRS.Fields("strDes­cription").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").Va­lue = 1
Or VMSRS.Fields ("IntVmsType").Val­ue = 2) Then
Plan.IsRunable = False
End If
Else


Call
Plan.AddVMSIndex(IntVMSNumForM­OLAVMSID(VMSRS.Fields("strVmsI­d").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").Valu­e)
Call Plan.AddMola3Destination(v,
DestRS.Fields("IncidentLink").­Value, DestRS.Fields
("intDestZone").Value, DestRS.Fields("response").Valu­e, 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()
 
Simon,

I don't even know if I have expirience with DAO, I thought that ADO was the
first I have seen.

However Paul Clement mostly involved with DAO in these newsgroups write
AFAIK forever that DAO was the fastest. I have not seen Paul somedays.

However when I see this kind of code I know that there should be something
wrong.
PlanRS.MoveLast()
PlanRS.MoveFirst()
Click to expand...

If you don't know how to change this, than I would go directly to ADONET.
However not for the benefit to speed up. However to get help to get it
better.

Just my thought,

Cor
 
¤ 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?
¤

Have you been able to identify what part of the code is actually slower?


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Thanks for your replies.

It doesn't appear that any one single part of the code is slower than
another - it's just that it takes a long time to go through the enitire
loop structure so I'm looking at ways to generally speed up the entire
routine. Perhaps it's running as fast as it can do but I didn't know
whether there were more optimal ways of interacting with the database.

Is there a way of reading the entire database into RAM and accessing it
from there? That would surely speed things up.

Based on Cor's response converting to ADO.NET will not deliver improved
performance - is that the general belief from everyone else?

Thanks,

Simon

P.S. Some Guy - I apologise if this was posted more than once - I have
no idea how that happened.
 
¤ Thanks for your replies.
¤
¤ It doesn't appear that any one single part of the code is slower than
¤ another - it's just that it takes a long time to go through the enitire
¤ loop structure so I'm looking at ways to generally speed up the entire
¤ routine. Perhaps it's running as fast as it can do but I didn't know
¤ whether there were more optimal ways of interacting with the database.
¤
¤ Is there a way of reading the entire database into RAM and accessing it
¤ from there? That would surely speed things up.
¤
¤ Based on Cor's response converting to ADO.NET will not deliver improved
¤ performance - is that the general belief from everyone else?
¤

Well there is COM interop overhead with DAO involved so that could be slowing the process down. It
actually might be a bit faster if you used ADO.NET and an OleDbDataReader.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

speeding up database access 2

Back
Top