ADO.net Code contest

[Jesús López]

Brian,

This kwnoledge base article will likely help you to solve the problem:

http://support.microsoft.com/kb/209805

I'm using the Text ISAM.

Regards from Madrid (Spain)

Jesús López
VB MVP

 

[Jesús López]

Also:

http://support.microsoft.com/?scid=kb;en-us;273458&spid=3042&sid=1335
http://support.microsoft.com/default.aspx?scid=kb;EN-US;283881

 

[William \(Bill\) Vaughn]

If all you want to do is transport data then you don't need JET or SQL
Server. All you need to do is create a delimited file. I worked in the
process control industry for awhile and we found fixed-width or comma or
tab-delimited files transfers are a fast and easy was to move data.
Virtually all DBMS engines know how to import from these file and they can't
be beat for speed.

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

 

[Guest]

Jesus,

I have tried the items suggested by Microsoft with no luck. The registry
keys look OK. The mstext40.dll is there and I even re-registered it and I
still get the ISAM error. It is also suggest re-installing MS office and I
am not going to do that.

Here is the query string produced on my computer.
Is this correct?

"INSERT INTO Table1 SELECT name, recdate, num, num2 FROM [Table1#txt] IN
'D:\VB.Net Projects\DBtest\bin' 'Text;';"

I don’t understand the last part. 'D:\VB.Net Projects\DBtest\bin' 'Text;';"

Also, how does the schema.ini file come into the picture.

Thanks

Brian

 

[Guest]

Oops!! You said me that the error was in cn.Open !!! So the error is in the connection string.

That is nothing to do with ISAMs because I'm not specifiying more than an Access database in the connection string. Perhaps you introduced a litle change to the connection string when you copied and pasted the code.

I'm pasting the code again, but in HTML format. In this way, no line breaks will be introduced. I have simplified the connection string and removed the culture info which is not neccesary, actually this might cause errors.

Sub Reward100(ByVal dbname As String)

Dim x As Integer

Dim d As Date



Dim writer As New StreamWriter("Table1.txt")



'insert records into Table1.txt text file'

For x = 1 To 30000

