Re: ADO.net Code contest
- From: "William \(Bill\) Vaughn" <billvaRemoveThis@xxxxxxxxxx>
- Date: Wed, 11 Jan 2006 09:47:20 -0800
Try beginning a transaction before these updates and see what happens. As it
is JET won't write these rows to the database until it's "idle" so it's not
really measuring anything except moving rows to JET's database cache in
memory. When uploading to SQL Server (and not using BCP) we roll our own
INSERT statements and fill in the values using string replacement tags.
However, I expect the Parameter approach is going to fare well against the
raw INSERT technique since you can't batch them in JET. You might also try
executing the INSERT asynchronously and do the prep work while you're
waiting.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xxxxxxxxx> wrote in message
news:xn0eh3sw37wvuh004@xxxxxxxxxxxxxxxxxxxxx
> bdwest wrote:
>
>> I am sponsoring a contest to see if someone can come up with ADO.net
>> code to insert records into a MS Access 2003 database faster than
>> ADODB methods (MS ActiveX Data Object 2.x library). My experiece has
>> shown that I can write records to Access at least 4 times faster
>> using ADODB methods in a Visual Studio 2003/2005 project as compared
>> to any ADO.net methods. I want to see if this is due to my ignorance
>> or a problem with ADO.net and Access 2003 databases.
>>
>> From the link below, there is a test project available that has five
>> methods of writing to an Access database, an ADODB method and 4
>> versions of ADO.net methods. The time to write 30,000 records is
>> timed for each method. The code is written in VB.net.
>>
>> There is a $100 reward to the first person who can use ADO.net
>> methods that beat the ADODB methods working within the confines of
>> the rules. Note there is only one reward.
>>
>> Please see the following link for full details.
>>
>> http://www.kelbli.net/kb1/AdoNetContest.html
>
> The OleDb provider's ExecuteNonQuery statement is the bottleneck,
> according to my profiling.
>
> Nevertheless, your code is also pretty flawed. If I look at the
> paramsAddInsert method, you don't re-use the parameters in the command,
> nor do you prepare the command for re-use.
>
> Re-using the parameters and preparing the statement, also specifying a
> length for the varchar makes the code go from 37 to 13 seconds. (adodb
> is 7).
>
> So if I use this code:
> Public Function AdoDotNet_paramsAddInsert(ByVal dbname As String) As
> Boolean
> 'Test inserting 30000 records into an Access database table using
> ADO.net
> 'with the parameters.add method
>
> Dim DBcon As OleDbConnection
> Dim mycmd As New OleDbCommand
> Dim x As Integer
> Dim d As Date
>
> ' create the connection string
> Dim conString As String = "Provider=MicroSoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & dbname
>
> 'Create the insert statement with place holders for particular values
> mycmd.CommandText = "insert into Table1 (RecDate, Name, num, num2)
> values(?, ?, ?, ?)"
>
> ' open the connection
> DBcon = New OleDbConnection(conString)
> mycmd.Connection = DBcon
> DBcon.Open()
> mycmd.Parameters.Add(New OleDb.OleDbParameter("@RecDate",
> OleDb.OleDbType.Date))
> mycmd.Parameters.Add(New OleDb.OleDbParameter("@Name",
> OleDb.OleDbType.VarChar, 50))
> mycmd.Parameters.Add(New OleDb.OleDbParameter("@num",
> OleDb.OleDbType.Integer))
> mycmd.Parameters.Add(New OleDb.OleDbParameter("@num2",
> OleDb.OleDbType.Integer))
> mycmd.Prepare()
> 'insert the records
> For x = 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 ^ 2
>
> mycmd.ExecuteNonQuery()
> Next
>
> DBcon.Close()
> mycmd = Nothing
> DBcon = Nothing
> End Function
>
> I get 13 seconds. (funny thing is that release builds or debug builds
> don't matter much, which likely is caused by the fact that the overall
> time is spend in the non-managed oledb code)
>
> This is the fastest you can possibly get. The rest of the time is
> spend in ExecuteNonQuery.
>
> For bulk inserts, this is a pretty solid method, though bulk inserts
> are a special case in data-access. In general, bulk inserts are often
> better done by special programs like DTS than by general purpose
> data-access methods simply because bulk inserts can take shortcuts like
> the code above does, but general purpose data-access code can't do
> that, which means that such code has to prepare a query for every row
> to insert, which is also why a dataset insert is much slower than this
> special purpose method.
>
> btw, a simple Prepare call and a length specification for the varchar
> brings down the proc method from 28 to 19 seconds.
>
> Frans
>
> --
> ------------------------------------------------------------------------
> Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
> My .NET blog: http://weblogs.asp.net/fbouma
> Microsoft MVP (C#)
> ------------------------------------------------------------------------
.
- Follow-Ups:
- Re: ADO.net Code contest
- From: Frans Bouma [C# MVP]
- Re: ADO.net Code contest
- References:
- Re: ADO.net Code contest
- From: Frans Bouma [C# MVP]
- Re: ADO.net Code contest
- Prev by Date: Re: ADO.net Code contest
- Next by Date: Re: I am a winner :-)
- Previous by thread: Re: ADO.net Code contest
- Next by thread: Re: ADO.net Code contest
- Index(es):
Relevant Pages
|
