Db Interaction spped issue with upgraded code from vb6 to vb.net

[LDD]

Hi Folks

I've recently upgraded an app from vb6 to vb.net

I've run into an issue where it takes about 15-20 times longer to write 1000
records to an MS Access database. (I'm sure it takes that long for 1 record
as well)

I'm writing 300k+ records to the database. The total time in VB6 to do this
is 10 min. in VB.Net it's about 3+ hours.

I'm adding the records using a DAO recordset..

The connection to a DAO.Database is made through a DAO.DbEngine

Dim myDB As DAO.Database
Dim mydbengine As DAO.DBEngine

mydbengine = New DAO.DBEngine
myDB = mydbengine.OpenDatabase(Application.StartupPath +
"\db\databaseName.mdb")

Switching to SQL, MSDE, or any other database technology is not an option at
this point. I must fix this issue.

My questions are as follows:

1. I've read that there is no official support for DAO in VN.Net. Can this
be the real reason
2. Will swithching to ADO.Net and connecting to the db using OleDb speed up
or slow down the process. I have read that using ADO.Net to connect to an MS
Access db, can be slow.
3. Are there other alternatives to speed up the process and get it back to
an acceptable state? Using MS Access and VB.Net of course.

I welcome any help on this issue

thanks folks

LDD

[Ken Tucker [MVP]]

Hi,

Using native dot net functions are always much faster. You will see a
big improvement by switching to ado.net.

Ken
----------------------------

"LDD" wrote:

Hi Folks

I've recently upgraded an app from vb6 to vb.net

I've run into an issue where it takes about 15-20 times longer to write 1000
records to an MS Access database. (I'm sure it takes that long for 1 record
as well)

I'm writing 300k+ records to the database. The total time in VB6 to do this
is 10 min. in VB.Net it's about 3+ hours.

I'm adding the records using a DAO recordset..

The connection to a DAO.Database is made through a DAO.DbEngine

Dim myDB As DAO.Database
Dim mydbengine As DAO.DBEngine

mydbengine = New DAO.DBEngine
myDB = mydbengine.OpenDatabase(Application.StartupPath +
"\db\databaseName.mdb")

Switching to SQL, MSDE, or any other database technology is not an option at
this point. I must fix this issue.

My questions are as follows:

1. I've read that there is no official support for DAO in VN.Net. Can this
be the real reason
2. Will swithching to ADO.Net and connecting to the db using OleDb speed up
or slow down the process. I have read that using ADO.Net to connect to an MS
Access db, can be slow.
3. Are there other alternatives to speed up the process and get it back to
an acceptable state? Using MS Access and VB.Net of course.

I welcome any help on this issue

thanks folks

LDD

[m.posseth]

you will not even get close to the speed of VB6 icw DAO / ADO ( win 32 )

as DAO is fully optimized for the ACCESS database engine ,,,,,,

i do also claim that ADO ( Win 32 ) is faster for updating a ACCESS
database , this because of its connected possibility`s , for reading data
you can gain a lot of benefit with ADO.NET if you did not know how to use
the options in ADO ( win32 ) ( as you could also create a firehose cursor in
this version wich ADO.Net now defaults to )

However using DAO or ADO ( win 32 ) will be much slower because everything
has to be marshalled to the .Net platform using ADO.Net will give you a
speed boost however ADO.Net `s Speed should be more seen as that it is fast
to program with not about its performance when we speak about Access ( it
is another story with SQL where it is superior to everything there was
before )
so if speed is a requirment i can only give you this advice

1. switch to SQL express as database
2. stay with DAO but also stay with VB6
regards

Michel Posseth [MCP]

"Ken Tucker [MVP]" <Ke**********@discussions.microsoft.com> wrote in message
news:37**********************************@microsof t.com...

Hi,

Using native dot net functions are always much faster. You will see a
big improvement by switching to ado.net.

Ken
----------------------------

"LDD" wrote:

Hi Folks

I've recently upgraded an app from vb6 to vb.net

I've run into an issue where it takes about 15-20 times longer to write
1000
records to an MS Access database. (I'm sure it takes that long for 1
record
as well)

I'm writing 300k+ records to the database. The total time in VB6 to do
this
is 10 min. in VB.Net it's about 3+ hours.

I'm adding the records using a DAO recordset..

The connection to a DAO.Database is made through a DAO.DbEngine

Dim myDB As DAO.Database
Dim mydbengine As DAO.DBEngine

mydbengine = New DAO.DBEngine
myDB = mydbengine.OpenDatabase(Application.StartupPath +
"\db\databaseName.mdb")

Switching to SQL, MSDE, or any other database technology is not an option
at
this point. I must fix this issue.

My questions are as follows:

1. I've read that there is no official support for DAO in VN.Net. Can
this
be the real reason
2. Will swithching to ADO.Net and connecting to the db using OleDb speed
up
or slow down the process. I have read that using ADO.Net to connect to an
MS
Access db, can be slow.
3. Are there other alternatives to speed up the process and get it back
to
an acceptable state? Using MS Access and VB.Net of course.

I welcome any help on this issue

thanks folks

LDD

[LDD]

thanks man.

I recently rewrote this section to make use of ADO.Net and it works great.

LDD

"Ken Tucker [MVP]" <Ke**********@discussions.microsoft.com> wrote in message
news:37**********************************@microsof t.com...

Hi,

Using native dot net functions are always much faster. You will see a big improvement by switching to ado.net.

Ken
----------------------------

"LDD" wrote:

Hi Folks

I've recently upgraded an app from vb6 to vb.net

I've run into an issue where it takes about 15-20 times longer to write 1000 records to an MS Access database. (I'm sure it takes that long for 1 record as well)

I'm writing 300k+ records to the database. The total time in VB6 to do this is 10 min. in VB.Net it's about 3+ hours.

I'm adding the records using a DAO recordset..

The connection to a DAO.Database is made through a DAO.DbEngine

Dim myDB As DAO.Database
Dim mydbengine As DAO.DBEngine

mydbengine = New DAO.DBEngine
myDB = mydbengine.OpenDatabase(Application.StartupPath +
"\db\databaseName.mdb")

Switching to SQL, MSDE, or any other database technology is not an option at this point. I must fix this issue.

My questions are as follows:

1. I've read that there is no official support for DAO in VN.Net. Can this be the real reason
2. Will swithching to ADO.Net and connecting to the db using OleDb speed up or slow down the process. I have read that using ADO.Net to connect to an MS Access db, can be slow.
3. Are there other alternatives to speed up the process and get it back to an acceptable state? Using MS Access and VB.Net of course.

I welcome any help on this issue

thanks folks

LDD