Atlanta Custom Software Development 

 
   Search        Code/Page
 

User Login
Email

Password

 

Forgot the Password?
Services
» Web Development
» Maintenance
» Data Integration/BI
» Information Management
Programming
  Database
Automation
OS/Networking
Graphics
Links
Tools
» Regular Expr Tester
» Free Tools

Create explicit Field objects when looping on large Recordsets
[ All Languages » VB »  ADO]

Total Hit ( 1639)

Rate this article:     Poor     Excellent 

 Submit Your Question/Comment about this article

Rating


 


Whenever you access a database field through the Recordset object you incur in a slight overhead. Take for example the following code snippet:

Click here to copy the following block
Dim rs As New ADODB.Recordset
rs.Open "SELECT au_lname, au_fname FROM authors", "DSN=pubs", , , adCmdText

Do Until rs.EOF
  List1.AddItem rs("au_lname") & ", " & rs("au_fname")
  rs.MoveNext
Loop
rs.Close

The references to the table's fields inside the Do...Loop block is actually carried out by ADO as follows:

Click here to copy the following block
List1.AddItem = rs.Fields("au_lname") & ", " & rs.Fields("au_fname")

and the overhead comes from the fact that ADO must locate the referenced fields in the Fields collection. You can make your code up to 3-4 times faster by explicitly creating Field objects before running the loop, as follows:

Click here to copy the following block
Dim rs As New ADODB.Recordset
Dim fldFName As ADODB.Field, fldLName As ADODB.Field

rs.Open "SELECT au_lname, au_fname FROM authors", "DSN=pubs", , , adCmdText

' create the two Field objects that reference those two fields
Set fldLName = rs.Fields("au_lname")
Set fldFName = rs.Fields("au_fname")

Do Until rs.EOF
  ' here you use the Field object's default Value property
  List1.AddItem fldLName & ", " & fldFName
  rs.MoveNext
Loop
rs.Close

However, bear in mind that looping on all the records in a Recordset rarely is your fastest option. For example, in this particular case you can populate the ListBox control faster using the following approach:

Click here to copy the following block
Dim rs As New ADODB.Recordset
Dim varArray() As Variant
Dim i As Long

' let SQL Server concatenate the strings
rs.Open "SELECT au_lname+', '+au_fname FROM authors", "DSN=pubs", , , adCmdText
' get all the strings in one operation
varArray() = rs.GetRows()
' fill the ListBox control
For i = 0 To UBound(varArray, 2)
  List1.AddItem varArray(0, i)
Next


Submitted By : Nayan Patel  (Member Since : 5/26/2004 12:23:06 PM)

Job Description : He is the moderator of this site and currently working as an independent consultant. He works with VB.net/ASP.net, SQL Server and other MS technologies. He is MCSD.net, MCDBA and MCSE. In his free time he likes to watch funny movies and doing oil painting.
View all (893) submissions by this author  (Birth Date : 7/14/1981 )


Home   |  Comment   |  Contact Us   |  Privacy Policy   |  Terms & Conditions   |  BlogsZappySys

© 2008 BinaryWorld LLC. All rights reserved.