Processing speed

[Peter W Johnson]

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)("MemberCategoryID"), 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>

 

[Andrew Morton]

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

 

[Armin Zingler]

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/e...ssagequeues/aboutmessagesandmessagequeues.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

 

[Peter W Johnson]

Sorry about the long piece of code.... I'll check out stored procedures.

Thanks for the help.

Cheers

Peter.