Array optimization - String or Integer?

My app is currently running pretty good, but I'm trying to figure out if my current setup can be furthermore optimized.

I have a database table containing only 2 columns, an X and an Y coordinate. At the beginning of my application, I use a recordset to transfer all those coordinates in an array. I didn't feel like simply using the recordset because I felt that dealing with a matrix/array would make things MUCH quicker. (Ain't I right?) I'll be browsing that huge array back and forth many times.

That database/array might end up containing a million records. YES it'll take a while at startup to fill the array - but that's not a big problem for me.. The app will afterward work with it for 2-3 days until I ask it to stop ( or unless I figure out I made a programming mistake and the memory-ressources gets eaten up too quick :o )

(please feel free to comment any of these techniques - I'm open to all good suggestions)

Another concern would be the array itself: it is declared as a STRING array. Now, if we nevermind the "Why use string if it only contains X,Y integer data???" - Will using a string array make things slower than using an integer array? I've been suggested this and I'd like to have some clarifications. Maybe does it only apply to string variables and such?

Thank you guys for the feedback.

Quote:

---

Will using a string array make things slower than using an integer array?

---

You bet it will!

The conversion from string to integer every time you make use of a value will take forever. If they aren't already integers in the database, convert the columns to a numeric data type and save tons of time. At the very least convert them when you read them in.

As you are using the data repeatedly, using an array (rather than a recordset) is a good idea - it should be much quicker.

As to it containing a million records, you may find that it uses up all of your spare physical memory, at which point your computer will become dramatically slower. Whether this is an issue or not depends on the size of the data, and the physical memory you have spare at any point when the program runs.


As to the data type of the array.. think what you would use if you had lots of separate variables instead, as an array is basically a group of variables.

To hold numeric data, Strings will use more space than the proper data types (any Integer takes 2 bytes, but a string takes 10 bytes plus the length of the string - so "99" will take 12 bytes [10 extra], and "32000" will take 15 [13 extra]). Assuming a million rows of just two values, that would be between 20MB and 30MB as string data, but only 4MB as numeric data.

Strings in VB are generally very slow, and if you are performing any type of mathematical calculations on them, this will be even worse - as they will get automatically converted to numbers (possibly Integers) before the calculation, and the final result converted back to a string.


You should always try to use the correct data type for the data, as it not only removes the extra conversions, but also dramatically reduces the chances of conversion/concatenation errors too.

si_the_geek, bogie: GREAT FEEDBACK... thank you!

My database is already in INTEGER type. The reason why I used a string array is that this array contains information from many sources - it's like having 500 arrays into one. So at the end of each "sub-array", I have some string-info in the array, something like "END-OF-ARRAY;INFO-BLABLA" to know where I'm at, in the array.

I guess I'll reconfigure how it works to avoid having that huge array as a string array - I guess I could manage to do that easily.

My machine has 3GB of physical memory. I'm planning on having a standalone machine(s) to deal with this - and I'll add all the required memory if needed.

si_the_beek, thanks for the brief but very informative tips about byte-usage of those variables.

:thumb:

Another question comes to mind:

To make sure my app doesn't crash, I have some checkups here and there on the "UBound(matMyMatrix, 1)" to avoid going further.

Should I save the UBound result in a variable and use this for the checkup instead? Or will UBound offer me an as blazing-fast result?

Rather than having the extra string info in the array, I would add a third 'column' to the array which points to another (string) array which contains that data. You would then be able to know from any row of the original data which 'sub-array' the data comes from.


As for storing the result of UBound to a variable, that could be a good idea (especially if you are calling it within a loop), as calling functions (even built in ones) takes time, as does actually running them. In this particular case the difference isn't huge, but if you also make a similar sort of change for other function calls you will get a noticeable gain.

If you are resizing the array at any point (which is very slow, especially with Preserve), using this variable allows you to use a much faster method.


I would recommend reading the article How do I optimize my code? from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page), as it contains several useful tips.

si_the_geek strikes again.

Good stuff.. I was already thinking about that 3rd row idea. But I think I will store info in that 3rd row only at the beginning of a "sub-array" since I don't need to check it up at any row. When I reach the beginning of a sub-array, I will simply keep its info in another variable and loop until the 3rd columns contains something again. I guess that'll lighten my array, too.

I do have to redim the array with PRESERVE.. But I'll put it on a 5-10 minutes timer so it won't be growing THAT much. Or anyway not that often.

I'll make sure I read that optimize-your-code thread. I think I print it once to help me pass time in the plane, but I didn't read it and prolly lost it since.

Thank again for the tips.

Long is the fastest data type to process since it is native to the processor.

Rule of thumb:
When in doubt use integer, long if necessary (>32768). Integer processing will always outrun strings and single precision.

I recall writing code once upon a time that converted single precision to integers by removing the decimal, performing the integer calculations, tracking the decimal point movement, and then displaying the results as a string by repositioning the decimal after the integer computations finished.

That "idiotic" procedure was faster than letting the computer perform single-precision manipulations with the same values.:eek:

I might have to benchmark both solutions. If randem is right, it sounds like I should go LONG. I will NEVER deal with decimals.

If your values will never exceed the range of Integer, -32768 to 32767, then stick with the Integer data type. They are half the size of Long, which could be significant given the size of your array, and you will never notice the difference in speed.

randem's generalized point about Long being more efficient is correct, but Logo's point about using an extra 4mb of memory is the bottom line for speed in this case.

Quote:

---

Originally Posted by Krass

My database is already in INTEGER type. The reason why I used a string array is that this array contains information from many sources - it's like having 500 arrays into one. So at the end of each "sub-array", I have some string-info in the array, something like "END-OF-ARRAY;INFO-BLABLA" to know where I'm at, in the array.

---

Sounds like it could be done better as a UDT array. A UDT array would also give you a more logical structure. Something like:

vb Code:

Private Type CoordinateType
    x As Integer
    y As Integer
End Type
 
Private Type MainType
    Info As String
    Coords() As CoordinateType
    Length As Long ' Stores the UBound() of Coords()
End Type
 
Private mtyp() As MainType
 
Public Sub InitArray()
    Dim i As Long
    Dim j As Long
 
    ReDim mtyp(500)
    For i = 0 To 500
        With mtyp(i)
            .Info = "Description goes here"
            ReDim .Coords(2000)
            .Length = 2000
            For j = 0 To 2000
                With .Coords(j)
                    .x = rst!x
                    .y = rst!y
                End With
            Next
        End With
    Next
End Sub

That's the basic idea, anyway.

Getting back to the database most large database engines use the integer type but it is really a long data type... Unless it is defined as short integer or small integer or something like that.

Besides I have never heard of a resultset being a million records and I have done some of the most busy financial systems and no where near that many records are used at one time...

That's a much better idea Ellis... I knew there was something that could be done for the organisational side of things, but didn't think of that even tho I use it regularly myself. :blush:

Quote:

---

Originally Posted by randem

Getting back to the database most large database engines use the integer type but it is really a long data type... Unless it is defined as short integer or small integer or something like that.

---

Very true, but as long as the values are always within the valid range for VB's Integer, that wont be a problem - the coercion will be fine.

I definitely agree with Logophobic's comments on the use of Integer for this - the Long data type is likely to cause more harm than good, unless it is required due to the scale of the numbers.

Quote:

---

Besides I have never heard of a resultset being a million records and I have done some of the most busy financial systems and no where near that many records are used at one time...

---

That's a fair point.. this amount of data is rather extreme (but not unheard of), and there may well be a much better/faster way to cope with this data - but it depends on what Krass is actually trying to achieve.

Hah. The usefulness of those forums will always amaze me.

I never played with UDT. At least not for such large jobs. I'll dig into that and that'll probably be my next posts topic!

A million records is something I never had to play with, too. Those X,Y coordinates are actually the coordinates of each "given-color"-pixels in many different pictures, which I'll have to compare with another smaller array (another picture).

Thanks for the feedback.

From a brief first reading of Ellis Dee's idea of using a User-Defined Type Array, the following questions pops up:

Will I be able to redim that array with PRESERVE just like normal arrays?

the .info (declared as string) will be filled with data 0.005% of the time. I guess it would be a waste of memory (speed) having that string declaration in there? I mean, even if it was integer! I'll need to know that the "sub-array" I'm browsing right now is, let's say, "252", but I'll only note that down when I'm reaching a new sub-array, I won't be using its content all the time.

Also I don't think the UBound result should be stored in the UDT, since it's pretty static and an outside variable would do the job as well.

Just my first 2 cents.. I'll work a bit on this and see how it goes.

Thanks

You will be able to ReDim in the usual way, as the UDT's (MainType and CoordinateType) are treated the same way as other data types (Integer/String/..).

Your concerns about the .Info and .Length items aren't valid, as there is only one copy for each set of data, and the actual X/Y data is in the Coords() array (which itself is based on a UDT!).


It would probably be a good idea to create a quick test program with a similar data structure, and step through it (including adding items to the watch window) to help you understand it.

si_the_geek, thanks for clearing things up.

