asked on

Performance writing multiple rows to Access DB

Hi,
I have a routine in a VB.NET 2010 application that reads rows of data from an import table "TempFeeddata". Each row of data from the source provides values that are in some cases accumultaed or other mathematical transformatons applied to them and then the 13 pieces of data created are written back to a separate table that is used to create a historical record of the data processed.

I am having real performance issues with the routine that writes the data to the target table, basically I have a class module that mirros the target data table and I write blocks of code to it like this:

Dim sn As New SellerNumbers

Try
sn.Employee_Number = SellerNumber
sn.Monitoring_Type_ID = mt.NewAccountSalesMonth
sn.Number = c.NewAccountSalesMonth
sn.Month_Of_Link = EndM
sn.Year_Of_Link = EndY
sn.Date_Created = Now
sn.AddRecord(False) 'Write occurs here

sn.Employee_Number = SellerNumber
sn.Monitoring_Type_ID = mt.NewAccountsNotActivated
sn.Number = c.NewAccountsNotActivated
sn.Month_Of_Link = EndM
sn.Year_Of_Link = EndY
sn.Date_Created = Now
sn.AddRecord(False) 'Write occurs here and so on.
...
'13 of these blocks in all per row from the source table row.

Return True

Catch ex As Exception

PEH("SaveMonthlyDataToSellerNumbers", "Calculations Module", ex.Message)
Return False

Finally

sn.Dispose()

End Try

I seem to get relatively long delays as each call to sn.AddRecord is called. This routine looks like this from the class:

Public Function AddRecord(ByVal Modify As Boolean) As Boolean

Dim strSQL As String = "", cb As OleDbCommandBuilder = Nothing, da As OleDbDataAdapter = Nothing
Dim Cn As OleDbConnection = Nothing, dt As Data.DataTable = Nothing
Dim SaveAsNew As Boolean = False, rw As Data.DataRow

Try

If Not Modify Then
strSQL = "SELECT * FROM SellerNumbers ;"
SaveAsNew = True
Else
strSQL = "SELECT * FROM SellerNumbers WHERE Employee_Number=" & Employee_Number & ";"
End If

Cn = New OleDbConnection(strCnn & DBFilePath)
Cn.Open()
dt = New Data.DataTable
da = New OleDbDataAdapter(strSQL, Cn)
cb = New OleDbCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
da.Fill(dt)

If Not SaveAsNew Then
'We are modifying an existing record
If dt.Rows.Count < 1 Then
Return 0 'Caller must warn user.
Else
'We have found the record to modify
rw = dt.Rows(0) 'Assign it to the row Var
End If
Else
'We are saving as new - NB Uniqueness is obtained from the composite key of Employee_Number, MT, M and Y
If Employee_Number <> 0 Then
rw = dt.NewRow
Else
Dim M As String = "WARNING: The Employee_Number for the SellerNumbers table was passed as zero whilst "
M += "this indicates an error in the routine that called this Function. "
M += "This needs reporting to " & AdminSupport & " who "
M += "should raise this with " & Support & " who will ascertain what is causing this issue." & NNL
M += "This means that the record you are working on has not been saved, apologies for that."
MessageBox.Show(M, H, MessageBoxButtons.OK, MessageBoxIcon.Error)

Return False 'Caller must warn user.
End If
End If

rw("Employee_Number") = Employee_Number
rw("Monitoring_Type_ID") = Monitoring_Type_ID
rw("Number") = Number
rw("Month_Of_Link") = Month_Of_Link
rw("Year_Of_Link") = Year_Of_Link
rw("Date_Created") = Date_Created

If SaveAsNew Then
dt.Rows.Add(rw)
End If

'Update the row to the database
da.Update(dt)

Return True

Catch ex As Exception

PEH("AddRecord", "SellerNumbers Class", ex.Message)
Return False

Finally

'Tidy up
dt.Dispose()
da.Dispose()
cb.Dispose()
Cn.Close()

End Try

End Function

My question is, is there any way to batch the 13 separate calls that would improve performance or another way of attacking the AddRecord methodolgy that would mean the add record is quicker. Because the source data table has about 68,000 rows this is taking up to 8 hours to process on a fairly powerful dual core 3.0GHz system running Windows 7 64 bit.
I cannot use SQL Server (I'd like to but it's not an option for the end user at the moment).

Any help appreciated.
Siv

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Siv

ASKER

LSMConsulting

This is a stanbdalone process, there no users accessing this whilst it's runing it's literaly just reading data from the feed table, disecting the columns we need from that doing some calculations that leave us with 13 values that must be written into the "SellerNumbers" table. This data is then further processed to produce a load of reports in the reporting stage of the process.

At this stage we are just reading in the feed, getting our values from it and then writing them back, it's pure data processing.

I wondered if it might be the objects as i am creating and destroying them each time round the loop.

Siv

Scott McDaniel (EE MVE )

You can certainly build the SQL Statements and issue them directly. If you've already got the data, I see no reason to continually build and destroy objects. Just build your well-formed SQL strings and go from there.

Siv

ASKER

Are you thinking of doing this:

Replacing this:
sn.Employee_Number = SellerNumber
sn.Monitoring_Type_ID = mt.NewAccountSalesMonth
sn.Number = c.NewAccountSalesMonth
sn.Month_Of_Link = EndM
sn.Year_Of_Link = EndY
sn.Date_Created = Now
sn.AddRecord(False)

With:
Dim StrSQL as String = ""

