Results 1 to 8 of 8

Thread: 2 ways of reading all records. Which one is best and why?

  1. #1

    Thread Starter
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,593

    Question 2 ways of reading all records. Which one is best and why?

    Hello, I have come up with this 2 methods of reading all records on a database table. Both work nice but I would like your expert opinion on which delivers best performance and why.

    We are reading table "DATA" and filling an array called lstData with the contents of the "Data" field.

    1) Method one is using the bindingsourse to go through all the records.

    Code:
    Me.DATATableAdapter.Connection.ConnectionString = strCmdString
    Me.DATATableAdapter.Fill(Me.Data_DatabaseDataSet.DATA)
    
    If DATABindingSource.Count > 0 Then
           For intCounter = 0 To DATABindingSource.Count - 1
                  lstData.Add(DATABindingSource.Item(intCounter)("Data"))
           Next
    End If
    2) Second methd is using a SQL statement in a connection string and a reader to do the same thing. I am not including the declaration of variables. Note: the first field in the table (field 0) is the "Data" field

    Code:
     conn = New SqlConnection(strCmdString)
     strSql = "select * from DATA"
     cmd = New SqlCommand(strSql, conn)
     conn.Open()
     rdr = cmd.ExecuteReader
    
          While rdr.Read
              strTemp = rdr.GetValue(0).ToString
              lstData.Add(strTemp)
          End While
    
     conn.Close()
    My guess is that the first method, while simpler to the eye could use more overhead. What I really would like to know is if the second method really delivers better performance and if it is worth doing all the manual SQL queries.

    thanks in advance.
    Last edited by kaliman79912; Oct 17th, 2010 at 12:38 PM.

  2. #2
    New Member GinGin's Avatar
    Join Date
    Oct 2010
    Posts
    13

    Re: 2 ways of reading all records. Which one is best and why?

    Why don't you just test it? Start a stopwatch, do the query in first way, stop the watch and check the elapsed time. Same thing for the second one. Whichever was faster, is the more efficient one, right? What comes to being worth the queries or not is up to you, depending on what you're doing and for who.

  3. #3
    Fanatic Member
    Join Date
    Aug 2010
    Posts
    624

    Re: 2 ways of reading all records. Which one is best and why?

    The ability to test with a stopwatch would really depend on the amount of records to get a time that makes sense. if you're pulling like 20 records I doubt the difference would even be noticeable (if there is a noticeable difference between the methods at all), but with 50,000 records you might start getting differences.
    If I helped you out, please take the time to rate me

  4. #4

    Thread Starter
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,593

    Re: 2 ways of reading all records. Which one is best and why?

    I appreciate your sugestions but I would like someone with insight to tell me why would one of these be better in a technical way.

    I am testing it with about 1200 records and both perform almost instantly. But the target is a network system that I can't test here. I will whenl I install it at the client's network. At the end it would not matter because the performance is really similar, but never the less, I would like to know which one is best.

  5. #5
    New Member GinGin's Avatar
    Join Date
    Oct 2010
    Posts
    13

    Re: 2 ways of reading all records. Which one is best and why?

    Of course 1200 records come instantly, try it with 120000 and I'm sure you'll get results.

  6. #6

    Thread Starter
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,593

    Re: 2 ways of reading all records. Which one is best and why?

    Thank you GinGin, but at the long run im not only interested in which one is fastest, because my guess is that the bindingsource will load a whole bunch of records and it may be faster, but would it fill up memory? Performance should not only be considered the speed.

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: 2 ways of reading all records. Which one is best and why?

    The datareader should be faster than the other solution. The datareader is forward only, read only, which was added because it is a faster means of iterating through data if you are content with those limitations. The dataadapter is slower for a single pass, but allows you to navigate forwards and backwards, read randomly, edit, and update. Therefore: Datareader for speed, datatable for versatility.

    By the way, you should also see some minor increase in speed if you write out the fields rather than using SELECT *. I have never tested this, but I have read it before. Even if you are returning every single field in the table, there is supposed to be some performance advantage to writing out the fields rather than using *.

    Also, the question about performance testing is a nice subject. I have a test app where I can add in the two versions I am testing, then run them in a loop using a stopwatch to time them. Each test is in its own subroutine, and I run them in the order: Test1, Test2, Test2, Test1, such that there is no order effect. It's a nice little testbed to use. In this case, while there might not be a huge difference, you WILL see the difference. I use this testapp to look for differences between + and *, or Long + Long vs. Integer + Integer, so a difference like the one you are talking about would be easy to see.

    On the other hand, your particular test would be sufficiently difficult to replicate that I haven't bothered just for this post, but if you are interested in seeing what the magnitude of the difference would be, I could try such a test against one of my database tables. In the end, if the Datareader isn't significantly faster than the dataadapter, then MS has been lying to us.
    My usual boring signature: Nothing

  8. #8

    Thread Starter
    PowerPoster kaliman79912's Avatar
    Join Date
    Jan 2009
    Location
    Ciudad Juarez, Chihuahua. Mexico
    Posts
    2,593

    Re: 2 ways of reading all records. Which one is best and why?

    Thank you Hiker, this is more of the kind of answer I was looking for. As for the "SELECT *" thing I agree, the code I posted is actually a summary of what I really have on my application, there was no point in posting here the whole enchilada, my tables are not really named "DATA" you know.
    Anyway, thanks a lot.
    More important than the will to succeed, is the will to prepare for success.

    Please rate the posts, your comments are the fuel to keep helping people

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width