Iteration SLOW!!!

[Tom Holmes Jr.]

WTF did Microsoft do when it came to ADO.NET????????????????????????

Using an OLEDB connection and Dataset it took hours to iterate through 5000+
records in one simple table.
That is horrible. If I go into Access 2003 and do the same thing with old
ADO and create a standard ADO resultset (recordset) ... I can iterate
through 5000+ records in seconds.

All I want my VB.NET application to do is copy data from an Access database
to a SQL Compact Edition SDF file on the desktop. I can connect to the
Access database, get data, and the iteration is damned slow ...
I can open to the SDF file with OLEDB ... but I haven't gotten to do the
insert statements yet ...

But anyway ... What is up with an iteration being super-slow? Thanks!


Tom

 

[pamela fluente]

WTF did Microsoft do when it came to ADO.NET????????????????????????

Using an OLEDB connection and Dataset it took hours to iterate through 5000+
records in one simple table.
That is horrible. If I go into Access 2003 and do the same thing with old
ADO and create a standard ADO resultset (recordset) ... I can iterate
through 5000+ records in seconds.

All I want my VB.NET application to do is copy data from an Access database
to a SQL Compact Edition SDF file on the desktop. I can connect to the
Access database, get data, and the iteration is damned slow ...
I can open to the SDF file with OLEDB ... but I haven't gotten to do the
insert statements yet ...

But anyway ... What is up with an iteration being super-slow? Thanks!

Tom

To move the data you can use this application (which uses oledb):

http://151.100.3.84/DataTimeUniversal/download.aspx

May use the Quick Export feature. Let me know if you still see
slowness.

-P

 

[Patrice]

My first thought would be doing too much work within the iteration. Perhaps
telling us what you are doing would help ?

 

[Smokey Grindle]

I can go through 20,000 records in under a second, you have to be doing
something else wrong... ADO.NET especially in 2.0 isn't slow, its actually
pretty well optimized

 

[Rad [Visual C# MVP]]

WTF did Microsoft do when it came to ADO.NET????????????????????????

Using an OLEDB connection and Dataset it took hours to iterate through 5000+
records in one simple table.
That is horrible. If I go into Access 2003 and do the same thing with old
ADO and create a standard ADO resultset (recordset) ... I can iterate
through 5000+ records in seconds.

All I want my VB.NET application to do is copy data from an Access database
to a SQL Compact Edition SDF file on the desktop. I can connect to the
Access database, get data, and the iteration is damned slow ...
I can open to the SDF file with OLEDB ... but I haven't gotten to do the
insert statements yet ...

But anyway ... What is up with an iteration being super-slow? Thanks!


Tom

Something is very wrong ... are you doing some other processing in the
loop?

Also, you can get some gains if you use a datareader directly rather
than a dataset

 

[Tom Holmes Jr.]

Ok guys ... what am I doing in the loop .... NOTHING!
Nothing more than displaying my data to the screen ... just to test out the
iteration and to see the data.

I guess I'll just have to post my code ...


'Define the connectors
Dim oConn As OleDbConnection
Dim oComm As OleDbCommand
Dim oData As OleDbDataAdapter
Dim resultSet As New DataSet
Dim oConnect, oQuery As String

'Define connection string
oConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
FileName & ";User ID=Admin;Password="

'Query String
oQuery = ""
oQuery = oQuery + "SELECT ServiceSitesId, City, "
oQuery = oQuery + "LastName, Address, Directions1, Directions2 "
oQuery = oQuery + "FROM [Service_Sites 2007] "
oQuery = oQuery + "ORDER BY ServiceSitesId "

'Instantiate the connectors
oConn = New OleDbConnection(oConnect)
oComm = New OleDbCommand(oQuery, oConn)
oData = New OleDbDataAdapter(oQuery, oConn)

Try

'Open connection
oConn.Open()

'Fill dataset
oData.Fill(resultSet, "Service_Sites")

'======================================================
Dim myDataRow1 As DataRow
'This loop is very, very, very, very, very slow ... and it's not
doing much .... this should be LIGHTNING FAST!
For Each myDataRow1 In resultSet.Tables("Service_Sites").Rows
Console.WriteLine("Data: ServiceSitesId=" &
myDataRow1("ServiceSitesId").ToString())
Console.WriteLine("Data: LastName=" &
myDataRow1("LastName").ToString())
Console.WriteLine("Data: City=" &
myDataRow1("City").ToString())
Console.WriteLine("Data: Address=" &
myDataRow1("Address").ToString())
Console.WriteLine("Data: Directions1=" &
myDataRow1("Directions1").ToString())
Console.WriteLine("Data: Directions2=" &
myDataRow1("Directions2").ToString())
Console.WriteLine()
Next
'======================================================

'Close connection
oConn.Close()

Catch ex As OleDb.OleDbException
Catch ex As Exception

'Show error message and exit
MsgBox(ex.Message & vbCrLf & ex.StackTrace)

Finally

'Dispose the connector objects
If Not (oConn Is Nothing) Then oConn.Dispose()
oConn = Nothing
If Not (oComm Is Nothing) Then oComm.Dispose()
oComm = Nothing
If Not (oData Is Nothing) Then oData.Dispose()
oData = Nothing

End Try

'Return results
Return resultSet

 

[Paul Clement]

¤ WTF did Microsoft do when it came to ADO.NET????????????????????????
¤
¤ Using an OLEDB connection and Dataset it took hours to iterate through 5000+
¤ records in one simple table.
¤ That is horrible. If I go into Access 2003 and do the same thing with old
¤ ADO and create a standard ADO resultset (recordset) ... I can iterate
¤ through 5000+ records in seconds.
¤
¤ All I want my VB.NET application to do is copy data from an Access database
¤ to a SQL Compact Edition SDF file on the desktop. I can connect to the
¤ Access database, get data, and the iteration is damned slow ...
¤ I can open to the SDF file with OLEDB ... but I haven't gotten to do the
¤ insert statements yet ...
¤
¤ But anyway ... What is up with an iteration being super-slow? Thanks!

Wouldn't it be easier to use SQL to perform the export, or for that matter the SQLBulkCopy class?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[pamela fluente]

'Fill dataset
oData.Fill(resultSet, "Service_Sites")

'======================================================
Dim myDataRow1 As DataRow
'This loop is very, very, very, very, very slow ... and it's not
doing much .... this should be LIGHTNING FAST!
For Each myDataRow1 In resultSet.Tables("Service_Sites").Rows
Console.WriteLine("Data: ServiceSitesId=" &
myDataRow1("ServiceSitesId").ToString()) ....
Next
'======================================================

This has nothing to do with the Query. It's normal that this kind of
display is slow.
Same is if you use labels to do the display.

If you want to go fast, create a stringbuilder and append to it.
Hence display it at once on a textbox when you are finished or,
better, when it reaches a maximum length you have decided.

Let me know.

-P