?
Solved

vb search

Posted on 2006-05-23
12
Medium Priority
?
165 Views
Last Modified: 2010-04-07
I have a performance question.

I want to see if a given item exists in a list.

search for Item #2

Item #1
Item #2
Item #3
Item #4

I am currently creating a semi colon delimited list

myList="Item #1;Item #2;Item #3;Item #4"

and then doing an instr so see if it exists

If InStr(myList, searchItem& ";") Then
  'I found it
end if


Is this a good way of accomplishing this?
Would it be better to use an array or something?

Thanks

0
Comment
Question by:jackjohnson44
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16742208
As long as the list is fairly short then you should be ok. But if you list is going to grow larger then you should probably consider switching to an array, or even using a dictionary.
0
 

Author Comment

by:jackjohnson44
ID: 16742245
how would I accomplish this using an array or dictionary?
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 16742314
Using an array would be a simple loop:

    Dim arrList(3)
    arrList(0) = "Item #1"
    arrList(1) = "Item #2"
    arrList(2) = "Item #3"
    arrList(3) = "Item #4"  

    Dim sSearchFor As String
    sSearchFor = "Item #2"

    Dim bFound As Boolean
    Dim i As Integer

    bFound = False    '// Default
    For i = 0 To UBound(arrList)
        If arrList(i) = sSearchFor Then
            '// Found the item, so exit loop
            bFound = True
            Exit For
        End If
    Next i


An example of using the Dictionary can be found here:

    http://www.kamath.com/tutorials/tut009_dictionary.asp
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 16742325
Here is another Dictionary example...

Option Explicit

' Click on Project --> References and Check the "Microsoft Scripting Runtime" Entry.

Private dct As New Dictionary

Private Sub Form_Load()
    dct.Add "Item #1", "a"
    dct.Add "Item #2", "b"
    dct.Add "Item #3", "c"
    dct.Add "Item #4", "d"
End Sub

Private Sub Command1_Click()
    Dim key As String
    Dim value As Variant
   
    key = "Item #3"
    If dct.Exists(key) Then
        value = dct(key)
        MsgBox value, vbInformation, key
    Else
        MsgBox key, vbExclamation, "Key Not Found"
    End If
   
    key = "Item #5"
    If dct.Exists(key) Then
        value = dct(key)
        MsgBox value, vbInformation, key
    Else
        MsgBox key, vbExclamation, "Key Not Found"
    End If
End Sub
0
 
LVL 1

Expert Comment

by:Brownhead
ID: 16747440
Here is the code you'll need.

'//Code\\
'This code requires nothing
Private Function IsPresent(ByVal List As String, ByVal Item As String) As Boolean
Dim aHold() As String, iCount As Integer
aHold = Split(List, ";")
For iCount = LBound(aHold) To UBound(aHold)
    If (aHold(iCount) = Item) Then IsPresent = True: Exit Function
Next iCount
End Function
'\\Code//

Arguments:
List :: The list, each item should be seperated by a SemiColon (;)
Item :: The item that you are checking for
0
 
LVL 19

Assisted Solution

by:BrianGEFF719
BrianGEFF719 earned 800 total points
ID: 16747906
Actually, your instr method is going to be faster than an array, considering some of these methods, (such as brownheads) require re-splitting the array EVERYTIME you want to check if the item is present, the overhead associated with this will be huge if you have a large list.

Stick with your method, it will be the fastest.

Good Luck
Brian
0
 
LVL 86

Expert Comment

by:Mike Tomlinson
ID: 16748199
For LARGE lists, the Collection or Dictionary approach will be the fastest...especially if you have to add/remove items in the list.
0
 
LVL 1

Assisted Solution

by:Brownhead
Brownhead earned 400 total points
ID: 16748317
Actually... for large lists the InStr (What Jack originally though of using) is faster.. and is combatible with almost all versions of visual basic, and on all computers that support VB. I'd definetly go with the InStr.
0
 
LVL 86

Accepted Solution

by:
Mike Tomlinson earned 400 total points
ID: 16748363
But the Instr() approach can't handle the caveat to my statement:

    "...especially if you have to add/remove items in the list."

VB is notoriously slow at dealing with Strings.

If you wanted to remove an item from the middle of the list you would have to use very SLOW string manipulation functions to "chop" that piece out.  If you are dealing with a large list this would mean making copies of that string in memory to move the pieces around.  When adding items you again have to append one string to another which is also very slow when dealing with large lists.

With a Collection or Dictionary, removing the item would be very fast since you can jump straight to it based on the key and then you just need to update the linked list pointer of the previous item to skip over the item being removed.  Adding items would be much faster as well.

You have to pick the right tools for the job...  =)

Again, it really depends on the size of the list and how you intend to interact with it.
0
 
LVL 19

Assisted Solution

by:BrianGEFF719
BrianGEFF719 earned 800 total points
ID: 16748388
>>If you wanted to remove an item from the middle of the list you would have to use very SLOW string manipulation >>functions to "chop" that piece out.  If you are dealing with a large list this would mean making copies of that string >>in memory to move the pieces around.  When adding items you again have to append one string to another which >>is also very slow when dealing with large lists.


Idle_Mind: I agree completely, however, my assumption has been that the list is static during the execution of the program. In which case InStr will be faster.

If your assumption is correct and the list needs to be modified during execution, then your collection will more than likely be faster than copying large strings over and over again.

Perhaps its time for the questioneer to contribute feed back on how this list will be handled during execution.


Brian
0
 
LVL 17

Assisted Solution

by:inthedark
inthedark earned 400 total points
ID: 16749566
Agreeing with much of the above I have found that Instr approach on really large strings is slow.  I suspect that this is because MS may use By Val instead of ByRef to some of the string functions. By my point is this:

A simple but fast method would be to use a disconnected adodb.recordset.  The nice thing is that you can load the data real fast either with a load statement or by using an SQL Select, then disconnecting the data base from the database. (A good place to keep data is in a database.)

The ADODB.Recordset is able to sort, filter, find records very quickly.  The ADODB.recordset is just an array with goodies so it builds and destroys real fast.  Also you can delete, addnew records and even save the whole lot in one hit. There is only one thing faster, a sorted array which can be binary chopped for faster access. It would be faster than ADODB.recordset  but not so you would notice.

Collections need obejcts and so are slow to build and destroy.  Dictionary object is excellent for small data but cannot be loaded in one hit.

So it really depends on the application's data growth potential to define which way to go.

0
 
LVL 17

Expert Comment

by:inthedark
ID: 16749584
Also forgot you can manually load recordset but if you need to do this use the dictionary object. Here is example:

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20990817.html?query=disconnected+recordset+ford&clearTAFilter=true
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month17 days, 8 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question