Re: I am a winner :-)

Tech-Archive recommends: Fix windows errors by optimizing your registry

Give him the money.
It might also be interesting to see if the Odbc to JET driver approach would
be faster as it has less COM overhead.

--
____________________________________
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.
__________________________________

"Jesús López" <sqlranger.mpv@xxxxxxxx> wrote in message
news:%23rZ75yrFGHA.2708@xxxxxxxxxxxxxxxxxxxxxxx
> Hi Brian,
>
> Here you have a code using the ADO.NET OleDb Provider and StreamWriter
> that is 6 to 8 times faster than your ADODB method:
>
> Sub Reward100(ByVal dbname As String)
>
> 'the first person to provide me with a ADO.net based function that
> will insert the 30,000 records into the database
> 'as shown in the other functions of this class will receive a
> cashier check for US $100. There is only one reward
> 'no other external references aloud
>
> 'reward code goes here
> 'Test inserting 30000 records into an Access database table using
> StreamWriter and ADO.NET.
> 'This function is 6 to 9 times faster than the ADODB method
>
> Dim x As Integer
> Dim d As Date
>
> Dim FormatProvider As IFormatProvider =
> Globalization.CultureInfo.GetCultureInfo("en-US")
> Dim writer As New StreamWriter("Table1.txt")
>
> 'insert records into a file text'
> For x = 1 To 30000
> d = DateAdd(DateInterval.Day, x, #1/1/1925#)
>
> writer.Write("M1") : writer.Write(vbTab)
> writer.Write(d.ToString("d", FormatProvider)) :
> writer.Write(vbTab)
> writer.Write(x) : writer.Write(vbTab)
> writer.Write(x ^ 2) : writer.Write(vbNewLine)
> Next
> writer.Close()
>
> 'create Schema.ini
> writer = New StreamWriter("Schema.ini")
> writer.WriteLine("[Table1.txt]")
> writer.WriteLine("Format = TabDelimited")
> writer.WriteLine("ColNameHeader = False")
> writer.WriteLine("Col1 = name Text Width 50")
> writer.WriteLine("Col2 = recdate DateTime")
> writer.WriteLine("Col3 = num Single")
> writer.WriteLine("Col4 = num2 Single")
> writer.Close()
>
> 'insert records into Access Database
> Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=" & dbname & ";Persist Security Info=False"
> Dim cn As New OleDbConnection(conString)
>
> Dim query As String = "INSERT INTO Table1 " & _
> "SELECT name, recdate, num, num2 FROM [Table1#txt] " & _
> "IN '" & Application.StartupPath & "' 'Text;';"
>
> Dim cmdInsert As New OleDbCommand(query, cn)
> cn.Open()
> cmdInsert.ExecuteNonQuery()
> cn.Close()
> End Sub
>
> Regards from Madrid (Spain)
>
> Jesús López
> VB MVP
>
> "bdwest" <bdwest@xxxxxxxxxxxxxxxxxxxxxxxxx> escribió en el mensaje
> news:EADFAF2B-D5E9-4255-A5DF-7AA836066D7F@xxxxxxxxxxxxxxxx
>>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
>>
>> Thanks
>>
>> Brian
>>
>>
>>
>>
>>
>
>


.

Relevant Pages

  • RE: I am a winner :-)
    ... There is only one reward ... >> insert records into a MS Access 2003 database faster than ADODB methods ... >> records to Access at least 4 times faster using ADODB methods in a Visual ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ADO.net Code contest
    ... > code to insert records into a MS Access 2003 database faster than ... > ADODB methods. ... are a special case in data-access. ... but general purpose data-access code can't do ...
    (microsoft.public.dotnet.framework.adonet)
  • I am a winner :-)
    ... Sub Reward100 ... > insert records into a MS Access 2003 database faster than ADODB methods ... > records to Access at least 4 times faster using ADODB methods in a Visual ...
    (microsoft.public.dotnet.framework.adonet)