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

On 11 Lug, 06:50, "Tom Holmes Jr."  wrote:

> 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

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

--
Patrice

"Tom Holmes Jr."  a crit dans le message de news: 
uyy4Fc3wHHA.4652@TK2MSFTNGP05.phx.gbl...

> 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 

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

"Tom Holmes Jr."  wrote in message 
news:uyy4Fc3wHHA.4652@TK2MSFTNGP05.phx.gbl...

> 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 

On Wed, 11 Jul 2007 00:50:36 -0400, "Tom Holmes Jr."
 wrote:


>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

--
http://bytes.thinkersroom.com

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

"Tom Holmes Jr."  wrote in message 
news:uyy4Fc3wHHA.4652@TK2MSFTNGP05.phx.gbl...

> 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 

On Wed, 11 Jul 2007 00:50:36 -0400, "Tom Holmes Jr."  wrote:

 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)

On 12 Lug, 02:15, "Tom Holmes Jr."  wrote:


>             '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