none
Datareader.Read() IS SLOWER THAN EVER

    Question

  • Hi everybody,

    I've read all kind of forum about this issue but i can't find an answer.

    Why the hell the datareader.Read() is ssooo slow when its time to read the first row ?

    I'm on a good PC (1gig ram, 3ghz intel)
    Database is Oracle 10g


    More info :

    Its not the first time im using datareader. I use it for lot of Queries and the problem (slow) appears on some table. Yes its a big table but it should not be the reason.

    Im doing a querie that return about 120 rows.  If i execute the querie on SQLDEVELOPPER it takes about 0.02 seconds. Ok you'll say "Oh youre on oracle developper so its normally faster blablabla"


    NAH ! IF I DO THE SAME QUERY IN VB6 with old stuff (recordset, adodb stuff, etc) it takes about the same TIME !!!!! 0.02 seconds !

    Worst of ALL ! i can execute the query in VB6 and populate a listview and its faster than execution of the query in VB.NET and i did not populate datagrid !!!!!!!
     
    Yes you heard me ! The old crapy VB6 stuff is faster than VB.NET system.data stuff. Why ?

    I've try to use ODBCcommand instead of OracleCommand. Same thing ! Its slow slow slow !!!.

    How slow it is ? about 55 secondes for the first iteration of Datareader.Read() ! Its totally unacceptable for an application thats going to be use by user in there day to day job.

    Actually, to by pass this problem i'm using vb6 to do the query and im using the result in vb.net  and its faster than just VB.NET BUT i really dont like this !

    Is there someone somewhere that know why its so slow and how to fix the problem without using old crapy vb6 stuff ?

    Thank in advance

    Phil


    **I dont really hate VB6. in fact i did lot of stuff with VB6 in the past year and it was nice. But we're in year 2008 not in 1998.
         VB.NET should be able to do the same thing in about the same time.



    Thursday, June 19, 2008 6:08 PM

All replies

  • I have the same problem! But seems there is no solution out there ?
    Wednesday, March 25, 2009 8:20 AM
  • Try tuning your sql may be OR did you try indexing?
    Wednesday, March 25, 2009 11:00 AM
  • Well .. the query it self isnt a problem. Because if you use some oracle client tool like Toad. The query is lightning fast. And also if you use same SQL but setting it INLINE its also lightning fast. So the problem is DataReader + RefCursor. But seems there isnt a solution for it.
    Wednesday, March 25, 2009 11:03 AM
  • Same problem here, but using C#, we have some queries on our application eating a lot of seconds to run. Of course same queries in SQLDeveloper work fast (less than 1 second).

    We also try using another providers (oracle native seems to be faster) and all of them have the same problem. Some posts on another forums reveal that some people have similar bad performance with datareader's but without real solution.
    Monday, May 18, 2009 4:10 PM
  • Philipe,

    I can not find this slow

     Dim x As New List(Of String)
            Dim st As New Stopwatch
            st.Start()
            For i = 1 To 10000
                Using connection As New SqlConnection("Data Source=YourServer;Initial Catalog=NorthWind;Integrated Security=True")
                    Dim command As New SqlCommand("SELECT * FROM Categories", connection)
                    connection.Open()
                    Dim reader As SqlDataReader = command.ExecuteReader()
                    While reader.Read()
                        x.Add(String.Format("{0}", reader(0)))
                    End While
                    reader.Close()
                End Using
            Next
            MessageBox.Show(String.Format("{0},   {1}", x.Count, st.Elapsed.Milliseconds))
    

    This goes in less than a second

    Cor
    Monday, May 18, 2009 5:36 PM
  • Well atleast for me , Its not the System.Data.SqlClient thats the problem its the System.Data.OracleClient. So your test only tests the Microsoft SQL Server and not the Oracle

    Regards
    Patrik
    Monday, May 18, 2009 9:26 PM
  • Do you have some code to post? I use Oracle but I can't say I've found reading through a DataTable or DataReader to be slow.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, May 18, 2009 10:35 PM
  • Same problem here!!! Any have some solution please!!!
    Monday, February 08, 2010 2:48 PM
  • Same problem here!!! Any have some solution please!!!

    Am going back to you first post...

    -------Cud be your Database
    -------Cud be your code as well
    -------Cud be PC problem

    Can you post the code here please...Thanks
    Just Be Humble Malange!
    Monday, February 08, 2010 4:01 PM
  • Same problem here!!! Any have some solution please!!!

    Am going back to you first post...

    -------Cud be your Database
    -------Cud be your code as well
    -------Cud be PC problem

    Can you post the code here please...Thanks
    Just Be Humble Malange!
    Hi, I have found this discussion, because I had the same problem. The solution in my case was, that I rebuilt the index, which was used in the query. It is strange, because there should'n be any difference depending on client, but there IS.
    Wednesday, November 03, 2010 3:16 PM
  • Hi,

    We have tested this solution and we agree that it works. By adding an index in Oracle , the problem is solved and the stored procedure is really lightning fast!

    Wednesday, June 15, 2011 1:35 PM