Hi guys,
I have the following code which I feel runs slow. I have tried to include
de-referencing variables and releasing unmanaged resources. Am I doing the
right thing or is there a better way of going about this.
Briefly, the code opens a table containing imported data. The code deletes
all the old data from two tables. When empty these two tables are opened to
gather the data from the import. A lookup is carried out on one piece of
data, the result being entered in the data table.
Any ideas on the best way to approach this?
Cheers
Peter.
Code follows:-
<code snip>
Dim myconnection As New Odbc.OdbcConnection("DSN=memberbase")
Dim mysql As String
Dim i As Integer
Dim RecCount As Integer
Private Sub btnStage1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnStage1.Click
' Round One
' Update members table, members category and Carnet card numbers
' delete all the records from the members table
myconnection.Open()
mysql = "Select * FROM tblMembers"
Dim daDeleteData As Odbc.OdbcDataAdapter = New
Odbc.OdbcDataAdapter(mysql, myconnection)
Dim dsDeleteData = New DataSet
Dim cbDeleteData As New Odbc.OdbcCommandBuilder(daDeleteData)
daDeleteData.Fill(dsDeleteData)
RecCount = dsDeleteData.Tables(0).Rows.Count
For i = 0 To dsDeleteData.Tables(0).Rows.Count - 1
dsDeleteData.Tables(0).Rows(i).Delete()
btnProgress.Text = "Deleting old Member records, " & i + 1 & "
of " & RecCount & "."
Me.Refresh()
Next
daDeleteData.Update(dsDeleteData)
btnProgress.Text = "Deleted."
Me.Refresh()
' clean up memory
myconnection.Close()
' dispose unneeded objects
daDeleteData.Dispose()
cbDeleteData.Dispose()
'dereference objects
mysql = Nothing
daDeleteData = Nothing
dsDeleteData = Nothing
cbDeleteData = Nothing
myconnection.Open()
mysql = "Select * FROM tblMemberCarnetCard"
Dim daDeleteCarnetCard As Odbc.OdbcDataAdapter = New
Odbc.OdbcDataAdapter(mysql, myconnection)
Dim dsDeleteCarnetCard = New DataSet
Dim cbDeleteCarnetCard As New
Odbc.OdbcCommandBuilder(daDeleteCarnetCard)
daDeleteCarnetCard.Fill(dsDeleteCarnetCard)
RecCount = dsDeleteCarnetCard.Tables(0).Rows.Count
For i = 0 To dsDeleteCarnetCard.Tables(0).Rows.Count - 1
dsDeleteCarnetCard.Tables(0).Rows(i).Delete()
btnProgress.Text = "Deleting old Carnet Card records, " & i + 1
& " of " & RecCount & "."
Me.Refresh()
Next
daDeleteCarnetCard.Update(dsDeleteCarnetCard)
' clean up memory
myconnection.Close()
' dispose unneeded objects
daDeleteCarnetCard.Dispose()
cbDeleteCarnetCard.Dispose()
'dereference objects
mysql = Nothing
daDeleteCarnetCard = Nothing
dsDeleteCarnetCard = Nothing
cbDeleteCarnetCard = Nothing
btnProgress.Text = "All Deleted."
Me.Refresh()
' import quickbooks data into SAAA tables
myconnection.Open()
' open imported data table
mysql = "Select * FROM ImportData"
Dim daQBData As Odbc.OdbcDataAdapter = New
Odbc.OdbcDataAdapter(mysql, myconnection)
Dim dsQBData = New DataSet
daQBData.Fill(dsQBData, "QB")
btnProgress.Text = "Opening Import Data Table."
Me.Refresh()
' open member carnet card data table
mysql = "Select * FROM tblMemberCarnetCard"
Dim daCarnetCard As Odbc.OdbcDataAdapter = New
Odbc.OdbcDataAdapter(mysql, myconnection)
Dim dsCarnetCard = New DataSet
Dim drCarnetCard As DataRow
Dim cbCarnetCard As Odbc.OdbcCommandBuilder = New
Odbc.OdbcCommandBuilder(daCarnetCard)
daCarnetCard.Fill(dsCarnetCard, "CarnetCard")
btnProgress.Text = "Opening Carnet Card Data Table."
Me.Refresh()
' select new members data table to import the data
mysql = "Select * FROM tblMembers"
Dim daQBMembers As Odbc.OdbcDataAdapter = New
Odbc.OdbcDataAdapter(mysql, myconnection)
Dim dsQBMembers = New DataSet
Dim drQBMembers As DataRow
Dim cbQBMembers As Odbc.OdbcCommandBuilder = New
Odbc.OdbcCommandBuilder(daQBMembers)
daQBMembers.Fill(dsQBMembers, "QBMembers")
btnProgress.Text = "Opening Member Data Table."
Me.Refresh()
myconnection.Close()
RecCount = dsQBData.Tables(0).Rows.Count
For i = 0 To dsQBData.Tables(0).Rows.Count - 1
drQBMembers = dsQBMembers.Tables(0).NewRow()
drCarnetCard = dsCarnetCard.Tables(0).NewRow()
' process members table
drQBMembers("Title") = dsQBData.Tables(0).Rows(i)("Mr, Mrs")
drQBMembers("MemberID") =
CType(dsQBData.Tables(0).Rows(i)("Account No"), Integer)
drQBMembers("FirstName") = dsQBData.Tables(0).Rows(i)("First
Name")
drQBMembers("MiddleName") = dsQBData.Tables(0).Rows(i)("MI")
drQBMembers("LastName") = dsQBData.Tables(0).Rows(i)("Last
Name")
drQBMembers("HomePhone") = dsQBData.Tables(0).Rows(i)("Phone")
drQBMembers("HomeFax") = dsQBData.Tables(0).Rows(i)("Fax")
drQBMembers("MobilePhone") = dsQBData.Tables(0).Rows(i)("Alt
Contact")
drQBMembers("HomeStreet") =
dsQBData.Tables(0).Rows(i)("Street1")
drQBMembers("HomeCity") = dsQBData.Tables(0).Rows(i)("City")
drQBMembers("HomeState") = dsQBData.Tables(0).Rows(i)("County")
drQBMembers("HomePostCode") = dsQBData.Tables(0).Rows(i)("Ship
to")
drQBMembers("EmailAddress") =
dsQBData.Tables(0).Rows(i)("Email")
drQBMembers("ExpiryDate") =
dsQBData.Tables(0).Rows(i)("Subscription Renewal")
drQBMembers("Profession") =
dsQBData.Tables(0).Rows(i)("Occupation/Expertise")
drQBMembers("Outstanding") = 0
drQBMembers("MembershipStatusID") = 1
drQBMembers("AddressType") = "Home"
' end process Members table
' process MemberCategoryID
Dim MemberCategory = dsQBData.Tables(0).Rows(i)("Customer Type")
mysql = "SELECT * from tblMemberCategory WHERE Description = '"
& MemberCategory & "'"
Dim dsMemberCat As New DataSet
myconnection.Open()
Dim daMemberCat As Odbc.OdbcDataAdapter = New
Odbc.OdbcDataAdapter(mysql, myconnection)
myconnection.Close()
daMemberCat.Fill(dsMemberCat, "MemberCat")
If dsMemberCat.Tables(0).Rows.Count <> 0 Then
drQBMembers("MemberCategoryID") =
CType(dsMemberCat.Tables(0).Rows(0)("MemberCategor yID"), Integer)
Else
drQBMembers("MemberCategoryID") = 5
End If
' end process MemberCategoryID
' process carnet card table
drCarnetCard("MemberID") =
CType(dsQBData.Tables(0).Rows(i)("Account No"), Integer)
If IsDBNull(dsQBData.Tables(0).Rows(i)("Carnet Card #")) = True
Then
drCarnetCard("CarnetCard") = 0
drQBMembers("Licence") = ""
drCarnetCard("Valid") = False
ElseIf IsNumeric(dsQBData.Tables(0).Rows(i)("Carnet Card #")) =
True Then
drCarnetCard("CarnetCard") =
CType(dsQBData.Tables(0).Rows(i)("Carnet Card #"), Integer)
drCarnetCard("Valid") = True
drQBMembers("Licence") = ""
Else
drCarnetCard("CarnetCard") = 0
drQBMembers("Licence") = dsQBData.Tables(0).Rows(i)("Carnet
Card #")
drCarnetCard("Valid") = False
End If
drCarnetCard("AircraftRegistration") =
dsQBData.Tables(0).Rows(i)("VH-REG")
' end process Members table
dsQBMembers.Tables(0).Rows.Add(drQBMembers)
daQBMembers.Update(dsQBMembers.Tables(0))
dsCarnetCard.Tables(0).Rows.Add(drCarnetCard)
daCarnetCard.Update(dsCarnetCard.Tables(0))
btnProgress.Text = "Importing new records into Members table, "
& i + 1 & " of " & RecCount & "."
Me.Refresh()
Next
DataGrid1.DataSource = dsQBMembers.Tables(0)
DataGrid1.TableStyles.Clear()
' dispose of unneeded objects
myconnection.Dispose()
daCarnetCard.Dispose()
cbCarnetCard.Dispose()
daQBMembers.Dispose()
cbQBMembers.Dispose()
daQBData.Dispose()
' dereference the objects
mysql = Nothing
daCarnetCard = Nothing
dsCarnetCard = Nothing
drCarnetCard = Nothing
cbCarnetCard = Nothing
daQBMembers = Nothing
dsQBMembers = Nothing
drQBMembers = Nothing
cbQBMembers = Nothing
daQBData = Nothing
dsQBData = Nothing
MsgBox("Done first round")
btnStage1.Enabled = False
btnStage2.Enabled = True
End Sub
</code snip> 3 1187
Peter W Johnson wrote: Hi guys,
I have the following code which I feel runs slow. I have tried to include de-referencing variables and releasing unmanaged resources. Am I doing the right thing or is there a better way of going about this. Briefly, the code opens a table containing imported data. The code deletes all the old data from two tables. When empty these two tables are opened to gather the data from the import. A lookup is carried out on one piece of data, the result being entered in the data table.
Any ideas on the best way to approach this?
How about using a stored procedure in the database to empty the tables? Or
drop the tables and re-create them?
Actually, it looks like the entire thing could be done with stored
procedures. Then there would be no back-and-forth data transfer between your
app and the database.
Andrew
"Peter W Johnson" <vk****@yahoo.com> schrieb Hi guys,
I have the following code which I feel runs slow. I have tried to include de-referencing variables and releasing unmanaged resources. Am I doing the right thing or is there a better way of going about this.
This is pretty much code to examine - for free.
Have you tried narrowing down the problem to fewer lines? How long do the
blocks take to execute? There are 3 loops AFAIS, there are Fill and Update
commands - it's hard to find the main problem.
I haven't inspected the whole code completely, but here are some remarks:
For i = 0 To dsDeleteData.Tables(0).Rows.Count - 1 dsDeleteData.Tables(0).Rows(i).Delete() btnProgress.Text = "Deleting old Member records, " & i + 1 & " of " & RecCount & "." Me.Refresh() Next
If there are many records, it might take some time to call Me.Refresh. The
textbox itself also has a Refereh method, so it's probably not necessary to
refresh the whole Form. Also be aware of the fact mentioned in the 3rd
paragraph @ http://msdn.microsoft.com/library/en...sagequeues.asp
Workaround: Call Peekmessage from time to time to avoid being forced to
write another thread without the need for the user being able to interact.
(I hope this sentence makes sense....)
daDeleteData.Update(dsDeleteData) btnProgress.Text = "Deleted." Me.Refresh()
' clean up memory myconnection.Close()
' dispose unneeded objects daDeleteData.Dispose() cbDeleteData.Dispose()
Just a side note: I prefer disposing the objects first, then closing the
connection (reverse order). Doesn't have to be a problem otherwise, but..
'dereference objects mysql = Nothing daDeleteData = Nothing dsDeleteData = Nothing cbDeleteData = Nothing
Disposing should be sufficient.
This...
drQBMembers("MemberID") = CType(dsQBData.Tables(0).Rows(i)("Account No"), Integer) drQBMembers("FirstName") = dsQBData.Tables(0).Rows(i)("First Name") drQBMembers("MiddleName") = dsQBData.Tables(0).Rows(i)("MI") drQBMembers("LastName") = dsQBData.Tables(0).Rows(i)("Last Name") drQBMembers("HomePhone") = dsQBData.Tables(0).Rows(i)("Phone")
....can be changed to
with dsQBData.Tables(0).Rows(i)
drQBMembers("MemberID") = CType(.Item("Account No"), Integer)
drQBMembers("FirstName") = .Item("First Name")
drQBMembers("MiddleName") = .Item("MI")
drQBMembers("LastName") = .Item("Last Name")
drQBMembers("HomePhone") = .Item("Phone")
'...
end with
Again, you should probably narrow the problem down, first.
Armin
Sorry about the long piece of code.... I'll check out stored procedures.
Thanks for the help.
Cheers
Peter.
"Armin Zingler" <az*******@freenet.de> wrote in message
news:%2*****************@TK2MSFTNGP10.phx.gbl... "Peter W Johnson" <vk****@yahoo.com> schrieb Hi guys,
I have the following code which I feel runs slow. I have tried to include de-referencing variables and releasing unmanaged resources. Am I doing the right thing or is there a better way of going about this.
This is pretty much code to examine - for free.
Have you tried narrowing down the problem to fewer lines? How long do the blocks take to execute? There are 3 loops AFAIS, there are Fill and Update commands - it's hard to find the main problem.
I haven't inspected the whole code completely, but here are some remarks:
For i = 0 To dsDeleteData.Tables(0).Rows.Count - 1 dsDeleteData.Tables(0).Rows(i).Delete() btnProgress.Text = "Deleting old Member records, " & i + 1 & " of " & RecCount & "." Me.Refresh() Next
If there are many records, it might take some time to call Me.Refresh. The textbox itself also has a Refereh method, so it's probably not necessary to refresh the whole Form. Also be aware of the fact mentioned in the 3rd paragraph @ http://msdn.microsoft.com/library/en...sagequeues.asp
Workaround: Call Peekmessage from time to time to avoid being forced to write another thread without the need for the user being able to interact. (I hope this sentence makes sense....)
daDeleteData.Update(dsDeleteData) btnProgress.Text = "Deleted." Me.Refresh()
' clean up memory myconnection.Close()
' dispose unneeded objects daDeleteData.Dispose() cbDeleteData.Dispose()
Just a side note: I prefer disposing the objects first, then closing the connection (reverse order). Doesn't have to be a problem otherwise, but..
'dereference objects mysql = Nothing daDeleteData = Nothing dsDeleteData = Nothing cbDeleteData = Nothing
Disposing should be sufficient.
This...
drQBMembers("MemberID") = CType(dsQBData.Tables(0).Rows(i)("Account No"), Integer) drQBMembers("FirstName") = dsQBData.Tables(0).Rows(i)("First Name") drQBMembers("MiddleName") = dsQBData.Tables(0).Rows(i)("MI") drQBMembers("LastName") = dsQBData.Tables(0).Rows(i)("Last Name") drQBMembers("HomePhone") = dsQBData.Tables(0).Rows(i)("Phone")
...can be changed to
with dsQBData.Tables(0).Rows(i) drQBMembers("MemberID") = CType(.Item("Account No"), Integer) drQBMembers("FirstName") = .Item("First Name") drQBMembers("MiddleName") = .Item("MI") drQBMembers("LastName") = .Item("Last Name") drQBMembers("HomePhone") = .Item("Phone") '... end with
Again, you should probably narrow the problem down, first. Armin This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: jmoy |
last post by:
I have some data (say in a file) that needs to be handled byte by
byte. Source code I have looked at does this by treating the data as a
stream of 'char's. However, the standard does not require a...
|
by: Alexander Muylaert |
last post by:
Hi
Does anyone know a good starting point about high speed string processing in
C#?
What I need is a very fast routine for a case insensitive "contains". e ==
E == é == ë == ...
Kind...
|
by: Claire |
last post by:
My application has a thread reading byte arrays from an unmanaged
dll(realtime controller monitoring). The array represents an unmanaged
struct containing a series of header fields plus a variable...
|
by: Joe Reazor |
last post by:
I've got an asp.net page that has approximately 1,440 controls on it. You
must think I'm crazy, but we need to have the page organized in this
fashion. As you can imagine, the page takes a very...
|
by: Geoff Pennington |
last post by:
My VB.Net app reads an Excel file, processes it one row at a time, and when
processing is complete writes the row to a database. The typical file will
have several thousand rows and may take a...
|
by: Alexis Gallagher |
last post by:
(I tried to post this yesterday but I think my ISP ate it. Apologies if
this is a double-post.)
Is it possible to do very fast string processing in python? My
bioinformatics application needs to...
|
by: K.S.Sreeram |
last post by:
All the recent discussions on xml parsing performance got me curious,
and i put together a small speed test for xml processing.
The test program was designed to have as minimal state requirements...
|
by: Enrique Cruiz |
last post by:
Hello all,
I am currently implementing a fairly simple algorithm. It scans a
grayscale image, and computes a pixel's new value as a function of its
original value. Two passes are made, first...
|
by: vectorizor |
last post by:
Hi all,
I am writing an image processing algorithm that goes across an image
first row-wise and then column-wise. For illustration purposes,
imagine that for every pixels, the output is computed...
|
by: jacob navia |
last post by:
OK I am running vista.
My old machine died with a disk controller failure and I had to buy
a new one. The new one was cheaper than the old one (1100 Euros vs
620 Euros) but had twice as much RAM...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |