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.

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.

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.

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.

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

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.

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.

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.