|
|
You're in the
dotnet ado.net
group:ADO faster than ADO.net ?
dotnet ado.net:
to insert records into an MS Access database 5-6 times faster than I can using ADO.net. We may be inserting tens of thousands of records at a time and performance becomes an issue. Let me also say I am required to use Access by company and clients so I am stuck with that limitation. Most of the stuff I have read implies that ADO.net should be faster. I find just the opposite for inserting large number or records in Access. I don't know if I am being inefficient in the ADO.net code or what. I would appreciate any input. I have a small vb.net project that inserts 30,000 records into a database using ADO as shown in the code fragment below. It also includes 3 ADO.net methods as well. It tells you the time in seconds it took to complete the write. On my machine the ADODB record set method inserts 30,000 records in 6 seconds. The ADO.net methods take 30-37 seconds depending on method. If you are interested, the project is available at this URL: http://www.kelbli.net/pub/transfer/DBtest.zip The database is in the bin directory (make sure you extract with relative paths enabled). Any help would be appreciated. Brian ********************************************************* ADO code fragment: 'create and open record set Dim rstData As ADODB.Recordset rstData = New ADODB.Recordset rstData.Open("Table1", db, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdTable) 'insert records For x = 1 To 30000 rstData.AddNew() d = DateAdd(DateInterval.Day, x, #1/1/1925#) rstData.Fields.Item(0).Value = "M1" rstData.Fields.Item(1).Value = d rstData.Fields.Item(2).Value = x rstData.Fields.Item(3).Value = x ^ 2 rstData.Update()
Cor,
Would you mind sending me back your changes so I can see what you did. Feel free to email me at bw@kelbli.com. Thanks for your input and advice. Brian [quoted text, click to view] "Cor Ligthert" wrote:
> Brian, > > Interesting. I tested it. > > Your code with the parameters was not ideal. > Making that better I got the same result as with the insert SQLString > > I also added a procedure with a stored procedure and could win 20%. > > However it was still 4 a 5 times slower than ADODB. > > I know that it does not help, however maybe was you interested if others > would try that nice test from you. > > By the way, all that setting to nothing is for nothing, it goes all out of > scope. > > I hope this helps something, > > Cor > >
Brian,
Interesting. I tested it. Your code with the parameters was not ideal. Making that better I got the same result as with the insert SQLString I also added a procedure with a stored procedure and could win 20%. However it was still 4 a 5 times slower than ADODB. I know that it does not help, however maybe was you interested if others would try that nice test from you. By the way, all that setting to nothing is for nothing, it goes all out of scope. I hope this helps something, Cor
On Wed, 29 Jun 2005 07:31:06 -0700, bdwest
[quoted text, click to view] <bdwest@discussions.microsoft.com> wrote: >Any help would be appreciated. A side note from my own experience with DAO is that when using the ..AddNew method to add 1000's of records, the app gradually got slower and slower.... However, if I used the SQL Insert Into statement and the MyDatabase.Execute mySQLstatement, the process shot through... Perhaps that might be a better avenue for you to try, rather then physically adding the records to the "recordset" Tym Please do not adjust your brain, there is a fault with reality.
Brian,
The other one I have not anymore, I made this one from that. There is now some time involved with creating the sp of couse, however that is in my opinion nothing. \\\ 'Test inserting 30000 records into an Access database table using ADO.net 'with the parameters.add method and sp ' create the connection string Dim conString As String = "Provider=MicroSoft.Jet.OLEDB.4.0;" & _ "Data Source=" & dbname ' open the connection Dim DBcon As New OleDbConnection(conString) Dim mycmd As New OleDbCommand("Create Procedure MyProc " & _ "(@RecDate DateTime, @Name Text(50), @num int, @num2 int)" & _ "As insert into Table1 (RecDate, Name, num, num2) values" & _ "(@RecDat, @Name, @num, @num2)", DBcon) DBcon.Open() mycmd.ExecuteNonQuery() 'insert the records mycmd.CommandText = "MyProc" mycmd.CommandType = CommandType.StoredProcedure mycmd.Parameters.Add(New OleDb.OleDbParameter("@RecDate", OleDb.OleDbType.Date)) mycmd.Parameters.Add(New OleDb.OleDbParameter("@Name", OleDb.OleDbType.VarChar)) mycmd.Parameters.Add(New OleDb.OleDbParameter("nume", OleDb.OleDbType.Integer)) mycmd.Parameters.Add(New OleDb.OleDbParameter("@num2", OleDb.OleDbType.Integer)) mycmd.Parameters.Add(New OleDb.OleDbParameter("", OleDb.OleDbType.Integer)) Dim d As DateTime For x As Integer = 1 To 30000 d = DateAdd(DateInterval.Day, x, #1/1/1925#) mycmd.Parameters(0).Value = d mycmd.Parameters(1).Value = "M3" mycmd.Parameters(2).Value = x mycmd.Parameters(3).Value = x mycmd.Parameters(4).Value = 0 mycmd.ExecuteNonQuery() Next mycmd.CommandText = "Drop Procedure MyProc" mycmd.CommandType = CommandType.Text mycmd.ExecuteNonQuery() DBcon.Close() /// I hope this helps, Cor
Brian,
[quoted text, click to view] > The Sp is defined with 4 parameters. It is obivously needed because it Than at least luckily for is that we both don't understand it.> won't run without the two lines. I don't understand why. > :-)) Cor
Cor,
Thanks for the code. In my test it was faster than all of the other .net methods. I appreciate your input. Thanks again Brian [quoted text, click to view] "Cor Ligthert" wrote:
> Brian, > > The other one I have not anymore, I made this one from that. > > There is now some time involved with creating the sp of couse, however that > is in my opinion nothing. > > \\\ > 'Test inserting 30000 records into an Access database table using ADO.net > 'with the parameters.add method and sp > ' create the connection string > Dim conString As String = "Provider=MicroSoft.Jet.OLEDB.4.0;" & _ > "Data Source=" & dbname > ' open the connection > Dim DBcon As New OleDbConnection(conString) > Dim mycmd As New OleDbCommand("Create Procedure MyProc " & _ > "(@RecDate DateTime, @Name Text(50), @num int, @num2 int)" & _ > "As insert into Table1 (RecDate, Name, num, num2) values" & _ > "(@RecDat, @Name, @num, @num2)", DBcon) > DBcon.Open() > mycmd.ExecuteNonQuery() > 'insert the records > mycmd.CommandText = "MyProc" > mycmd.CommandType = CommandType.StoredProcedure > mycmd.Parameters.Add(New OleDb.OleDbParameter("@RecDate", > OleDb.OleDbType.Date)) > mycmd.Parameters.Add(New OleDb.OleDbParameter("@Name", > OleDb.OleDbType.VarChar)) > mycmd.Parameters.Add(New OleDb.OleDbParameter("nume", > OleDb.OleDbType.Integer)) > mycmd.Parameters.Add(New OleDb.OleDbParameter("@num2", > OleDb.OleDbType.Integer)) > mycmd.Parameters.Add(New OleDb.OleDbParameter("", > OleDb.OleDbType.Integer)) > Dim d As DateTime > For x As Integer = 1 To 30000 > d = DateAdd(DateInterval.Day, x, #1/1/1925#) > mycmd.Parameters(0).Value = d > mycmd.Parameters(1).Value = "M3" > mycmd.Parameters(2).Value = x > mycmd.Parameters(3).Value = x > mycmd.Parameters(4).Value = 0 > mycmd.ExecuteNonQuery() > Next > mycmd.CommandText = "Drop Procedure MyProc" > mycmd.CommandType = CommandType.Text > mycmd.ExecuteNonQuery() > DBcon.Close() > /// > > I hope this helps, > > Cor > >
Cor,
One more question for you. Why do you have to have to add this line mycmd.Parameters.Add(New OleDb.OleDbParameter("", OleDb.OleDbType.Integer)) and this line mycmd.Parameters(4).Value = 0 The Sp is defined with 4 parameters. It is obivously needed because it won't run without the two lines. I don't understand why. Thanks Brian [quoted text, click to view] "Cor Ligthert" wrote:
> Brian, > > The other one I have not anymore, I made this one from that. > > There is now some time involved with creating the sp of couse, however that > is in my opinion nothing. > > \\\ > 'Test inserting 30000 records into an Access database table using ADO.net > 'with the parameters.add method and sp > ' create the connection string > Dim conString As String = "Provider=MicroSoft.Jet.OLEDB.4.0;" & _ > "Data Source=" & dbname > ' open the connection > Dim DBcon As New OleDbConnection(conString) > Dim mycmd As New OleDbCommand("Create Procedure MyProc " & _ > "(@RecDate DateTime, @Name Text(50), @num int, @num2 int)" & _ > "As insert into Table1 (RecDate, Name, num, num2) values" & _ > "(@RecDat, @Name, @num, @num2)", DBcon) > DBcon.Open() > mycmd.ExecuteNonQuery() > 'insert the records > mycmd.CommandText = "MyProc" > mycmd.CommandType = CommandType.StoredProcedure > mycmd.Parameters.Add(New OleDb.OleDbParameter("@RecDate", > OleDb.OleDbType.Date)) > mycmd.Parameters.Add(New OleDb.OleDbParameter("@Name", > OleDb.OleDbType.VarChar)) > mycmd.Parameters.Add(New OleDb.OleDbParameter("nume", > OleDb.OleDbType.Integer)) > mycmd.Parameters.Add(New OleDb.OleDbParameter("@num2", > OleDb.OleDbType.Integer)) > mycmd.Parameters.Add(New OleDb.OleDbParameter("", > OleDb.OleDbType.Integer)) > Dim d As DateTime > For x As Integer = 1 To 30000 > d = DateAdd(DateInterval.Day, x, #1/1/1925#) > mycmd.Parameters(0).Value = d > mycmd.Parameters(1).Value = "M3" > mycmd.Parameters(2).Value = x > mycmd.Parameters(3).Value = x > mycmd.Parameters(4).Value = 0 > mycmd.ExecuteNonQuery() > Next > mycmd.CommandText = "Drop Procedure MyProc" > mycmd.CommandType = CommandType.Text > mycmd.ExecuteNonQuery() > DBcon.Close() > /// > > I hope this helps, > > Cor > >
If I may...the first line you ask about creates a Parameter object that
acts as the "placeholder" for the query for the command object. In the other line, you actually assign a value to that Parameter. When the command object is executed, the value in teh Parameter object is passed to the query. [quoted text, click to view] bdwest wrote:
> Cor, > > One more question for you. Why do you have to have to > > add this line > > mycmd.Parameters.Add(New OleDb.OleDbParameter("", OleDb.OleDbType.Integer)) > > and this line > > mycmd.Parameters(4).Value = 0 > > The Sp is defined with 4 parameters. It is obivously needed because it > won't run without the two lines. I don't understand why. > > Thanks > > Brian
JerryH,
If you look at Cor's code the SP is defined with 4 parameters. However, 5 parameters must be passed to get it to run. You get an exception otherwise and I don't understand why. I was asking him about the two extra lines. There are only four fields in the database. Any ideas? Thanks Brian [quoted text, click to view] "JerryH" wrote:
> If I may...the first line you ask about creates a Parameter object that > acts as the "placeholder" for the query for the command object. > > In the other line, you actually assign a value to that Parameter. When > the command object is executed, the value in teh Parameter object is > passed to the query. > > bdwest wrote: > > Cor, > > > > One more question for you. Why do you have to have to > > > > add this line > > > > mycmd.Parameters.Add(New OleDb.OleDbParameter("", OleDb.OleDbType.Integer)) > > > > and this line > > > > mycmd.Parameters(4).Value = 0 > > > > The Sp is defined with 4 parameters. It is obivously needed because it > > won't run without the two lines. I don't understand why. > > > > Thanks > > > > Brian > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |