[RESOLVED] How To Improve Array Updating Speed?

[webbiz]

I have an array of Data().

Items are Data().Date, Data().High, Data().Low, Data().Type.

For my test, there are 8854 array elements.

The following snippet of code simply compares each data element with the previous one.

It then assigns a 'type' (string) value to that element.

    For i = Days To UBound(Data)
    
        If Data(i).High > Data(i - 1).High And Data(i).Low < Data(i - 1).Low Then
            Data(i).Type = "OB"
        ElseIf Data(i).High > Data(i - 1).High Then
            Data(i).Type = "HH"
        ElseIf Data(i).Low < Data(i - 1).Low Then
            Data(i).Type = "LL"
        Else
            Data(i).Type = "IB"
        End If
        
        Debug.Print Data(i).Date + " - " + Data(i).Type
        
    Next i

Following are the last few printed results including how many seconds it took to do this. Nearly 6 seconds! That's a long time.

01/06/2023 - OB
01/09/2023 - HH
01/10/2023 - LL
01/11/2023 - IB
01/12/2023 - OB
01/13/2023 - HH
01/17/2023 - HH
01/18/2023 - HH
01/19/2023 - LL
01/20/2023 - HH
01/23/2023 - HH
01/24/2023 - HH
01/25/2023 - HH
01/26/2023 - HH
01/27/2023 - IB
01/30/2023 - LL
01/31/2023 - LL
02/01/2023 - HH
02/02/2023 - HH
02/03/2023 - LL
02/06/2023 - LL
 - LL
 5.8269999999975

Clearly this must be an inefficient way to accomplish this task.

What would be a better approach?

TIA

[vb6forever]

webbiz:
Not sure what your trying to accomplish.
Per thread title "Array Updating"
You're not updating anything with that code.

[OptionBase1]

How fast does it run compiled with the "Remove Array Bounds Checks" advanced compiler optimization box checked?

[dilettante]

Have you tried running a code profiler against this? That could tell you things like the cost of various steps and which paths get hit most frequently. From there you might rearrange your tests to do the frequent ones first, cache intermediate values for faster repeated access in the loop, etc.

[webbiz]

webbiz:
Not sure what your trying to accomplish.
Per thread title "Array Updating"
You're not updating anything with that code.

Huh?

You sure?

That code is updating the ".type" item for each array element.

[webbiz]

Have you tried running a code profiler against this? That could tell you things like the cost of various steps and which paths get hit most frequently. From there you might rearrange your tests to do the frequent ones first, cache intermediate values for faster repeated access in the loop, etc.

@dilenttante You're speaking Greek to me again.

@OptionBase1 This code is part of a larger program written years ago that I recently discovered is missing code. Somehow I didn't back up the original and so now I cannot compile this app until I repair or modify the missing gaffs. During the process I'm changing up a few things and while adding this piece of code I noticed it takes 6 seconds to complete. So I'm at a point where if there is a faster way.

Meanwhile, I'm adding a database to this old app and will test to see if using SQL will make this process faster.

:-)

[Niya]

I managed to coax an almost 3 fold increase in performance by performing the greater than and less than performances only once. The results of each I used to set the first and second bits of a bit field and I stored the value of the entire bit field in the UDT instead of a String. I then wrote a function to convert the value of the bit field into one of the four String, OB, HH, LL, IB.

Unfortunately the IDE crashed during testing and I lost all the code and I'm too lazy to rewrite all that again. Unlike modern editions of Visual Studio, you can't recover lost code after a crash

EDIT:

Here's what remains of the code I was testing from an open browser tab where I was already writing a response:-

    Dim data() As MyUDT
    Dim flags As Long
    Dim t(0 To 3) As String
    
    t(1) = "HH"
    t(2) = "LL"
    t(3) = "OB"
    t(0) = "IB"
    
    data = GenerateSampleData()
    
    For i = LBound(data) + 1 To UBound(data)
        flags = 0
        
        If data(i).High > data(i - 1).High Then flags = flags Or 1
        If data(i).Low < data(i - 1).Low Then flags = flags Or 2
        
        data(i).Type = t(flags)
    Next

In the final version, I changed Type to a Long which put the value of flags there instead to avoid all the String copying. The function I mentioned earlier could later convert that value to a String if needed.

[webbiz]

I managed to coax an almost 3 fold increase in performance by performing the greater than and less than performances only once. The results of each I used to set the first and second bits of a bit field and I stored the value of the entire bit field in the UDT instead of a String. I then wrote a function to convert the value of the bit field into one of the four String, OB, HH, LL, IB.

Unfortunately the IDE crashed during testing and I lost all the code and I'm too lazy to rewrite all that again. Unlike modern editions of Visual Studio, you can't recover lost code after a crash

EDIT:

Here's what remains of the code I was testing from an open browser tab where I was already writing a response:-

    Dim data() As MyUDT
    Dim flags As Long
    Dim t(0 To 3) As String
    
    t(1) = "HH"
    t(2) = "LL"
    t(3) = "OB"
    t(0) = "IB"
    
    data = GenerateSampleData()
    
    For i = LBound(data) + 1 To UBound(data)
        flags = 0
        
        If data(i).High > data(i - 1).High Then flags = flags Or 1
        If data(i).Low < data(i - 1).Low Then flags = flags Or 2
        
        data(i).Type = t(flags)
    Next

In the final version, I changed Type to a Long which put the value of flags there instead to avoid all the String copying. The function I mentioned earlier could later convert that value to a String if needed.

You are a clever one indeed.

While still transferring a string to .Type from the t() array but using the bit math, the following is the comparison speeds.

My original code (minus the Debug.Print in the loop): 9.9999998928979E-04

So it turns out to be less than 1 second afterall.

And changing the code to use your adaptation (at a minimum, it is cleaner):

    For i = LBound(Data) + 1 To UBound(Data)
        flags = 0

        If Data(i).High > Data(i - 1).High Then flags = flags Or 1
        If Data(i).Low < Data(i - 1).Low Then flags = flags Or 2

        Data(i).Type = t(flags)


    Next i

The timer returns: 0

Sheesh! It's so fast it doesn't even register. Nice!

Thanks for tip.

And like you said, I might change the .Type (type) to a Long as I don't need it as a string. A numeric value representing those tags will be just fine.

:-)

[Niya]

Oh wow it worked that well for you? Nice. I knew it would give some improvement but wasn't sure it gonna work that well for you

You should double check that the String tags properly match flag combinations to make sure I got it right. I'm pretty sure I did but double check to make sure.

[webbiz]

Oh wow it worked that well for you? Nice. I knew it would give some improvement but wasn't sure it gonna work that well for you

You should double check that the String tags properly match flag combinations to make sure I got it right. I'm pretty sure I did but double check to make sure.

I did check, and manually did it to make sure the math added up. Thinking in OR speak isn't my first language. ;-)

Ironically, back in 1978 I started out with Sperry Univac in the core memory test/repair dept. I'd spend hours flipping switches from 00000000 to 00000001, then 00000010, etc. until 11111111 to test for dead memory cores. Picked it up within minutes of first exposure to the delight of my lead person.

And yet I can't even think about it as a solution for speeding up some code. LOL!

Thanks.

[jennyaa]

Split the IF statements. VB doesn't give you an option for complete boolean evaluation. So, when the first statement is false the second one will still be evaluated, even if your result is useless.

Private Sub Command1_Click()
' This code snippet will run slowly
If function1 And function2 Then
Debug.Print "It is True"
End If

' Executed faster because function2 is not executed when function1 is false
If function1 Then
If function2 Then
Debug.Print "It is True"
End If
End If
End Sub

Also, write the function that is expected to run faster at the top.

[Niya]

And yet I can't even think about it as a solution for speeding up some code. LOL!

Well to be fair, no one else did either. The only reason I did is because this thread is fundamentally about performance and when I hear that my natural instinct to start thinking about what the machine code might be doing. I felt your original code might be producing too much conditional branching because of the redundant comparisons. Also, bitwise operations are known to be among the fastest operations a processor can perform. So I starting thinking about how I could reduce all the branching and given that combinations were involved, I figured there must be a way to utilize bitwise operations on a bit field.

[webbiz]

Split the IF statements. VB doesn't give you an option for complete boolean evaluation. So, when the first statement is false the second one will still be evaluated, even if your result is useless.

Private Sub Command1_Click()
' This code snippet will run slowly
If function1 And function2 Then
Debug.Print "It is True"
End If

' Executed faster because function2 is not executed when function1 is false
If function1 Then
If function2 Then
Debug.Print "It is True"
End If
End If
End Sub

Also, write the function that is expected to run faster at the top.

Good points.

In my particular example, the second would need to run regardless of the result of the first. Each test is required.

Thanks.