473,326 Members | 2,114 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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 1448
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Angel Faus | last post by:
Hi all, We've got an Oracle database in a Web application. Intermedia Text queries are an important part of the usage and work fine. The problem is that in order to generate a certain web...
3
by: Chris Ochs | last post by:
First of all, we are still running sapdb at the moment but are in the process of moving to postgresql, so it seemed a good idea to post this type of question here. In our environment we have...
8
by: Stephen K. Young | last post by:
If you have not seen it, this recent Microsoft Knowledge Base article is worth reading: http://support.microsoft.com/kb/889588 "How to optimize Office Access and Jet database engine...
2
by: Robert Wilkens | last post by:
Ok... This may be the wrong forum, but it's the first place I'm trying. I'm new to C# and just implemented the 3-tier Distributed application from Chapter 1 (the first walkthrough) in the...
2
by: simon_w3 | last post by:
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...
3
by: Mark Reed | last post by:
All, I have built a database recently which resides on a network server which is constantly being re-structured. This is something I have no control over so have had to incorporate a means by...
3
by: Cleverbum | last post by:
I was hoping to parse a webpage and extract all link and image URLs from it and enter the new ones into mySQL tables, below is my code to do it, I've tried to optimise it as much as I can but it...
5
by: RobinAG | last post by:
Hello, I just split my database into front and back end. My front end users are experiencing really slow opening of forms. I've searched online for help speeding up forms, but I'm not sure what...
10
by: ags5406 | last post by:
I've created an application that downloads data daily from a secure web site and stores that data in an Access database. Then there are different options for allowing the user to do keyword and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.