[RESOLVED] Optimization tricks needed - VERY LARGE Select Case statement

[Krass]

###Resume: App too long to compile because of 2000-cases long "Select Case" statements###

I have an application that has been developped a long time ago which has been serving me very well for the last few years.

The only problem is that compiling it under VB6 takes a while (something like 2-3-4 minutes - which is very long, on a fairly good machine). I don't have to recompile that often so I never took the time to fix this. Now the time has come.

The only cause is that I have a module with 5-6 "Select Case" statements. Each of those statements has something like 2000 cases. I would have made only one single select, but VB6 wouldn't handle that long.

The purpose of these statements are to return random words. I love to play with databases (that's actually where those words come from) but at first I wanted the application to have those words totally implemented, avoiding the need of any recordset or links to a database.

I have thought of one solution, and I'm posting here before implementing in case someone has a good idea/workaround for this... here it is:

Since I don't want my application (which is constantly running) to connect to an external database over and over, I thought I could simply fill a recordset with all those words on the form_load so that my application can randomly pick words in that "huge" recordset quite quickly. The recordset is filled once, and I don't think it will eat up too much ressources.

Then some people I talked to thought it might be a good idea to dig into XML. I don't know much about it, but I might gather some info if any of you guys thinks it might be a good approach. From what I could understand, an XML file would act somehow as a database, straight in a "XML-formatted" text file. Q: Since I'm querying that 'word collection' ***VERY*** often, would that bring any drawbacks? ie speed.

My main goal is to keep the blazing fast results I'm getting right now by using those words STRAIGHT into the compiled vb app.

Thanks for any idea/thoughts on this!

[DigiRev]

Exactly how many random words are you storing? 5-6 select case statements with about 2,000 cases would be roughly 10 to 12,000 words. How long did it take for you to type all of those?

That's really not too much to store straight in memory though. At least not with computers nowadays.

You could store them in a sorted array. Then create an "array map" so you can pick words from it quickly.

[Krass]

Hi there.

Your math is good, that roughly gives 10-12k words. Rest assured, I manually typed NONE of those words It originally came from some databases I found.

I wouldn't even need to do any sorting since I only need random results from it (I don't need to find one given word, nor a word beginning with "a" or based on length). Without remembering the exact syntax, it would be something like:

rstRandomWord.GoToRecord int(rnd*12000)
msgbox rstRandomWord!strRandomWord

But that's using a recordset. I'm not quite sure what you meant about "array MAPS", bout you brought up an interesting point: would it be better (in my case, based on speed when querying a word) to use a recordset or a simple array?

Anyone with an answer to that?

Thanks

[DigiRev]

Databases can be pretty fast. But it doesn't really get any faster than just storing and retrieving directly from your program's memory like an array.

[randem]

Let's see this "Select Case" Statement. I am sure it can be whittled down to a few lines...

[Krass]

Here's what it looked like:

Public Function fctRandomWord1()
    Randomize
    Select Case Int(Rnd * 2000)
    Case 0
        fctRandomWord1 = "Word1"
    Case 1
        fctRandomWord1 = "Word2"
    Case 2
        fctRandomWord1 = "Word3"
    Case 3
        fctRandomWord1 = "Word4"
    Case 4
        fctRandomWord1 = "Word5"

...That would have reached "Case 1999"

I'm done with filling an array instead of using those huge select statements. I think it'll work great. Actually I was 30 seconds from my first compile when I saw your post.

But still, I'm curious to know how you would have narrowed that huge pile of code... Except for the fact that it could have been done:
Case 0: fctRandomWord1 = "Word1"
Case 1: fctRandomWord1 = "Word2"

Thanks.

[DigiRev]

If you have all of them in an array (say, strWords()), then you can condense all the code down to something like this:

Option Explicit
 
'Your array of words.
Private strWords() As String
 
Private Function RandomNumber(ByVal Minimum As Long, _
    ByVal Maximum As Long) As Long
    RandomNumber = Int((Maximum - Minimum) * Rnd + Minimum)
End Function
 
Public Function fctRandomWord1() As String
    fctRandomWord1 = strWords(RandomNumber(0, UBound(strWords)))
End Function
 
Private Sub Form_Load()
    Randomize 'Only needs to be called once.
    'Put words into strWords() array.
End Sub

[opus]

If you have all the words in an array (like arrWords()), you could use something like this:

Public Function fctRandomWord1()
        fctRandomWord1 = arrWords(Int(Rnd * 2000))
 End Function

As I see it you don't need a Select Case (if you have the words in an array). Also I wouldn't use Randomize in that Function, us it only once for the whole application!

[randem]

And your array would look something like:

Const Words = "word1,word2,word3,word4,word5,..."
Dim WordArr() as string
Dim WordCount as long
 
WordArr = Split(Words,",")
WordCount = Ubound(WordArr)+1

Or you could read the words from a file (better method if you need to add/change/remove words)

The you use a function like opus suggest to get the words you need.

[Krass]

Thanks for the help guys. I got that thing running smoothly now, and the compiling time is back to normal.

Of course those huge select statements are now eliminated, and I get my randomword by using:
strWord = arrWord(Int(Rnd * arrWordCount))

On the form load, a recordset gets filled. I keep the RecordCount info in a variable and then loop the recordset to fill my array. Works perfectly!

Have a nice day...