Very slow updating access table using vb.net

[MGM]

Hey everyone, I'm having some issues here. I have a large Access .mdb (over 400,000 rows).

What I need to do is, there are 12 columns that need to be reordered (quite literally as simple as if it is XZY, change to XYZ, but the row next to it must ALSO be changed accordingly, as in, WEC to WCE [in other words, however the first row's order was changed, the second row must also be changed in the exact same order]).

Anyways, when counting all of the rows that have the above mistake, there are nearly 300,000 rows affected. I need to write a quick program or web app that can go through, find any row that isn't ordered properly (XYZ) and change it (and the row next to it) to the proper order. Then I need to update the Access database with the new data.

Here is my current code:

Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Text
 
Module Module1
    Sub Main()
 
        Dim conn, cmd, adpt, Data
        conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Phasing Devices.mdb")
        cmd = New OleDbCommand("SELECT TOP 500 * FROM phasedetails WHERE devphasing1='YX' OR devphasing1='yx'", conn)
        adpt = New OleDbDataAdapter(cmd)
        Data = New DataSet()
        adpt.Fill(Data)
 
        conn.Open()
        Dim i = 0
        For Each Dr As DataRow In Data.Tables(0).Rows
            Dim id = Dr.Item("ID")
            Dim devphasing1, devorien1, title, title2
            Dim newDevphasing1 As String
            Dim newDevorien1 As String
 
            title = "devphasing1"
            title2 = "devorien1"
            newDevphasing1 = "XY"
            devphasing1 = ""
            devorien1 = ""
            newDevorien1 = ""
 
            devphasing1 = Dr.Item(title).ToString
            devorien1 = Dr.Item(title2)
 
            If Len(devorien1) > 0 Then
                newDevorien1 = Right(devorien1, 1) & Left(devorien1, 1)
            Else
                newDevorien1 = ""
            End If
 
 
            Dim objCommUpdate As New OleDbCommand("UPDATE phasedetails SET devphasing1='XY' AND devorien1='" & newDevorien1.ToString & "' WHERE ID=" & id, conn)
 
 
            Try
                objCommUpdate.ExecuteNonQuery()
            Catch ex As Exception
                Console.WriteLine("Error!")
            End Try
            Console.WriteLine("Done: " & id & " (2)")
        Next
 
        Console.WriteLine("Completed Successfully...")
 
        'Close the database connection
        conn.Close()
        Console.ReadLine()
    End Sub
 
End Module
 

Now, ignore the query at the moment. I am only doing it for YX to XY to get it working, then I'll expand it for all variations of XYZ that need to be re-ordered alphabetically.

From my own messing around, I've found that removing this line makes everything go tons faster:

objCommUpdate.ExecuteNonQuery()

So I know that it has something to do with updating the database. So can anyone figure out how to speed things up? Right now it takes literally a second for each update, and it is literally impossible to try and get this done for 300,000 rows.

[Plater]

this is something that only needs to happen once though right?
once things are in order you don't have to worry about it?
fire it off on a slow time and let it go.

[MGM]

I already tried having this run overnight and it only completed about 30% of it. It would take about 3 full days for this to get done. If I were to let this run over the weekend, and if something were to go wrong, I wouldn't know about it until Monday, which probably means I'd have to start it all over again.

I need something that can get this done as quickly as possible, preferably a few hours, and no more than a day (so that I can keep track of it on and off over a 24 hour period).

One idea I had is to import this into a SQL Server database, then do the updating there (using an ASP web app or .net app), and then export it back to an Access database. Is this a viable solution or will I run into the same speed issues?

MGM out

[Plater]

So let me see if I understand this
You have a table like:

A | B | C
------------
1 | 3 | 2
4 | 3 | 6

For example. And you need to go down each row and correct them as to some design.
(for my example I will make them count up)
A | B | C
------------
1 | 2 | 3
3 | 4 | 6

Is that correct? Cause oh-boy would that ever be intensive.
You could try moving it to MSSQL but I don't know how much time that will really even buy you.
You may want to just try and do it section by section. Do it in chunks of 50k entries at a time or something.
If they don't need to be fixed, your time will be greatly reducee I would think.

So If you just keep doing 50k until you think you've gotten them all, then do a scan over the WHOLE thing, the full scan should take way less time because hopefully it won't have to fix any.
Plus, with the 50k entries you'll have the added bonus of only trying to move around a much smaller chunk of data

[MGM]

It's more like this:

A | B | C
------------
1 | 3 | 2
5 | 6 | 4

Needs to turn into this:

A | B | C
------------
1 | 2 | 3
5 | 4 | 6


Notice how the second row changes according to how the first row's order is changed (to numerical)?

And I don't really see how making it into 50k chunks would help, as it would still take the same amount of time to go through and edit all of the rows... except now I have to run the program 6 times to get all 300,000 rows...

MGM out

[Plater]

Well I don't see much difference in our examples (except that your first coloumn never changed)

And doing it in 50k chunks means you can be around for the result and to see if anything goes wrong.

[MGM]

The ABC are the column names, and the numbers are different (132 to 123, and 564 to 546).

I uploaded it to SQL Server and wrote a quick ASP web page to do the conversion and it seems to do it much, much faster. It'll still take a while, but hopefully not as long. Only problem is, I can only do about 300 rows at a time or else it gives me a Timeout error. Any way to get past this? Would doing it in vb.net be faster?

MGM out

[Plater]

Is the timeout error an SQL timeout or a webpage timeout?
If it's a webpage timeout, you can throw it in a windows app and be done with it.

If it's an sql timeout error, you will have to change the timeout value in your connection string

[MGM]

I converted it to an vb.net app and running it right now. A few times during the running I did get a timeout error for this line:

connSQL2.Open()
Dim sqlComm2 = New SqlCommand("UPDATE phasedetails SET " & title & "='" & newDevphasing1 & "', " & title2 & "='" & newDevorien1 & "' WHERE ID=" & id, connSQL2)
sqlComm2.ExecuteNonQuery()
connSQL2.Close()

Specifically, the line sqlComm2.ExecuteNonQuery() is what's giving me the timeout error. Any reasons why? I fixed it (for now) by changing this line earlier in the code:

Dim sqlQuery = New String("SELECT TOP 1000 * FROM phasedetails WHERE " & title & "='" & UCase(oldDevphasing1) & "' OR " & title & "='" & LCase(oldDevphasing1) & "'")

To this line:

Dim sqlQuery = New String("SELECT TOP 1000 id,devphasing1,devphasing2,devphasing3,devorien1,devorien2,devorien3,condphasing1,condphasing2,condphasing3,condorien1,condorien2,condorien3 FROM phasedetails WHERE " & title & "='" & UCase(oldDevphasing1) & "' OR " & title & "='" & LCase(oldDevphasing1) & "'")

However, I have no idea if the timeout error will occur again over the weekend as this is running. Is there something else I should change to prevent this error from occuring?

MGM out

[Plater]

However, I have no idea if the timeout error will occur again over the weekend as this is running. Is there something else I should change to prevent this error from occuring?

There should be a timeout property you can set in your connection string.
(I know msSQL has one)
http://www.connectionstrings.com/

[MGM]

I looked at that page's SQL Server strings and didn't find anything on timeouts...

MGM out

[Plater]

Hmm I guess it's only a timeout for connecting.
You might have to go run through the SQL server itself's properties for a timeout then.

[MGM]

It's fine. I haven't gotten any timeout errors since last time. I got an exception error of some sort while running this over the weekend however, so I STILL haven't gotten this thing running. I figured it out and fixed it, and as of right now everything is running properly. I'm going to be checking this throughout the day to make sure it stays running properly. If so, I'll leave it running over night and hopefully it'll be done by tomorrow.

Thanks for the help everyone!

MGM out