I understand that whole UDT thing very good now, well.... how Ellis Dee used it. - I must say that looks perfect. Nicely done, Ellis Dee, your sample code will probably look a lot like my final code ;)

While playing Guitar Hero 2 and Settlers of Catan tonight I was thinking of keeping it the way it is (but manage to avoid strings). But that new method sure looks sweet!

You guys should play guitar hero 2..

Thanks for the help again
(gh2)

Quote:

---

Originally Posted by Krass

Also I don't think the UBound result should be stored in the UDT, since it's pretty static and an outside variable would do the job as well.

---

Either way works, but in the code sample above the UBound() effectively is "outside", because it's not a part of the coordinates array.

Is each subarray the same length? If so, then you're right, it's pointless waste to maintain 500 copies of the same number in the UDT.

For the text descriptions, though, regardless of how infrequently you need to reference them the way it is shown above is the best way to go if you have a different description for each subarray.

Ellis, each subarray will indeed be of a diffrent length. So your method is good.

That makes me think: some of my arrays are going to be 300-long, some 900-long, but the average will be about 500. I guess it would be a good idea to redim Coords() to 1000 right at the beginning of my procedure (when I fill the X,Y) .. and, at the end, do a last negative redim to remove all unused rows?

That sounds like a good idea to me, because I used to do a loop and do a redim preserve to add +1 each time I need to add a new X,Y.. So that's a lot or redim PRESERVE.

My only worry is: Are the memory/ressources gonna get free'd nicely without hassle? Would you recommend this approach?

(... I *just* realised : if this approach is good, alright.. it is good knowledge for me. But that would be optimizing the part of filling my arrays - the part that is not critical for me, optimizingly speaking!.. oh well)

Thanks.

Quote:

---

Originally Posted by Krass

That makes me think: some of my arrays are going to be 300-long, some 900-long, but the average will be about 500. I guess it would be a good idea to redim Coords() to 1000 right at the beginning of my procedure (when I fill the X,Y) .. and, at the end, do a last negative redim to remove all unused rows?

---

That'd work fine. Memory will be freed nicely when you ReDim Preserve it down to the final size after each subarray loop finishes.

You don't have any way to identify how many coordinate pairs are in a given subarray before you start populating it?

Just out of interest, you say this is about comparing arrays (representing particular pixels) between many different images. Would you mind telling us a little more about that? There are some blindingly fast ways to work with pixel data which might help if appropriate.

Each sub-arrays are MASKS of a word (for some reason I couldn't go by letter). Comparing all those sub-arrays (masks) against one other main picture will allow me to extract info from that image as it if was text (some player stats info from an online game: that has been done before, it's something like it, but in a more personal field) - anyway you get the idea.

My loops are checking up blue pixels, as it's all I need. Sometimes yellow, for numbers - in case I need that to be done.

I know I've been proven wrong before (!), but I'm pretty sure the part of storing X,Ys in a database, to then fill an array/udt is the way to go. Those sub-arrays are actually created by me, dynamically, and it would slow down the whole process to have it deal with "finding blue pixels" everytime in my "huge" loop. - those are already found out and in a DB!

That method was primarily used to avoid using the very slow Pic1.point method which would slow down the process a lot. (because my sub arrays are X,Y coords retrieved from a picturebox, loaded up with each of my sub-arrays one after the other).

That might be confusing.

One optimization that will need to be done is a technique I heard in one of my last posts on which I'll have to give more attention when my main "looping" code is up and running, optimized. It consists in turning the picturebox content in an array instantly - it sounded like a nice technique, but on the first read I didn't quite understood - will get back to it later.

Ellis Dee: You are right. Once the X,Ys are in my DB, I could easily know how long I must create the coordinate array - avoiding redims..... Like I said, I don't need much optimization for THAT application portion but - it'll be done that way!

Thank you's!

The Point method (and similar ones) are painfully slow... it sounds like your future method is DIB Sections, which (once you've got your head around them!) will make a huge improvement to the speed of reading/writing pixels.

Here's a quick way to get those pixels into an array using GetDIBits. There is a slight caveat :D extacting the data this way inverts the Y axis and alters the colours (they become BGRA as opposed to RGBA). I've included a conversion function to toggle between RGBA and BGRA. There is also an option to produce a two Dimensional array but you will find the single dimensional array performs better. If you want to use this with the two dimensional array you will have to invert the Y coordinates in your Database, much better would be to then convert each pair of X and inverted Y integers into a single Long and use a one dimensional array, this is easier than it sounds and can be done quickly.

Also seeing as you are only interested in a few colours it's worth converting the image data from a long array and into a byte array that just holds the colours you are interested in, see the retByteData function.

vb Code:

'Module code
Option Explicit
 
Private Declare Function GetObjectA Lib "GDI32" (ByVal hObject As Long, ByVal nCount As Long, ByRef lpObject As Any) As Long
Private Declare Function GetDIBits Lib "GDI32" (ByVal hDC As Long, ByVal hBitmap As Long, ByVal nStartScan As Long, ByVal nNumScans As Long, lpBits As Any, lpBI As BITMAPINFO, ByVal wUsage As Long) As Long
Private Declare Sub RtlMoveMemory Lib "kernel32" (Destination As Any, Source As Any, ByVal Length As Long)
 
Private Type BITMAP '24 bytes
 bmType As Long
 bmWidth As Long
 bmHeight As Long
 bmWidthBytes As Long
 bmPlanes As Integer
 bmBitsPixel As Integer
 bmBits As Long
End Type
 
Private Type RGBQUAD
 Blue As Byte
 Green As Byte
 Red As Byte
 Alpha As Byte
End Type
 
Private Type BITMAPINFOHEADER '40 bytes
 bmSize As Long
 bmWidth As Long
 bmHeight As Long
 bmPlanes As Integer
 bmBitCount As Integer
 bmCompression As Long
 bmSizeImage As Long
 bmXPelsPerMeter As Long
 bmYPelsPerMeter As Long
 bmClrUsed As Long
 bmClrImportant As Long
End Type
 
Private Type BITMAPINFO
 bmHeader As BITMAPINFOHEADER
 bmColors(0 To 255) As RGBQUAD
End Type
 
Public Function RetDIBDataLong(PictureObject As Object, Optional Return2DArray As Boolean) As Long()
Dim BM As BITMAP, bmi As BITMAPINFO, lngImageData() As Long
   'The PictureObject must be an object with both a HDC and Image property
   'such as a PictureBox, UserControl or Form
   
   GetObjectA PictureObject.Image, 24, BM
   bmi.bmHeader.bmWidth = BM.bmWidth
   bmi.bmHeader.bmHeight = BM.bmHeight
   bmi.bmHeader.bmSize = 40
   bmi.bmHeader.bmPlanes = 1
   bmi.bmHeader.bmBitCount = 32
   
   If Return2DArray Then
     ReDim lngImageData(BM.bmWidth - 1, BM.bmHeight - 1)
     GetDIBits PictureObject.hDC, PictureObject.Image.Handle, 0, BM.bmHeight, lngImageData(0, 0), bmi, 0
   Else
     ReDim lngImageData(BM.bmWidth * BM.bmHeight - 1)
     GetDIBits PictureObject.hDC, PictureObject.Image.Handle, 0, BM.bmHeight, lngImageData(0), bmi, 0
   End If
   
   RetDIBDataLong = lngImageData
End Function
 
Public Function retByteData(PictureObject As Object, SearchPalette() As Long) as Byte()
'The ImageObject must be an object with both a HDC and Image property
'such as a PictureBox, UserControl or Form
 
'The searchpalette should contain no more than 255 colours and these
'colours must be in the form of DIBSection colours i.e. BGRA not RGBA see
'ConvertColour function below
 
 
Dim i As Long, ii As Long, lPalLen As Long, lngImageData() As Long, bytImageData() As Byte
  lPalLen = UBound(SearchPalette)
  If lPalLen > 254 Then lPalLen = 254
  
  lngImageData = RetDIBDataLong(PictureObject)
  i = UBound(lngImageData)
  ReDim bytImageData(i)
  
  For i = 0 To i
    For ii = 0 To lPalLen
      If lngImageData(i) = SearchPalette(ii) Then
        bytImageData(i) = ii + 1
        Exit For
      End If
    Next ii
  Next i
  
 'If you only want a couple of colours they might be better HardCoded here rather
 'than in the passed SearchPalette, example loop would then be...
  'For i = 0 To i
      'Select Case lngImageData(i)
        'Case Colour1
          'bytImageData(i) = 1
        'Case Colour2
          'bytImageData(i) = 2
      'End Select
  'Next i
  
  retByteData = bytImageData
End Function
 
Public Function ConvertColour(ByVal Colour As Long) As Long
Dim uRGBA As RGBQUAD, Swp As Byte
  RtlMoveMemory uRGBA, Colour, 4&
  With uRGBA
     Swp = .Red
    .Red = .Blue
    .Blue = Swp
  End With
  RtlMoveMemory Colour, uRGBA, 4&
  ConvertColour = Colour
End Function

vb Code:

'Example calling code for a form with two pictureboxes and two command buttons
Option Explicit
Private Declare Function SetPixelV Lib "GDI32" (ByVal hDC As Long, ByVal X As Long, ByVal Y As Long, ByVal crColor As Long) As Byte
 
Private Sub Command1_Click()
Dim PicData() As Byte, pal() As Long, i As Long, W As Long
  ReDim pal(3)
  'First fill the pal array with the colours we want to capture, up to 255
  'RGB can be used to generate 32bpp DIB Colours by swaping Red for Blue and visa versa
  pal(0) = RGB(0, 3, 1) 'same as ConvertColour(RGB(1, 3, 0))
  pal(1) = RGB(3, 0, 15)
  pal(2) = RGB(33, 0, 206)
  pal(3) = RGB(249, 252, 250)
  PicData = retByteData(Picture1, pal)
  
  'This next bit is slow, it is just to show the data we have just gained visualy
  With Picture1
    W = Picture1.ScaleX(.ScaleWidth, .ScaleMode, vbPixels)
  End With
  
  Picture2.Cls
  For i = 0 To UBound(PicData)
    If PicData(i) Then SetPixelV Picture2.hDC, i Mod W, i \ W, QBColor(PicData(i))
  Next i
  'As you can see the Y axis has been inverted
End Sub
 
 
Private Sub Command2_Click()
 
Dim PicData() As Long, X As Long, Y As Long, UBY As Long
  'Get the pixel data very quickly
  PicData = RetDIBDataLong(Picture1, True)
  
  'Then a slow method to show how the Y axis is inverted and the Red and Blue bytes are swapped
  Picture2.Cls
  UBY = UBound(PicData, 2)
  For X = 0 To UBound(PicData, 1)
    For Y = 0 To UBY
      SetPixelV Picture2.hDC, X, Y, PicData(X, Y)
    Next Y
  Next X
End Sub

To copy the code, quote the post and copy from there.
Attached is the image I tested this with.

Hello.

I think I'm done optimizing my main loop. Using the method you guys suggested (user defined types) - and avoiding strings - significantly speeds up the process.

I don't think the code can be done better, below is an idea of what it looks like... It's designed to find matching pixels (same X,Y from my sub-array (from 1 millions record array - about 500 records each times) as the X,Y from the small image I'm comparing it to).

Code:

---

Do While xSmall <= intSmallUBound - 1 And xHuge <= HugeType(i).Length - 1
    If HugeType(i).Coords(xHuge).x < (arrSmallSubArray(0, xSmall) + xAdd) Then
        xHuge = xHuge + 1
    ElseIf HugeType(i).Coords(xHuge).x > (arrSmallSubArray(0, xSmall) + xAdd) Then
        xSmall = xSmall + 1
    ElseIf HugeType(i).Coords(xHuge).y < (arrSmallSubArray(1, xSmall) + yAdd) Then
        xHuge = xHuge + 1
    ElseIf HugeType(i).Coords(xHuge).y > (arrSmallSubArray(1, xSmall) + yAdd) Then
        xSmall = xSmall + 1
    Else
        xSmall = xSmall + 1
        xHuge = xHuge + 1
        MatchCount = MatchCount + 1
    End If
Loop

---

My next step is to read, understand and implement Milk's piece of code. Btw thank you, that seems to be pretty complete. That's the last portion slowing down my application with those .pset and .point calls. I'm hoping to have time testing this next week.

I've got a (maybe a little off-topic) question. When populating a recordset, why must I use .movelast to be able to retrieve .recordcount? If I just populate it and check the .recordcount it will always show "0". Not a big problem, but I find it weird to have to use .movelast and .movefirst to retrieve that info.

Thank you!

Quote:

---

Originally Posted by Krass

When populating a recordset, why must I use .movelast to be able to retrieve .recordcount? If I just populate it and check the .recordcount it will always show "0". Not a big problem, but I find it weird to have to use .movelast and .movefirst to retrieve that info.

---

It's an optimization; the server doesn't send the entire recordset until it has to.

If you have some time, I've always been curious if the With operator speeds up UDT array access. If you change the code you posted to the following, is it any faster?

Code:

---

With HugeType(i)
    Do While xSmall <= intSmallUBound - 1 And xHuge <= .Length - 1
        If .Coords(xHuge).x < (arrSmallSubArray(0, xSmall) + xAdd) Then
            xHuge = xHuge + 1
        ElseIf .Coords(xHuge).x > (arrSmallSubArray(0, xSmall) + xAdd) Then
            xSmall = xSmall + 1
        ElseIf .Coords(xHuge).y < (arrSmallSubArray(1, xSmall) + yAdd) Then
            xHuge = xHuge + 1
        ElseIf .Coords(xHuge).y > (arrSmallSubArray(1, xSmall) + yAdd) Then
            xSmall = xSmall + 1
        Else
            xSmall = xSmall + 1
            xHuge = xHuge + 1
            MatchCount = MatchCount + 1
        End If
    Loop
End With

---

Also, I see couple tweaks you could do to squeeze a little more speed out of the posted code.

Code:

---

With HugeType(i)
    Do While xSmall < intSmallUBound And xHuge < .Length ' removing the two subtractions should help a bit
        Select Case arrSmallSubArray(0, xSmall) + xAdd
            Case Is > .Coords(xHuge).x: xHuge = xHuge + 1
            Case Is < .Coords(xHuge).x: xSmall = xSmall + 1
            Case Else
                Select Case arrSmallSubArray(1, xSmall) + yAdd
                    Case Is > .Coords(xHuge).y: xHuge = xHuge + 1
                    Case Is < .Coords(xHuge).y: xSmall = xSmall + 1
                    Case Else
                        xSmall = xSmall + 1
                        xHuge = xHuge + 1
                        MatchCount = MatchCount + 1
                End Select
        End Select
    Loop
End With

---

While I concede that this logic is much less readable, it should be perceptibly more efficient. Every iteration of the Do...Loop has at least 2 fewer arithmetic operations, and each match saves another two operations. Given the huge number of passes this loop is making, this could add up to a fairly decent speed boost.

I don't quite understand what the code is supposed to achieve, but there are a few things you could do that may improve the speed a bit.

The With block as Ellis suggested should give an improvement, as should the changes on the Do line.


Over the life of the loop you are repeatedly checking the same values which involve array access (slightly slower than reading a variable), and some of them also involve calculations too (which again take time).

I would recommend storing the result of (arrSmallSubArray(0, xSmall) + xAdd) (and the Y equivalent into variables and using them instead - of course re-calculating the value whenever xSmall changes.

You could do similar for HugeType(i).Coords(xHuge) , as that also takes time (two array accesses, and one member lookup), resetting it whenever xHuge changes. If you use a With block, this may not get much benefit.

Ellis's use of Select Case is a reasonable alternative to these variables, but I think the variables will be faster... although the Select Case method can be improved by using Sgn function, eg:

Code:

---

        Select Case Sgn((arrSmallSubArray(0, xSmall) + xAdd) - (.Coords(xHuge).x))
            Case 1: xHuge = xHuge + 1
            Case -1: xSmall = xSmall + 1
            case Else

---

..whether this beats the variables depends on various factors, so would need to be tested.


There is another possible improvement which is highly dependent on the data, and may not make a big difference.. if the final Else condition is a regular occurrence, you would find speed gains by re-ordering the If's (as an = comparison is faster than a < one, and it would mean a maximum of 3 If's instead of 4).


As to the recordset issue, you can get a RecordCount without a MoveLast if set the CursorLocation to clientside, and change the CursorType. But.. that is slow, and for speed you should run a "SELECT Count(*)" type query instead.

Quote:

---

Originally Posted by si_the_geek

... although the Select Case method can be improved by using Sgn function, eg:

Code:

---

        Select Case Sgn((arrSmallSubArray(0, xSmall) + xAdd) - (.Coords(xHuge).x))
            Case 1: xHuge = xHuge + 1
            Case -1: xSmall = xSmall + 1
            case Else

---

---

This benchmarks much slower on my machine:

vb Code:

Private Sub Sample()
    Const Iterations = 10000000
    Dim sngStart As Single
    Dim i As Long
    Dim x As Long
    Dim y As Long
    
    x = 32
    y = 57
    sngStart = Timer
    For i = 1 To Iterations
        Select Case x
            Case Is < y
        End Select
    Next
    MsgBox Format(Timer - sngStart, "0.000")
    sngStart = Timer
    For i = 1 To Iterations
        Select Case Sgn(x - y)
            Case -1
        End Select
    Next
End Sub

I get 0.062 and 0.230 respectively. It effectively adds a function call and an operation, though four times slower is more extreme than I expected.

Your other suggestions will almost certainly speed things up a bit despite the code sprawl and extra assignments:

Code:

---

Dim lngHugeX As Long
Dim lngHugeY As Long
Dim lngSmallX As Long
Dim lngSmallY As Long
Dim lngLength As Long

With HugeType(i)
    lngLength = .Length
    With .Coords(xHuge)
        lngHugeX = .x
        lngHugeY = .y
    End With
    lngSmallX = arrSmallSubArray(0, xSmall) + xAdd
    lngSmallY = arrSmallSubArray(1, xSmall) + yAdd
    Do While xSmall < intSmallUBound And xHuge < lngLength
        Select Case lngHugeX
            Case Is < lngSmallX
                xHuge = xHuge + 1
                With .Coords(xHuge)
                    lngHugeX = .x
                    lngHugeY = .y
                End With
            Case Is > lngSmallX
                xSmall = xSmall + 1
                lngSmallX = arrSmallSubArray(0, xSmall) + xAdd
                lngSmallY = arrSmallSubArray(1, xSmall) + yAdd
            Case Else
                Select Case lngHugeY
                    Case Is < lngSmallY
                        xHuge = xHuge + 1
                        With .Coords(xHuge)
                            lngHugeX = .x
                            lngHugeY = .y
                        End With
                    Case Is > lngSmallY
                        xSmall = xSmall + 1
                        lngSmallX = arrSmallSubArray(0, xSmall) + xAdd
                        lngSmallY = arrSmallSubArray(1, xSmall) + yAdd
                    Case Else
                        xSmall = xSmall + 1
                        xHuge = xHuge + 1
                        MatchCount = MatchCount + 1
                        With .Coords(xHuge)
                            lngHugeX = .x
                            lngHugeY = .y
                        End With
                        lngSmallX = arrSmallSubArray(0, xSmall) + xAdd
                        lngSmallY = arrSmallSubArray(1, xSmall) + yAdd
                End Select
        End Select
    Loop
End With

---

If you are comparing arrSmallSubArray() to all 500 HugeType() subarrays, then you could definitely optimize that by adding xAdd and yAdd to the values in a single loop once before you begin the million comparisons. Even if you then went through and subtracted the offsets back out at the end, the speed savings should be significant.

Of course, doing that would reduce the speed improvement of the above code, since there would be fewer operations that get avoided.

Now that I'm thinking about it, I think it will be as fast or faster to read an array value twice than it is to read it once and assign it to another variable, so I'm not sure how effective the above code will be. I'll craft yet another version and put it in a new post.

Code:

---

For i = 0 To UBound(arrSmallSubArray, 2)
    arrSmallSubArray(0, i) = arrSmallSubArray(0, i) + xAdd
    arrSmallSubArray(1, i) = arrSmallSubArray(1, i) + yAdd
Next
'....code you didn't post goes here
For i = 0 To UBound(HugeType)
    '....code you didn't post goes here
    With HugeType(i)
        Do While xSmall < intSmallUBound And xHuge < .Length ' removing the two subtractions should help a bit
            With .Coords(xHuge)
                Select Case arrSmallSubArray(0, xSmall)
                    Case Is > .x: xHuge = xHuge + 1
                    Case Is < .x: xSmall = xSmall + 1
                    Case Else
                        Select Case arrSmallSubArray(1, xSmall)
                            Case Is > .y: xHuge = xHuge + 1
                            Case Is < .y: xSmall = xSmall + 1
                            Case Else
                                xSmall = xSmall + 1
                                xHuge = xHuge + 1
                                MatchCount = MatchCount + 1
                        End Select
                End Select
            End With
        Loop
    End With
    '....code you didn't post goes here
Next
'....code you didn't post goes here
For i = 0 To UBound(arrSmallSubArray, 2)
    arrSmallSubArray(0, i) = arrSmallSubArray(0, i) - xAdd
    arrSmallSubArray(1, i) = arrSmallSubArray(1, i) - yAdd
Next

---

Note the highlighted interior With block. That should make the interior loop as fast or faster than the method of assigning the values to local variables, which will help keep the number of local variables down to a manageable level.

EDIT: I'm waffling. While I stand by the code in this post, I think si's technique may help if applied to just the arrSmallSubArray values. By how much I can't even guess.

Hello. I was more than happy to benchmark some of your solutions, and here are the results (in seconds - this was done over a small set of records - of course using exactly the same for all tests, executed 3 times each):

Quote:

---

Exactly as I first posted it : 29, 29, 29
Exactly as I first posted it (no -1 in do) : 29, 29, 29
After Ellis's post 28 (without "with") : 29, 28, 29
After Ellis's post 28 (with "with") : 25, 26, 26
After geek's post 29 (without variable - using 2 sgn calls) : 26, 25, 26

---

I am about to test the code using variable instead. My tests stops are post #29.

Si_the_geek: "=" won't be the most regular occurence... Those < and > operators are used a lot to move my pointer in the arrays UNTIL they are "=".

And also I was wondering if I should use the SELECT count(*) instead of using .movelast/.movefirst. Because after all, I need all the populated info in the recordset and loop it, so using count(*) would mean populating ANOTHER recordset just to know the recordcount.

You guys are really into it. Devoted help - I like.

Fair point on the sgn etc Ellis.. but your test was biased, as you only compared to one outcome rather than two, and there weren't array/property accesses on the cases either.

Of course, the effectiveness of my method would depend on the actual data in use - and if there is a preference towards the first outcome then the extra function and subtraction (which I admittedly didn't account for fully) will make it slower.


It's all academic tho, as your latest version using the extra variables eliminates the problem that the method was trying to solve!

Quote:

---

Originally Posted by si_the_geek

Fair point on the sgn etc Ellis.. but your test was biased, as you only compared to one outcome rather than two, and there weren't array/property accesses on the cases either.

---

Conceded.

Quote:

---

Originally Posted by Krass

Hello. I was more than happy to benchmark some of your solutions, and here are the results (in seconds - this was done over a small set of records - of course using exactly the same for all tests, executed 3 times each):

---

Compiled exe, right? IDE rsults aren't meaningful...

As you may have guessed, I (and several others on the boards) enjoy optimizing code for speed, so if there are other sections to your processing loop, I'd be happy to take a look at the whole routine.

No that was not on compiled exe. Only by executing it. Shouldn't that be meaningful if they were ALL tried that way? (conceded it may in fact run faster after compilation)...

Strangely enough, using the same code (from my last test - 26, 25, 26) with an "interior with" have dropped me back to 29, 30, 29. (still using sgn)

Based on those tests, the 2 sgn calls seems to give the same speed as not using them. I guess I can just use them anyway?..

What seemed to really give a performance boost was the first global "with", without "interior with", clocking at "25, 25, 26"....

I still havn't tested using a variable at any point (except for those already in my first post, of course)

Testing speed in the IDE is virtually meaningless I'm afraid - code not only runs slower, but also in a different way completely.

In the past I have seen two similar pieces of code that were compared, one of them was about twice as fast as the other in the IDE, but then several times slower than it when compiled!

Quote:

---

Originally Posted by Krass

No that was not on compiled exe. Only by executing it. Shouldn't that be meaningful if they were ALL tried that way? (conceded it may in fact run faster after compilation)...

---

No, the IDE will be slower on some things but not others. IDE results have no predictive value on compiled speed when comparing different methods.

If you were comparing the same method with different data, the relative speeds in the IDE may give you rough idea, but even then the ratios will be all wrong. (28, 26 and 30 show much less variation than 7, 5, 6, for example.)

That thread will have learned me a couple of things.

I think I'll be short on time today but hopefully I'll be able to re-do those timechecks with compiled IDE soon. And will post the results.

I actually became very curious as to how it will differ!..

I guess the compiled version will show a difference between using sgn calls or not. In IDE, it changed absolutely nothing.

Also, that "interior with" was bad, adding an average of 5 seconds to the process. Do you think it has chances of improving when compiled? Only one way to know, I guess!

Quote:

---

Originally Posted by Krass

Also, that "interior with" was bad, adding an average of 5 seconds to the process. Do you think it has chances of improving when compiled?

---

Almost certainly.

I was able to re-do those tests with compiled version and here are the results:

Note that I still didn't even try to use more variables than there was in my original post. I guess that would be the next test.

Quote:

---

Test#1: Exactly as I first posted it : 14, 14, 13
Test#2: Exactly as I first posted it (no -1 in do) : 14, 14, 14
Test#3: After Ellis's post 28 (without "with") : 13, 13, 13
Test#4: After Ellis's post 28 (with "with") : 13, 12, 13
Test#5: After geek's post 29 (without variable - using 2 sgn calls) : 13, 12, 12
Test#6: Added interior with : 14, 14, 13

---

...Looks to me that the best solutions is to use those sgn calls and avoid the "interior with" (Test #5).

I am reposting what the code now looks like:

Code:

---

Do While xSmall < intSmallUBound And xHuge < .Length ' removing the two subtractions should help a bit
    Select Case Sgn((arrSmallSubArray(0, xSmall) + xAdd) - (.Coords(xHuge).x))
        Case 1: xHuge = xHuge + 1
        Case -1: xSmall = xSmall + 1
        Case Else
            Select Case Sgn((arrSmallSubArray(1, xSmall) + yAdd) - (.Coords(xHuge).y))
                Case 1: xHuge = xHuge + 1
                Case -1: xSmall = xSmall + 1
                Case Else
                    xSmall = xSmall + 1
                    xHuge = xHuge + 1
                    MatchCount = MatchCount + 1
            End Select
    End Select
Loop

---

That looks good, doesn't it?