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:
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.E xecuteNonQuery( )
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.
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:
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
From my own messing around, I've found that removing this line makes everything go tons faster:
objCommUpdate.E xecuteNonQuery( )
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.
Comment