|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
ADO.net Code contestinsert 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 Show quote "Jesús López" wrote: > 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 > > > You won't find any winners. The poor performance
is not because of your ignorance. And, I too have found DAO to be faster from within .NET than ADODB in .NET via COM Interop. Show quote "bdwest" <bdw***@discussions.microsoft.com> wrote in message news:2A62974C-F3BB-4620-BCDB-18052065F3E3@microsoft.com... > > Thanks for your interest. I am specifically interested in ADO.net methods > that outperform ADODB, so unfortunately DAO is not allowed. > > Thanks > > Brian > "Jesús López" wrote: > >> 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 >> >> >> 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. -- Show quote____________________________________ 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. __________________________________ "Robbe Morris [C# MVP]" <i***@eggheadcafe.com> wrote in message news:%23jeTUnkFGHA.2212@TK2MSFTNGP15.phx.gbl... > You won't find any winners. The poor performance > is not because of your ignorance. > > And, I too have found DAO to be faster from > within .NET than ADODB in .NET via COM > Interop. > > -- > Robbe Morris - 2004/2005 Microsoft MVP C# > http://www.eggheadcafe.com/forums/merit.asp > > > > > > "bdwest" <bdw***@discussions.microsoft.com> wrote in message > news:2A62974C-F3BB-4620-BCDB-18052065F3E3@microsoft.com... >> >> Thanks for your interest. I am specifically interested in ADO.net >> methods >> that outperform ADODB, so unfortunately DAO is not allowed. >> >> Thanks >> >> Brian >> "Jesús López" wrote: >> >>> 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 >>> >>> >>> > > 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 Show quote "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message news:u8lQYKnFGHA.1100@TK2MSFTNGP10.phx.gbl... > 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. > __________________________________ > > "Robbe Morris [C# MVP]" <i***@eggheadcafe.com> wrote in message > news:%23jeTUnkFGHA.2212@TK2MSFTNGP15.phx.gbl... >> You won't find any winners. The poor performance >> is not because of your ignorance. >> >> And, I too have found DAO to be faster from >> within .NET than ADODB in .NET via COM >> Interop. >> >> -- >> Robbe Morris - 2004/2005 Microsoft MVP C# >> http://www.eggheadcafe.com/forums/merit.asp >> >> >> >> >> >> "bdwest" <bdw***@discussions.microsoft.com> wrote in message >> news:2A62974C-F3BB-4620-BCDB-18052065F3E3@microsoft.com... >>> >>> Thanks for your interest. I am specifically interested in ADO.net >>> methods >>> that outperform ADODB, so unfortunately DAO is not allowed. >>> >>> Thanks >>> >>> Brian >>> "Jesús López" wrote: >>> >>>> 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 >>>> >>>> >>>> >> >> > > 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 Show quote "Sahil Malik [MVP C#]" wrote: > 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 > > > > "William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> wrote in message > news:u8lQYKnFGHA.1100@TK2MSFTNGP10.phx.gbl... > > 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. > > __________________________________ > > > > "Robbe Morris [C# MVP]" <i***@eggheadcafe.com> wrote in message > > news:%23jeTUnkFGHA.2212@TK2MSFTNGP15.phx.gbl... > >> You won't find any winners. The poor performance > >> is not because of your ignorance. > >> > >> And, I too have found DAO to be faster from > >> within .NET than ADODB in .NET via COM > >> Interop. > >> > >> -- > >> Robbe Morris - 2004/2005 Microsoft MVP C# > >> http://www.eggheadcafe.com/forums/merit.asp > >> > >> > >> > >> > >> > >> "bdwest" <bdw***@discussions.microsoft.com> wrote in message > >> news:2A62974C-F3BB-4620-BCDB-18052065F3E3@microsoft.com... > >>> > >>> Thanks for your interest. I am specifically interested in ADO.net > >>> methods > >>> that outperform ADODB, so unfortunately DAO is not allowed. > >>> > >>> Thanks > >>> > >>> Brian > >>> "Jesús López" wrote: > >>> > >>>> 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 > >>>> > >>>> > >>>> > >> > >> > > > > > > > bdwest wrote:
Show quote > I have acutally had some people give me a little bit of hard time about using I was thinking pretty much the same thing as Shahil when I saw your > 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 > > "Sahil Malik [MVP C#]" wrote: > >> Y'know what I find funny here is, someone is trying to get hardcore >> performance out of "access". 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). Jesús López wrote:
> May I use DAO 3.6 instead of ADO.NET or ADODB ? No, ODBC is (if I recall correctly). That is, raw C++ with odbc. > > I think DAO is the fastest way to deal with Access databases. > > Regards from Madrid (Spain) > Jesús López FB -- ------------------------------------------------------------------------ Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ > Jesús López wrote: If you use a Ferrari maybe, however with a bicycle it is much slower.> >> 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 > > No, ODBC is (if I recall correctly). That is, raw C++ with odbc. > 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 Cor Ligthert [MVP] wrote:
Show quote > > Jesús López wrote: erm... you can use odbc instead of oledb in ado.net? Accessing access> > > >> 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 > > > > No, ODBC is (if I recall correctly). That is, raw C++ with odbc. > > > 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? is done through oledb, so picking odbc is the same. FB -- ------------------------------------------------------------------------ Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ 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 -- Show quote____________________________________ 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@xs4all.nl> wrote in message news:xn0eh3unbabflr000@news.microsoft.com... > Cor Ligthert [MVP] wrote: > >> > Jesús López wrote: >> > >> >> 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 >> > >> > No, ODBC is (if I recall correctly). That is, raw C++ with odbc. >> > >> 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? > > erm... you can use odbc instead of oledb in ado.net? Accessing access > is done through oledb, so picking odbc is the same. > > FB > > -- > ------------------------------------------------------------------------ > Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com > My .NET blog: http://weblogs.asp.net/fbouma > Microsoft MVP (C#) > ------------------------------------------------------------------------ Bill,
> And folks, even a Ferrari won't beat a bicycle in heavy traffic. Anyone Right answer, when I had sent it I thougt about that, although the city I > who has witnesses traffic in Bangkok can tell you that. > 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. :-) CorBill,
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 Show quote "William (Bill) Vaughn" wrote: > 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. > __________________________________ > > "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message > news:xn0eh3unbabflr000@news.microsoft.com... > > Cor Ligthert [MVP] wrote: > > > >> > Jesús López wrote: > >> > > >> >> 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 > >> > > >> > No, ODBC is (if I recall correctly). That is, raw C++ with odbc. > >> > > >> 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? > > > > erm... you can use odbc instead of oledb in ado.net? Accessing access > > is done through oledb, so picking odbc is the same. > > > > FB > > > > -- > > ------------------------------------------------------------------------ > > Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com > > My .NET blog: http://weblogs.asp.net/fbouma > > Microsoft MVP (C#) > > ------------------------------------------------------------------------ > > > 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. -- Show quote____________________________________ 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. __________________________________ "bdwest" <bdw***@discussions.microsoft.com> wrote in message news:8EE9A803-B9D0-49EC-BB41-105BDA71A79C@microsoft.com... > 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 > > "William (Bill) Vaughn" wrote: > >> 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. >> __________________________________ >> >> "Frans Bouma [C# MVP]" <perseus.usenetNOSPAM@xs4all.nl> wrote in message >> news:xn0eh3unbabflr000@news.microsoft.com... >> > Cor Ligthert [MVP] wrote: >> > >> >> > Jesús López wrote: >> >> > >> >> >> 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 >> >> > >> >> > No, ODBC is (if I recall correctly). That is, raw C++ with odbc. >> >> > >> >> 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? >> > >> > erm... you can use odbc instead of oledb in ado.net? Accessing access >> > is done through oledb, so picking odbc is the same. >> > >> > FB >> > >> > -- >> > ------------------------------------------------------------------------ >> > Get LLBLGen Pro, productive O/R mapping for .NET: >> > http://www.llblgen.com >> > My .NET blog: http://weblogs.asp.net/fbouma >> > Microsoft MVP (C#) >> > ------------------------------------------------------------------------ >> >> >> bdwest wrote:
Show quote > I am sponsoring a contest to see if someone can come up with ADO.net The OleDb provider's ExecuteNonQuery statement is the bottleneck,> 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 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#) ------------------------------------------------------------------------ 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. -- Show quote____________________________________ 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@xs4all.nl> wrote in message news:xn0eh3sw37wvuh004@news.microsoft.com... > 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#) > ------------------------------------------------------------------------ William (Bill) Vaughn wrote:
> Try beginning a transaction before these updates and see what Ah , that's why starting a transaction before the dataset write> 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. started was saving 7-8 seconds! I already wondered why on earth it could be faster when it was in a transction... FB -- ------------------------------------------------------------------------ Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------ 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 Show quote "Frans Bouma [C# MVP]" wrote: > 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#) > ------------------------------------------------------------------------ > 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 Show quote "bdwest" <bdw***@discussions.microsoft.com> escribió en el mensaje news:EADFAF2B-D5E9-4255-A5DF-7AA836066D7F@microsoft.com... >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 > > > > > 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. -- Show quote____________________________________ 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.***@mpvs.org> wrote in message news:%23rZ75yrFGHA.2708@TK2MSFTNGP11.phx.gbl... > 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" <bdw***@discussions.microsoft.com> escribió en el mensaje > news:EADFAF2B-D5E9-4255-A5DF-7AA836066D7F@microsoft.com... >>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 >> >> >> >> >> > > 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 On Wed, 11 Jan 2006 09:49:51 -0800, "William \(Bill\) Vaughn" <billvaRemoveT***@nwlink.com> wrote: ¤ 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) 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 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. -- Show quote____________________________________ 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.***@mpvs.org> wrote in message news:Op5nup7FGHA.3984@TK2MSFTNGP14.phx.gbl... > 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 > > On Thu, 12 Jan 2006 21:51:31 +0100, "Jesús López" <sqlranger.***@mpvs.org> wrote: ¤ 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) 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 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 Show quote "Jesús López" wrote: > 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" <bdw***@discussions.microsoft.com> escribió en el mensaje > news:EADFAF2B-D5E9-4255-A5DF-7AA836066D7F@microsoft.com... > >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 > > > > > > > > > > > > > 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 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 Show quote "Jesús López" wrote: > 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 > > > 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 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 Show quote "Jesús López" wrote: > Ohh :-(. Sorry.I had a lot of grammar mistakes in this message Amazing :)
FB Jesús López wrote: Show quote > 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" <bdw***@discussions.microsoft.com> escribió en el mensaje > news:EADFAF2B-D5E9-4255-A5DF-7AA836066D7F@microsoft.com... > > 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. -- ------------------------------------------------------------------------ Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com My .NET blog: http://weblogs.asp.net/fbouma Microsoft MVP (C#) ------------------------------------------------------------------------  |
|||||||||||||||||||||||
Other interesting topics