Solved

Performance writing multiple rows to Access DB

Posted on 2011-02-22
10
560 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 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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 84
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
 

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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 84
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 84
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now