locked
Datareader is very slow RRS feed

  • Question

  • User1038109032 posted

     Hello all,

    I'm using the datareader on an access database, and its very slow.

    The query runs in access in about 1/2 second, when run with a datareader it takes 25 seconds.

    Code below

            Using Connection As New OleDbConnection(ConnectionSettings.ConnectionString)
    
                Using Command As New OleDbCommand(String.Format("SELECT * FROM [Customers With Outstanding Orders] " & _
                                                "WHERE [OnHold] = {0}", OnHold), Connection)
                    Try
                        Connection.Open()
                        StopW.Reset()
                        StopW.Start()
                        'This bit is very slow
                        Using reader As OleDbDataReader = Command.ExecuteReader
                            StopW.Stop()
    

    Any tips as to what I should be looking for.

    Friday, April 9, 2010 5:59 AM

All replies

  • User2039959148 posted

    u just use oledbdataadapter object and fill the datatable

    and then apply looping on datatable , it is also same as datareder

    and fast


    Friday, April 9, 2010 7:47 AM
  • User-1823825456 posted

    simply use datatable or dataset with dataadapter


    Friday, April 9, 2010 7:59 AM
  • User1038109032 posted

    But will that make it any quicker?

     

    Why is the datareader so slow, should I not use a datareader?

    Friday, April 9, 2010 8:10 AM
  • User1038109032 posted

    Double post

    Friday, April 9, 2010 8:11 AM
  • User-1823825456 posted

    But will that make it any quicker?

     

    Why is the datareader so slow, should I not use a datareader?


    datareader is not advisable to use cause it requires active connection to get the values always. it means whenever u use datareader the connection to the DB is mainatined.

     but in case of datadapter is not like that. it is in offline mode. it doesnt require any active connection and hence it will be faster.


    Friday, April 9, 2010 8:46 AM
  • User1038109032 posted

    I've switched to a DataAdaptor, No speed improvement.

    The query runs in 1/2 second in access and over 25 seconds when connecting with datareader or adaptor.

    My new code

            Dim cmd As String = String.Format("SELECT * FROM [Customers With Outstanding Orders] " & _
                                                "WHERE [OnHold] = {0}", OnHold)
            Dim adapter As New OleDbDataAdapter(cmd, ConnectionSettings.ConnectionString)
            Dim topics As New DataSet
            StopW.Reset()
            StopW.Start()
            'This bit is very slow
            '**********************************
            adapter.Fill(topics, "Customers")
            '**********************************
            StopW.Stop()
    
            Dim Customer As DataRow
            For Each Customer In topics.Tables("Customers").Rows
    


     

    Friday, April 9, 2010 11:35 AM
  • User1038109032 posted

    I've had another look at this after a couple of weeks, and now its working nice and quick, under 1/2 a second.

    I don't know what has fixed it, it was n't me.

    I have compacted and repaired the database in that time, so that might be it and worth trying for anyone with similar problems.

    Tuesday, April 27, 2010 6:23 AM