Ms Access Data Updation using VB.Net
-
VB.Net/MS Access. I need to insert records into a particular table (say X)after getting the data from two different tables (say A & B) and doing some permutations and combinations on it. I am getting entire data from tables A & B into two different Datasets. A loop has been written to parse the records from A (dataset), check in B(dataset) and insert the record in table C. However, this entire process is taking a long time. (i.e: in my test run, to insert around 8 lakh records, it took approx 4 hrs). As per my perception, the time delay is being caused since I am firing an insert statement directly on the database during every loop cycle. Is there a way to avoid this and speed up the process. (i.e. Can the records be inserted into a dataset and then all the records be directly inserted into the table from the dataset) I am providing sample code for your reference. Code : ---------------------------------------------------------------------------- str = "select id, code, name, period from EMP2000" ipcon.Open() DA = New OleDbDataAdapter(str, ipcon) DS = New DataSet DA.Fill(DS, "irec") str2 = "select id, code, addr, period from EMPADDR" DA2 = New OleDbDataAdapter(str2, ipcon) DS2 = New DataSet DA2.Fill(DS2, "imsctrl") DV = New DataView(DS2.Tables("imsctrl")) str3 = "select Id, code, name, addr from EMPDATA where period = '2000'" DA3 = New OleDbDataAdapter(str3, ipcon) DS3 = New DataSet DA3.Fill(DS3, "emp") Dim cb As New OleDbCommandBuilder(DA3) Dim cnt As Integer cnt = 0 ''' Dim DataRow As DataRow For Each myrow In DS.Tables("irec").Rows If (myrow(1).Equals(System.DBNull.Value)) Then code1 = "" Else code1 = myrow(1) End If If (myrow(3).Equals(System.DBNull.Value)) Then period1 = "" Else period1 = myrow(3) End If If (myrow(2).Equals(System.DBNull.Value)) Then name1 = "" Else name1 = myrow(2) End If For Each DataRow In DV.Table.Rows If Trim(DataRow(1)) = Trim(code1) And Trim(DataRow(3)) = Trim(period1) Then addr1 = Trim(DataRow(2)) Exit For End If Next dsNewRow = DS3.Tables("emp").NewRow dsNewRow.Item("code") = Trim(code1) dsNewRow.Item("name") = trim(name1) dsNewRow.Item("addr") = Trim(addr1) DS3.Tables("emp").Rows.Add(dsNewRow) DA3.Update(DS3, "emp") Next ---------------------------------------------------------------------------- Is there a way to insert all the records in a dataset and finally update the dataset to the database? If there is such a way then is it really fast. Thnx for your help. With Best Regards, Mayur
-
VB.Net/MS Access. I need to insert records into a particular table (say X)after getting the data from two different tables (say A & B) and doing some permutations and combinations on it. I am getting entire data from tables A & B into two different Datasets. A loop has been written to parse the records from A (dataset), check in B(dataset) and insert the record in table C. However, this entire process is taking a long time. (i.e: in my test run, to insert around 8 lakh records, it took approx 4 hrs). As per my perception, the time delay is being caused since I am firing an insert statement directly on the database during every loop cycle. Is there a way to avoid this and speed up the process. (i.e. Can the records be inserted into a dataset and then all the records be directly inserted into the table from the dataset) I am providing sample code for your reference. Code : ---------------------------------------------------------------------------- str = "select id, code, name, period from EMP2000" ipcon.Open() DA = New OleDbDataAdapter(str, ipcon) DS = New DataSet DA.Fill(DS, "irec") str2 = "select id, code, addr, period from EMPADDR" DA2 = New OleDbDataAdapter(str2, ipcon) DS2 = New DataSet DA2.Fill(DS2, "imsctrl") DV = New DataView(DS2.Tables("imsctrl")) str3 = "select Id, code, name, addr from EMPDATA where period = '2000'" DA3 = New OleDbDataAdapter(str3, ipcon) DS3 = New DataSet DA3.Fill(DS3, "emp") Dim cb As New OleDbCommandBuilder(DA3) Dim cnt As Integer cnt = 0 ''' Dim DataRow As DataRow For Each myrow In DS.Tables("irec").Rows If (myrow(1).Equals(System.DBNull.Value)) Then code1 = "" Else code1 = myrow(1) End If If (myrow(3).Equals(System.DBNull.Value)) Then period1 = "" Else period1 = myrow(3) End If If (myrow(2).Equals(System.DBNull.Value)) Then name1 = "" Else name1 = myrow(2) End If For Each DataRow In DV.Table.Rows If Trim(DataRow(1)) = Trim(code1) And Trim(DataRow(3)) = Trim(period1) Then addr1 = Trim(DataRow(2)) Exit For End If Next dsNewRow = DS3.Tables("emp").NewRow dsNewRow.Item("code") = Trim(code1) dsNewRow.Item("name") = trim(name1) dsNewRow.Item("addr") = Trim(addr1) DS3.Tables("emp").Rows.Add(dsNewRow) DA3.Update(DS3, "emp") Next ---------------------------------------------------------------------------- Is there a way to insert all the records in a dataset and finally update the dataset to the database? If there is such a way then is it really fast. Thnx for your help. With Best Regards, Mayur
This is all something that wound be MUCH faster and better done in the database itself using straight SQL code. This would require completely rewriting this. For example,
For Each DataRow In DV.Table.Rows
If Trim(DataRow(1)) = Trim(code1) And Trim(DataRow(3)) = Trim(period1) Then
addr1 = Trim(DataRow(2))
Exit For
End Ifyou're going through each record in a table and looking for records that has 2 fields that match certain requirements and returning that one record. This can be done in a simple SELECT statement:
SELECT addr1 FROM irec WHERE code1=something AND period1=something
This, of course, won't run because I have no idea what your database structure is, but it'll be FAR, FAR faster than you going through each record in a table and comparing things yourself. You code is just using a database engine for storing data. It's not using the database engine to do any processing! Your code is taking hours to run because you're not using easier and faster methods to process your data. The more you can do in SQL code, the faster this process will go! This is what I mean by competely scrapping what you have, rethinking your logic, and breaking each step of your logic down to simpler steps. Then you can find faster ways of doing each step. I bet you could do this entire thing in a single stored procedure on an SQL server and it would probably take all of 10 seconds to run (depending on the number of records of course!) To bad Access doesn't support true stored procedures! RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
This is all something that wound be MUCH faster and better done in the database itself using straight SQL code. This would require completely rewriting this. For example,
For Each DataRow In DV.Table.Rows
If Trim(DataRow(1)) = Trim(code1) And Trim(DataRow(3)) = Trim(period1) Then
addr1 = Trim(DataRow(2))
Exit For
End Ifyou're going through each record in a table and looking for records that has 2 fields that match certain requirements and returning that one record. This can be done in a simple SELECT statement:
SELECT addr1 FROM irec WHERE code1=something AND period1=something
This, of course, won't run because I have no idea what your database structure is, but it'll be FAR, FAR faster than you going through each record in a table and comparing things yourself. You code is just using a database engine for storing data. It's not using the database engine to do any processing! Your code is taking hours to run because you're not using easier and faster methods to process your data. The more you can do in SQL code, the faster this process will go! This is what I mean by competely scrapping what you have, rethinking your logic, and breaking each step of your logic down to simpler steps. Then you can find faster ways of doing each step. I bet you could do this entire thing in a single stored procedure on an SQL server and it would probably take all of 10 seconds to run (depending on the number of records of course!) To bad Access doesn't support true stored procedures! RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
Thnx for your reply Dave. However, my backend is MS Access. I have no idea how we can write a stored procedure in access. Is there a way to add all the new records in a dataset and then finally uploading the dataset into a table? With Best Regards, Mayur
-
Thnx for your reply Dave. However, my backend is MS Access. I have no idea how we can write a stored procedure in access. Is there a way to add all the new records in a dataset and then finally uploading the dataset into a table? With Best Regards, Mayur
Access doesn't support true stored procedures, so you'll have to write more efficiently in single SELECT statements. Access only supports single statement queries. Doing it in code the way you are now is just so inefficient it's not funny. You're not using the power of the database at all. Like I said, you'll haev to rewrite this so you're code is not going through the same records time and time again "looking" for a record that matches a certain criteria. This is one MAJOR spot where your code is bogging down. Rewrite just this little search as a SELECT statement and you should see a huge imporovement. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome