ADO.net Code contest

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
May I use DAO 3.6 instead of ADO.NET or ADODB ?

I think DAO is the fastest way to deal with Access databases.

Regards from Madrid (Spain)
Jesús López

VB MVP
 
Thanks for your interest. I am specifically interested in ADO.net methods
that outperform ADODB, so unfortunately DAO is not allowed.

Thanks

Brian
 
Yes, and a bicycle can get through city traffic faster than a car, but you
get kinda wet in the rain and you can't carry much where you're going.

--
____________________________________
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 said:
May I use DAO 3.6 instead of ADO.NET or ADODB ?

I think DAO is the fastest way to deal with Access databases.

Regards from Madrid (Spain)
Jesús López
Click to expand...

No, ODBC is (if I recall correctly). That is, raw C++ with odbc.

FB

--
 
Y'know what I find funny here is, someone is trying to get hardcore
performance out of "access".
But this contest makes a good point - ADOc is still better in certain
borderline cases.

- Sahil Malik
 
Jesús López said:
No, ODBC is (if I recall correctly). That is, raw C++ with odbc.
Click to expand...
If you use a Ferrari maybe, however with a bicycle it is much slower.

What has ODBC to do with comparing DAO, ADO or ADONET?

There have been more discussions about this subject in this newsgroup where
Paul Clement was often in involved. (There are more regulars, however Paul
is in my mind equivalent for DAO).

In my opinion not any homemade software can be as advice to deal with
Access. because you never know if it is reliable for the given problem.

Which as well does not mean that I would ever advice to use DAO in any Net
environment.

Just my thought,

Cor
 
bdwest said:
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
Click to expand...

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

--
 
Cor said:
If you use a Ferrari maybe, however with a bicycle it is much slower.

What has ODBC to do with comparing DAO, ADO or ADONET?
Click to expand...

erm... you can use odbc instead of oledb in ado.net? Accessing access
is done through oledb, so picking odbc is the same.

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
 
Ah, actually, accessing an Access database is done through JET. If you use a
generic interface like OLE DB or ODBC from any data access interface (DAI)
you get...

DAI -> System.Data.OleDb -> JET .NET provider -> JET API
or
DAI -> System.Data.Odbc-> JET ODBC driver-> JET API
or
DAI -> ODBC API-> JET ODBC driver-> JET API
or
DAI -> OLE DB COM interface-> JET OLE DB provider-> JET API
unless you use DAO
DAO->JET API

Trying to get more performance out of JET by choosing one provider is an
interesting approach, but hardly useful. ODBC, OLE DB are
"one-size-fits-all" (OSFA) interfaces designed to permit a (mostly) common
DAI to be able to access a variety of backend data sources like JET, SQL
Server, Oracle, DB2 and the rest. To access a data source with the least
overhead, you need to use the "native language" interface--not a generic
intermediate layer. Most DBMSs has their own unique native language. For JET
the only public interface is DAO as its API interface is not published
outside of Microsoft. For SQL Server the public interface is DB-Library (the
TDS protocol is proprietary).

However, arguing which is faster is academic. It's not how fast you ask the
question, its how fast the question can be answered that determines true
database performance. DAO, RDO, ADO, ADO.NET and the OSFA interfaces were
not designed for bulk operations as your test suggests. They are query
interfaces and they all wait at the same speed. For SQL Server (and other
business-class DBMSs), there are special utilities (BCP/DTS etc.) that are
specifically designed do this work. JET has no such utility--but Access
does. Access knows how to use JET APIs and locking schemes to import data
faster than any DAO-driven program can. There are also JET Replication
Object (JRO) COM APIs that developers can use to manipulate JET databases.

Again, the ability to flood a database with data is not a typical
application task--especially not with a database engine like JET. The
various data access interfaces listed above were designed to make
programming easier, to make development of code generators easier, to make
it possible to access data without having to know how to use the complicated
ODBC API and the mind-boggling OLE DB COM++ interface. ADO.NET has done
something different for most backend data sources--it has exposed new
"managed" .NET Data Providers that speak the native tongue of specific
backends. These provider are widely available. The Framework ships with SQL
Server and Oracle managed providers as well as an Odbc .NET Provider. It
also includes an OleDb .NET Provider that's suitable only for cases where a
"native" managed provider is not available. This is the case for JET and one
reason why it takes longer to get data to and from JET via ADO.NET.

And folks, even a Ferrari won't beat a bicycle in heavy traffic. Anyone who
has witnesses traffic in Bangkok can tell you that.

hth

--
____________________________________
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.
__________________________________
 
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.
__________________________________
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Bill,
And folks, even a Ferrari won't beat a bicycle in heavy traffic. Anyone
who has witnesses traffic in Bangkok can tell you that.
Click to expand...

Right answer, when I had sent it I thougt about that, although the city I
was thinking on was Amsterdam, did you ever see all those bicycles in
Amsterdam?

Although that it is in the city where Frans lives the same.

:-)

Cor
 
Jesus.

I am have a little problem running your code. I get an error on the cn.open
line
the error is

"Could not find installable ISAM"

Any Ideas?

Out broadband connection has been down all day and I am logged in via modem
which is painfully slow so can not really reasearch the issue.

Please let me know if you have any ideas as to how to correct the problem.

Thanks

Brian






Jesús López said:
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 said:
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
Click to expand...
Click to expand...
 
Frans,

Thanks for pointing out problems with my code. Your changes show
significant improvements. I am seeing about 8 seconds on the ADODB method
and about 16-18 seconds with your changes. I really appreciate your input.

Brian
 
I have acutally had some people give me a little bit of hard time about using
Access. I am currently working on a surface water hydrologic model that has
to interface with a groundwater model that is run by a consultant of ours.
Access was chosen as the "best" way to transfer the data to another party and
still allow us to summarize and answer question about the data using SQL or
tools in Access. After a model run I may have have 100,000 + records to
write to the database so I want to do it as fast as possible. Hence this
contest.

I am an water resources engineer who does quite a bit of software
development in the area of hydrology. I had minimal computer science
training back in my college days - not that it would matter after nearly
20-years. I have learned everything on my own and don't necessarily know the
best way of getting something done. I can normally figure out a way to do
something but it may be flawed as Frans points out. I have had some
excellent input here and I appreciate the interest.

Brian
 
Bill,

Please see the reply to Sahil for the reason of going through this
excercise. I agree, this is not a typical use of Access.

Thanks for your comments.

Brian
 
bdwest said:
I have acutally had some people give me a little bit of hard time about using
Access. I am currently working on a surface water hydrologic model that has
to interface with a groundwater model that is run by a consultant of ours.
Access was chosen as the "best" way to transfer the data to another party and
still allow us to summarize and answer question about the data using SQL or
tools in Access. After a model run I may have have 100,000 + records to
write to the database so I want to do it as fast as possible. Hence this
contest.

I am an water resources engineer who does quite a bit of software
development in the area of hydrology. I had minimal computer science
training back in my college days - not that it would matter after nearly
20-years. I have learned everything on my own and don't necessarily know the
best way of getting something done. I can normally figure out a way to do
something but it may be flawed as Frans points out. I have had some
excellent input here and I appreciate the interest.

Brian
Click to expand...

I was thinking pretty much the same thing as Shahil when I saw your
post, but I can see your point. Although, exchanging SQL Server Express
MDF files isn't really harder than using MDB files, the DB is free (with
a good upgrade path and all), no big learning curve, it performs very
well - a very good/solid RDBMS compared to Jet (access). You can
definitely learn this on your own too. And as for using Access' tools,
you can create "linked" tables in access as well... If you want speed,
it's worth looking into (there's MANY more benefits - not just speed).
 
Back
Top