try
strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.NewAccountSalesMonth & ", " & c.NewAccountSalesMonth & ", " & EndM & ", " & EndY & ", " & Now & ") ;"
cmd = New OleDbCommand(strSQL, Cn)
cmd.ExecuteNonQuery()

followed by this:

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.NewAccountsNotActivated & ", " & c.NewAccountsNotActivated & ", " & EndM & ", " & EndY & ", " & Now & ") ;"
cmd = New OleDbCommand(strSQL, Cn)
cmd.ExecuteNonQuery()

Instead of this:

sn.Employee_Number = SellerNumber
sn.Monitoring_Type_ID = mt.NewAccountsNotActivated
sn.Number = c.NewAccountsNotActivated
sn.Month_Of_Link = EndM
sn.Year_Of_Link = EndY
sn.Date_Created = Now
sn.AddRecord(False)

Or am I barking up the worng tree?

Siv

ASKER

LSMConsulting,

I made it like this:

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.NewAccountSalesMonth & ", " & c.NewAccountSalesMonth & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

'Remmed out the old code:
'sn.Employee_Number = SellerNumber
'sn.Monitoring_Type_ID = mt.NewAccountSalesMonth
'sn.Number = c.NewAccountSalesMonth
'sn.Month_Of_Link = EndM
'sn.Year_Of_Link = EndY
'sn.Date_Created = Now
'sn.AddRecord(False)
I just create a command object at module level and a connection object at module level:

Private cmd As New OleDbCommand
Private Cn As OleDbConnection = Nothing

Just before I enter the routine that loops through the feed data I initialise teh command object and teh connection:

'Open the connection
Cn = New OleDbConnection(strCnn & DBFilePath)
Cn.Open()

'Assign the connection to the Cmd OledbCommand Object.
cmd.Connection = Cn 'Get the command object fired up ready for use in the "SaveMonthlyDataToSellerNumbers" routine.

Then as each rows is processed I call my "SaveMonthlyDataToSellerNumbers" routine, that now looks like this:

Private Function SaveMonthlyDataToSellerNumbers(ByRef c As Calculations, ByVal SellerNumber As Integer) As Boolean
'Dim sn As New SellerNumbers
Dim strSQL As String = ""

Try
strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.NewAccountSalesMonth & ", " & c.NewAccountSalesMonth & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.NewAccountsNotActivated & ", " & c.NewAccountsNotActivated & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.CardSalesMonth & ", " & c.CardSalesMonth & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.CardExitsMonth & ", " & c.CardExitsMonth & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.InsuranceSalesMonth & ", " & c.InsuranceSalesMonth & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.HomeExitsMonth & ", " & c.HomeExitsMonth & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.LoanSalesMonth & ", " & c.LoanSalesMonth & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd = New OleDbCommand(strSQL, Cn)
cmd.ExecuteNonQuery()

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.LoanExitsMonth & ", " & c.LoanExitsMonth & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

c.CreditSalesMonth = c.CardSalesMonth + c.LoanSalesMonth

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.CreditSalesMonth & ", " & c.CreditSalesMonth & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.URCASalesMonth & ", " & c.URCASalesMonth & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.URCAExitsMonth & ", " & c.URCAExitsMonth & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

strSQL = "INSERT INTO SellerNumbers VALUES (" & SellerNumber & ", " & mt.URCANTUsMonth & ", " & c.URCANTUsMonth & ", " & EndM & ", " & EndY & ", '" & Format(Now, "dd/MM/yyyy") & "') ;"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()

Return True

Catch ex As Exception

PEH("SaveMonthlyDataToSellerNumbers", "Calculations Module", ex.Message)
Return False

End Try
End Function
I then close the cmd and Cn objects at the end of the calling routine. Which occurs when all 66,000 rows of the feed have been processed.

ORIGINALLY USING THE PREVIOUS METHOD THIS ROUTINE TOOK 8 hours to run.
PROCESS NOW TAKES LESS THAN 1 minute!!!

I have checked the results and it has created the same data output to the target table.

Huge thanks for your help.

Siv

ASKER

Huge thanks, the routine now takes less than one minute to run where it took 8 hours using class modules.

Scott McDaniel (EE MVE )

Wow. That's a HUGE difference. Glad to help!

Siv

ASKER

LSMConsulting,
I just spoke with the end user and he's run the converted version of my program and he runs it in one and a half minutes. He has a very lowly 1.2GHz processor on a laptop and that was taking him over 10 hours to run before the fix.

I am just wondering if applying this sort of thinking to SQL Server which I normally use as my backend database, might have similar performance improvements?

Thanks again.

Scott McDaniel (EE MVE )

As I said earlier, ANYTIME you can use straight SQL to run DML queries (Data Manipulation Language), you're better off in my opinion.

If you're using SQL Server, you might get even better results using Stored Procedures. Using this logic, you just "tell" the server to execute those commands, and the server takes over from there. In many cases it's MUCH faster to allow the server to handle this rather than your app.

If you're not familiar with Stored Procs, do a little reading online to gain an understanding of them, then try your hand. They're fairly straight forward if you understand SQL syntax, and can often make a significant difference.

Siv

ASKER

LSMConsulting,

I use stored procedures now in all my apps, and i often have the "asynchronous processing=true" switch in the SQL Connection string turned on so that I can hand off lengthy processes and then wait for the callback.

I am thinking more of routines like this one where I am loading data from an import that just lives in one table, reading data from that and then writing the adjusted figures back to another table in the same SQL database. I would imagine that this technique might help. Certainly food for thought.

Thanks again for your suggestions.