470,593 Members | 2,459 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,593 developers. It's quick & easy.

Speeding up database access

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(CD BPath &
TacCDBDB)
VMSQuery = myDB.CreateQueryDef("",
"SELECTtblPlanVmsSetting.strVm*sId,tblPlanVmsSetti ng.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.LngDes tinationDiv*ersionNum)

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

& "
tblVmsResponse.strIncidentLink*,tblVmsResponse.byt MessageResp*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.Fiel ds("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("diversionnu mber").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()

Nov 21 '05 #1
5 1370
We heard you the first time.

"Simon" <si******@ntlworld.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
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(CD BPath &
TacCDBDB)
VMSQuery = myDB.CreateQueryDef("",
"SELECTtblPlanVmsSetting.strVm*sId,tblPlanVmsSetti ng.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.LngDes tinationDiv*ersionNum)

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

& "
tblVmsResponse.strIncidentLink*,tblVmsResponse.byt MessageResp*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.Fiel ds("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("diversionnu mber").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()
Nov 21 '05 #2
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()


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

Nov 21 '05 #3
On 31 May 2005 07:57:58 -0700, "Simon" <si******@ntlworld.com> wrote:

§ 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)
Nov 21 '05 #4
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.

Nov 21 '05 #5
On 1 Jun 2005 01:44:30 -0700, "Simon" <si******@ntlworld.com> wrote:

§ 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)
Nov 21 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Angel Faus | last post: by
3 posts views Thread by Chris Ochs | last post: by
8 posts views Thread by Stephen K. Young | last post: by
2 posts views Thread by Robert Wilkens | last post: by
2 posts views Thread by simon_w3 | last post: by
3 posts views Thread by Mark Reed | last post: by
3 posts views Thread by Cleverbum | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.