Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Visual Basic
Expertise: Intermediate
Oct 9, 1998

Faster Searches on Arrays, List Boxes, and Combo Boxes

This method is handy when the user enters data that you need to validate against a lookup table. Validating the value by querying the database is out of the question because of the relatively slow response time. An alternative is to keep the lookup table locally in a sorted array, or in a list-box or a combo-box control whose Sorted property is set to True. Lookup tables are static, so you don't need to worry about the local copy being out of sync with the database. For large sorted arrays of strings or for list-box or combo-box controls with large sorted lists (in the range of 10,000 entries), you will find the binary search to be 10 to 20 times faster than calling the API, and hundreds of times faster than a sequential search. The difference becomes dramatic if the operation needs to be done multiple times. If you want to use an array instead of a combo-box or a list-box control, there is no API call you can use. Use this code for an array:

 'In:
'Array to search
Dim rasArray() As String
'String to search for
Dim vsName As String	
'Out:
'Index in the array of the string if found
Dim rlIndex As Long
'Local variables:
'Index in the array
Dim lnIdx As Long
'Lower bound of the search interval
Dim lnMin As Long
'Upper bound of the search interval
Dim lnMax As Long
'Return an invalid index, if string is not found 
rlIndex = LBound(rasArray) - 1
lnMax = UBound(rasArray)
lnMin = LBound(rasArray)
'lookup vsName in rasArray()
Do While lnMin <= lnMax
	lnIdx = (lnMax + lnMin) \ 2
	If vsName = rasArray(lnIdx) Then
		rlIndex = lnIdx
		Exit Do
	ElseIf vsName < rasArray(lnIdx) Then
		lnMax = lnIdx - 1
	Else
		lnMin = lnIdx + 1
	End If
Loop

You can easily modify this code for a combo-box control to use with list-box controls as well:

 'In:
'Combo to search
' (change into As ListBox for listbox controls
' or use As Controls to use with both types 
' of controls)
Dim rcboCombo As ComboBox
'String to search for
Dim vsName As String
'Out:
'Index in the combo if the string is found
Dim rlIndex As Long
'Local variables
'Index in the array
Dim lnIdx As Long
'Lower bound of the search interval
Dim lnMin As Long
'Upper bound of the search interval
Dim lnMax As Long
'Return an invalid index, if string is not found
rlIndex = -1  
lnMin = 0
lnMax = rcboCombo.ListCount - 1
lnIdx = lnMax \ 2
'lookup name in the combo
Do While rlIndex = -1 And lnMin <= lnMax
	If vsName = rcboCombo.List(lnIdx) Then
		rlIndex = lnIdx
	ElseIf vsName < rcboCombo.List(lnIdx) Then
		lnMax = lnIdx - 1
		lnIdx = (lnMax + lnMin) \ 2
	Else
		lnMin = lnIdx + 1
		lnIdx = (lnMax + lnMin) \ 2
	End If
Loop
Adrian Cerchia
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date