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) Please help me its very urgent !!! Sorry for the long description With Best Regards, Mayur -- modified at 0:10 Wednesday 8th February, 2006
-
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) Please help me its very urgent !!! Sorry for the long description With Best Regards, Mayur -- modified at 0:10 Wednesday 8th February, 2006
Without seeing any of the relevant code, it's impossible to tell you what's going on. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
Without seeing any of the relevant code, it's impossible to tell you what's going on. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
Sorry once again I am providing a huge chunk of code below. Please review it and provide me with some ideas to speed up the process. 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 ---------------------------------------------------------------------------- Dave, 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
-
Sorry once again I am providing a huge chunk of code below. Please review it and provide me with some ideas to speed up the process. 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 ---------------------------------------------------------------------------- Dave, 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
Convert these to parameterized stored procedures. Only grab the data you need at the time. It looks like you're retrieving every employee in the table EMP2000, then going to another table and grabbing all of their addresses, then your grabbing more name and addresses according to some WHERE statement. WHY???? Do you really need all this information at any one point in your code??? I doubt it! You could retrieve all the RELEVANT stuff in one SELECT statement! But, only get what you need at the time. If you updating someones name, you don't need to retrieve the entire database! The only thing slowing your code down is your own logic. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
Convert these to parameterized stored procedures. Only grab the data you need at the time. It looks like you're retrieving every employee in the table EMP2000, then going to another table and grabbing all of their addresses, then your grabbing more name and addresses according to some WHERE statement. WHY???? Do you really need all this information at any one point in your code??? I doubt it! You could retrieve all the RELEVANT stuff in one SELECT statement! But, only get what you need at the time. If you updating someones name, you don't need to retrieve the entire database! The only thing slowing your code down is your own logic. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
Thnx for your reply. I guess you did not understand my requirement. I need to get the address of each employee and insert the info into another table. EMP2000 contains only the emp code,name and period. EMPADDR contains emp code,addr and period. I need to link these and insert each employee record in EMPDATA. With Best Regards, Mayur