?
Solved

Performance writing multiple rows to Access DB

Posted on 2011-02-22
10
Medium Priority
?
576 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Siv
  • 6
  • 4
10 Comments
 
LVL 86

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 34954944
Your overhead may be coming about due to building and maintaining the Objects. Unless you are working directly with that DataTable (i.e. you've built an in-memory table, and your users are working with it through a Datagrid or something of that nature)  you can just open a connection to the Access database and use the OLEDBCommand object to write it. This would entail reworking the method you use to "build" that SellerNumber string, since this would expect a fully formed and valid Access-compliant query, but if you did this, you could build ONE connection, one command, and use the ExecuteNonQuery method on that object:

Dim cmd As New OleDb.OleDbCommand("INSERT INTO blah blah", YourConnectionObject)
cmd.ExecuteNonQuery()

Of course, if you must also maintain existing records you'll have to change the INSERT into a SELECT.
0
 

Author Comment

by:Siv
ID: 34955019
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
0
 
LVL 86
ID: 34957430
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.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 

Author Comment

by:Siv
ID: 34957927
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?
0
 

Author Comment

by:Siv
ID: 34958152
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.
0
 

Author Closing Comment

by:Siv
ID: 34958162
Huge thanks, the routine now takes less than one minute to run where it took 8 hours using class modules.
0
 
LVL 86
ID: 34959820
Wow. That's a HUGE difference. Glad to help!
0
 

Author Comment

by:Siv
ID: 34959881
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.
0
 
LVL 86
ID: 34959950
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.
0
 

Author Comment

by:Siv
ID: 34960080
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.
0

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question