By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,369 Members | 1,240 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,369 IT Pros & Developers. It's quick & easy.

Speeding up database access

P: n/a
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
Share this Question
Share on Google+
5 Replies

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.