Share via

Datareader.Read() IS SLOWER THAN EVER

  • Article

Question

Thursday, June 19, 2008 6:08 PM

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.

All replies (11)

Wednesday, March 25, 2009 8:20 AM

I have the same problem! But seems there is no solution out there ?

Wednesday, March 25, 2009 11:00 AM

Try tuning your sql may be OR did you try indexing?

Wednesday, March 25, 2009 11:03 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.

Monday, May 18, 2009 4:10 PM

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 5:36 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 9:26 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 10:35 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, February 8, 2010 2:48 PM

Same problem here!!! Any have some solution please!!!

Monday, February 8, 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...ThanksJust Be Humble Malange!

Wednesday, November 3, 2010 3:16 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, June 15, 2011 1:35 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!