d = DateAdd(DateInterval.Day, x, #1/1/1925#)



writer.Write("M1") : writer.Write(vbTab)

writer.Write(d.ToString("d"))

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

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



The IN Clause

Jet SQL has an extension to the SQL language: the IN clause. The IN clause allows you to specify tables from external databases. These external databases can be:
a.. Other Access databases
b.. ISAM databases such as text files, dBase, Paradox and so on.
c.. Databases accessed via ODBC
The sintax for the IN clause is :

IN ExternalDatabaseSpecifier

The external database specifier sintax for text files is:

'PathToTheFolderWhereTheTextFilesReside' ' Text;'

The Schema.ini file

Since the text file itself does not has information in it that describes its format (tab delimited, fixed lenght, etc) and structure (field names and field types), a way must exists to specify this information. You can specify this information by including a Schema.ini file in the folder where the text files reside. Please take a look to the following:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Regards from Madrid (Spain)

Jesús López
VB MVP

 

[Frans Bouma [C# MVP]]

Amazing

FB

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

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.

--

 

[Frans Bouma [C# MVP]]

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.

Ah , that's why starting a transaction before the dataset write
started was saving 7-8 seconds! I already wondered why on earth it
could be faster when it was in a transction...

FB


--

 

[Guest]

Ohh :-(. Sorry.I had a lot of grammar mistakes in this message.

 

[=?iso-8859-1?B?SmVz+nMgTPNwZXo=?=]

You are right Bill

The ODBC method takes as long as the ADODB (keyset server side cursor) method.

The ODBC ADO.NET method: 6 seconds
The ADODB method: 6 seconds
The OleDb ADO.NET method: 12 seconds
The "StreamWriter + Just one Query" method that I posed: 1 second.

Here is the code:

Sub OdbcAdoNet(ByVal dbname As String)
Dim cnString As String = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Application.StartupPath & "\" & dbname
Dim cn As New OdbcConnection(cnString)
Dim query As String = "INSERT INTO Table1(Name, recdate, num, num2) VALUES(?,?,?,?)"
Dim cmd As New OdbcCommand(query, cn)
cmd.Parameters.Add("Name", OdbcType.VarChar, 50)
cmd.Parameters.Add("recdate", OdbcType.Date)
cmd.Parameters.Add("num", OdbcType.Real)
cmd.Parameters.Add("num2", OdbcType.Real)
cn.Open()
cmd.Prepare()
For x As Integer = 1 To 30000
Dim d As DateTime = DateAdd(DateInterval.Day, x, #1/1/1925#)
With cmd.Parameters
.Item("Name").Value = "M1"
.Item("recdate").Value = d
.Item("num").Value = x
.Item("num2").Value = x ^ 2
End With
cmd.ExecuteNonQuery()
Next
cn.Close()
End Sub


Public Sub OleDbAdoNet(ByVal dbname As String)
Dim conString As String = "Provider=MicroSoft.Jet.OLEDB.4.0;Data Source=" & dbname
Dim DBcon As New OleDbConnection(conString)
Dim query As String = "insert into Table1 (RecDate, Name, num, num2) values(?, ?, ?, ?)"
Dim mycmd As New OleDbCommand(query, DBcon)
mycmd.Parameters.Add("Recdate", OleDbType.Date)
mycmd.Parameters.Add("Name", OleDbType.VarChar, 50)
mycmd.Parameters.Add("num", OleDbType.Single)
mycmd.Parameters.Add("num2", OleDbType.Single)
DBcon.Open()
mycmd.Prepare()
For x As Integer = 1 To 30000
Dim d As Date = DateAdd(DateInterval.Day, x, #1/1/1925#)
With mycmd.Parameters
.Item("Recdate").Value = d
.Item("Name").Value = "M1"
.Item("num").Value = x
.Item("num2").Value = x ^ 2
End With
mycmd.ExecuteNonQuery()
Next
DBcon.Close()
End Sub


Regards from Madrid (Spain)

Jesús López
VB MVP

 

[Guest]

Jesús in Madrid wins the contest.

He has requested that the $100 be donated

to Medicins Sans Frontiers: http://www.doctorswithoutborders.org/

which I will do.

Thanks for all the discussion and suggestions. I really didn't think anyone
could beat the ADODB method but I was proved wrong.

Please take a look at the following page for the details of the winning entry

http://www.kelbli.net/kb1/AdoNetContest.html

Thanks to all.

Brian

 

[Paul Clement]

¤ 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.

....and less functionality and less stability. ;-)


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Jesús López]

Why are you saying that ODBC has less stability? Are you able prove it ?

ODBC has a long history. It is a very mature techonology, and it is very
fast ...

Regards from Madrid (Spain)

Jesús López
VB MVP

 

[William \(Bill\) Vaughn]

I understand that the ODBC _driver_ for JET databases was originally
written to solve a specific problem with Microsoft Office many years ago and
was never intended to be used for serious production work. It only exposes
the bare essentials of the JET paradigm so it can't do a whole litany of
things. It has nothing to do with the strengths/weaknesses of ODBC.

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

 

[Paul Clement]

¤ Why are you saying that ODBC has less stability? Are you able prove it ?
¤
¤ ODBC has a long history. It is a very mature techonology, and it is very
¤ fast ...
¤

What Bill said. Microsoft recommends that you use the Jet OLEDB provider instead.

And yes, some features are not supported by the MS Access ODBC driver, such as those in ADOX.

Just to repeat, it's not ODBC that is the issue but the MS Access driver.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Jesús López]

I think comparing Jet to SQLite is also interesting. The following code
takes just one second to complete:

Sub SQLiteInsert(ByVal dbname As String)
Dim cnString As String = "Data Source=dbtest1.db"
Dim cn As New SQLiteConnection(cnString)

Dim query As String = "INSERT INTO Table1(Name, recdate, num, num2)
VALUES($Name,$recdate,$num,$num2)"
Dim cmd As New SQLiteCommand(query, cn)
cmd.Parameters.Add("$Name", DbType.String, 50)
cmd.Parameters.Add("$recdate", DbType.DateTime)
cmd.Parameters.Add("$num", DbType.Single)
cmd.Parameters.Add("$num2", DbType.Single)
cn.Open()
Dim tran As SQLiteTransaction = cn.BeginTransaction()
cmd.Prepare()
For x As Integer = 1 To 30000
Dim d As DateTime = DateAdd(DateInterval.Day, x, #1/1/1925#)
With cmd.Parameters
.Item("$Name").Value = "M1"
.Item("$recdate").Value = d
.Item("$num").Value = x
.Item("$num2").Value = x ^ 2
End With
cmd.ExecuteNonQuery()
Next
tran.Commit()
cn.Close()
End Sub

I'm using ADO.NET 2.0 Provider for SQLite:

http://sourceforge.net/projects/sqlite-dotnet2


Regards from Madrid (Spain)

Jesús López
VB MVP