ADODB.Recordset Versus ADO.NET DataSets

[MattJH]

If I use the following to add records from one Database to another :

Do While Not rstHDNET.EOF
 
                With rstLabel
 
                    .AddNew()
 
                    .Fields("PROD_CODE").Value = rstHDNET("Q$LITM").Value
                    .Fields("PROD_DESC").Value = rstHDNET("Q$DSC").Value
                    .Fields("PROD_PRICE").Value = Math.Round(rstHDNET("Q$SRP").Value, 2)
 
                    .Update()
 
                End With
 
                prgMain.Value = rstHDNET.AbsolutePosition
                rstHDNET.MoveNext()
                Application.DoEvents()
 
                lblCount.Text = "Record Number " & rstHDNET.AbsolutePosition & " of " & rstHDNET.RecordCount
 
            Loop

It will do around 130000 records in about 10 minutes.

If I use a dataset and use the mydataadapter.update(mydataset, "tablename") it takes absolutley donkeys years. Like around 3 hours or something.

Am I doing something wrong or what???

[nswan]

post the code for ado.net

[MattJH]

Dim myconnection As New OleDb.OleDbConnection( _
            "Provider=Microsoft.JET.OLEDB.4.0;Data Source=c:\mydatabase.mdb")
        Dim mydataadapter As New OleDb.OleDbDataAdapter("Select * From mytable", myconnection)
        Dim mycommandbuilder As New OleDb.OleDbCommandBuilder(mydataadapter)
        Dim mydataset As New DataSet()
 
        Dim myrow As DataRow
 
        mydataset.Clear()
        mydataadapter.Fill(mydataset, "mytablename")
 
        Do While Not rstHDNET.EOF
 
            With rstLabel
 
                myrow = mydataset.Tables("mytablename").NewRow
 
                myrow.Item("PROD_CODE") = rstHDNET("Q$LITM").Value
                myrow.Item("PROD_DESC") = rstHDNET("Q$DSC").Value
                myrow.Item("PROD_PRICE") = Math.Round(rstHDNET("Q$SRP").Value * 1.175, 2)
 
                mydataset.Tables("mytablename").Rows.Add(myrow)
 
            End With
 
            rstHDNET.MoveNext()
        Loop
 
    End Sub

The above code goes really quickly, but after this I need to update the data adapter to commit the records into the rstHDNET recordset. Right??

I know I have missed the - mydataadapter.update(mydataset, "mytablename")

So where should I put the update??? Inside the loop like I have in the first post?? Or outside the Loop???

Either way it is incredibly slow and generally the program stop responding where or not I have the application.doevents() in the loop.

Maybe I have got this ADO.NET thing all wrong and generally screwed it up.

Please advise,


Many thanks,


Matt.

[nswan]

i would say put the update outside of the loop.

Is it the update that is taking the time?
Try putting it outside the loop and put a message box either side of it so we know if it is the bit taking ages.

You code looks fine though. It's the way that i'd have done it (again that doesn't mean in the best way though!!!!)

Nick

[MattJH]

It is the update that is taking the time. It is mad.

I know there is around 130,000 records in the database but the first example does it without even breaking sweat, but with the dataset it is ending up not responding. Weird indeed.

Cheers

Matt.

[nswan]

it's a shot in the dark but before filling your dataset try applying the tables schemea to it.

myDA.FillSchema(myDS.Tables("whatever"), SchemaType.Mapped)

if mapped doesn't work try source.

I doubt it'll be the problem but worth a try!

[Cander]

Umm. what exactly is your code doing. You are filling the dataset, but then what is this recordset you are looping?

[Edneeis]

What are you using for your UpdateCommand? Did you write it? Use the Wizard? Use the CommandBuilder?

[MattJH]

This is what I am using for my update.

mydataadapter.update(mydataset, "mytablename")

Pretty standard I thought.

Oh nswan I tried the Schema but no success. I thought the schema was just to handle the primary keys???????
What is the difference between the mapped and source schema??

The loop just runs through all the records in one database and adding them into another.

[Cander]

No need to do the loop. Just fill another table in the dataset with the fill just like you alreqady did once.

[MattJH]

Cander I see what you mean and yeah sure I agree, but the initial thing was that I couldn't understand why that it took so long (3 hours) to add records into a database whilst using a dataset and if I used and ado recordset it took 15 mins.

I don't need
Before I changed the loop using the adodb.recordset I was using a datareader but this was the reason I changed to the adodb.recordset because I thought that it may be that that was slowing the process down.

Do While (myDataReader.Read())
 
 
Loop

Would/Should using the loop create a 2:45 hour difference in the simple adding of records????

Personally I wouldn't have thought so. There are not many programs that I would write, that would require mass transferring of data as this one does, but I was amazed at the length of time it took using DataSets. I was just enquiring to see whether or not I had missed something in my code.

Thanks for all you help.

[Andy]

what is rsthdnet and rstlabel? is that your file your using to get info from? just curious cause I am trying to learn how to do this also.

[MattJH]

Hi Phantom,

Well, rstHdnet is holding the information from one database table and the rstLabel is holding table information from another database.

While I understand that using the DataSet feature from ADO.NET in VB.NET is basically an "offline"/"disconnected" version of the database table, I can't understand why the speed would have been sacrificed, with this newer version of ADO.

I just can't understand it.

It is weird.