-
Oct 17th, 2010, 11:22 AM
#1
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.
-
Oct 17th, 2010, 11:41 AM
#2
New Member
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.
-
Oct 17th, 2010, 11:45 AM
#3
Fanatic Member
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
-
Oct 17th, 2010, 11:58 AM
#4
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.
-
Oct 17th, 2010, 12:00 PM
#5
New Member
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.
-
Oct 17th, 2010, 12:41 PM
#6
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.
-
Oct 17th, 2010, 12:52 PM
#7
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
-
Oct 17th, 2010, 01:58 PM
#8
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|