Groups > comp.lang.basic.visual.misc > #832

Re: What is a class?

From	Schmidt <sss@online.de>
Newsgroups	comp.lang.basic.visual.misc
Subject	Re: What is a class?
Date	2012-02-27 19:09 +0100
Organization	Aioe.org NNTP Server
Message-ID	<jiggst$rbl$1@speranza.aioe.org> (permalink)
References	(7 earlier) <jicag2$87f$1@dont-email.me> <5odjk75idm6fgl98vbmabap2djd38nvum5@4ax.com> <jicjab$6ta$1@dont-email.me> <6sD2r.204924$WX2.187127@newsfe28.ams2> <jig9cq$val$1@dont-email.me>

Show all headers | View raw

Am 27.02.2012 17:01, schrieb GS:

> Unfortunately, there was no apparent performance improvement as the
> function took the same amount of time to process for each of the 4
> possible results. I invite comments...
>

I think the routine as it is (based on VB(A).Collections)
cannot be improved much more (maybe by 10-15% or so ...).

And since you're running in PCode whilst working in VBA,
there's also not much to improve, by implementing your
own "lightweight, specialized Array-BinarySort-stuff",
which only makes sense when you can native compile in VB6.
With your own, selfimplemented fast "Exists-Checks-Hash-
Class" (in a VB6-Dll) you could go a bit faster
(at the cost of a Dll-dependency in the VBA-environment).


Since I know, that you make use of the RichClient-stuff -
(and if the Dll-dependency topic is a "no-issue" on your
  end) then you could also make use of an already implemented
"fast, Keyed-Access-Container" from this lib (cSortedDictionary).

Here's some Code, which works about 3 times as fast
as your code (I have only XL-2000 with a 65536-Row-Limit)
and this ratio should get better with more Items in the Compare-
List, since I know, that the cSortedDictionary scales nearly
linearly with higher ItemCounts - whereas the VB.Collection
does not (it get's "overproportionally slower with more items
to hash within).

To be able to test this also in VB6, I've changed the
Param-Structure to my needs - but maybe you find my
changes useful too (I give VArr() params directly
into the Procedure - as well as two new Enum-Params.

This is my "Excel-Button"-click Event, which triggers
the whole thing here on my VM (just that you see, that
everything is "yet there" <g>...

Private Sub cmdCompare2_Click()
     Dim T!, Matches As Long
     Dim vFiltrRange(): vFiltrRange = Range("A1:A50000")
     Dim vCheckRange(): vCheckRange = Range("B1:B50000")
     T = Timer

       Matches = FilterMatches2(vFiltrRange, vCheckRange, "D", _
                 Abs(Not chkReturnMatches), chkDupesAllowed)

     txtTiming.Text = Timer - T
     txtReport.Text = Matches
End Sub


Ok - here the Code - please let me know how it performs with
500000 items on your end - and if everything was understood
correctly by me (regarding your intentions with the generated OutPut).


'***** Into a normal VB(A)-Module *****
Option Explicit

Public Enum FilterMode
   fltReturnMatches
   fltReturnNonMatches
End Enum

Public Enum ExpectedTypes
   UseStringComparison
   UseIntegerComparisons
   UseDoubleComparisons
End Enum

'Returns Count of found Matches or NonMatches (accord. to the Mode-Enum)
'One can achieve additional speedup, when the "Types are known" - e.g.
'when both Compare-Ranges consist "only of Doubles" - or alternatively
'are made up completely from LongInt or Currency-Types - then the
'Default-Method (UseStringComparison, see last Param) can be overridden
Function FilterMatches2&(vFilterRng(), vCheckRng(), _
                          Optional OutColName As String, _
                          Optional ByVal Mode As FilterMode, _
                          Optional ByVal DupesAllowed As Boolean, _
                          Optional ByVal TypeComparison As ExpectedTypes)

Dim DCheck As cSortedDictionary, DDupes As cSortedDictionary
Dim i As Long, Key, Match As Boolean, Results(), ResCount As Long, Out()

     Set DCheck = New cSortedDictionary
         DCheck.StringCompareMode = TextCompare
     Set DDupes = New cSortedDictionary
         DDupes.StringCompareMode = TextCompare

     For i = LBound(vCheckRng) To UBound(vCheckRng)
         Select Case TypeComparison
           Case UseStringComparison:   Key = CStr(vCheckRng(i, 1))
           Case UseIntegerComparisons: Key = CCur(vCheckRng(i, 1))
           Case UseDoubleComparisons:  Key = CDbl(vCheckRng(i, 1))
         End Select
         If Not DCheck.Exists(Key) Then DCheck.Add Key
     Next i

     If DCheck.Count = 0 Then Exit Function

     ReDim Results(1 To DCheck.Count)

     For i = LBound(vFilterRng) To UBound(vFilterRng)
        Select Case TypeComparison
          Case UseStringComparison:   Key = CStr(vFilterRng(i, 1))
          Case UseIntegerComparisons: Key = CCur(vFilterRng(i, 1))
          Case UseDoubleComparisons:  Key = CDbl(vFilterRng(i, 1))
        End Select
        Match = DCheck.Exists(Key)

        If Match And Mode = fltReturnMatches Then
           If Not DupesAllowed Then DCheck.Remove Key
           ResCount = ResCount + 1: Results(ResCount) = vFilterRng(i, 1)
        ElseIf Not Match And Mode = fltReturnNonMatches Then
           If DupesAllowed Then
             ResCount = ResCount + 1: Results(ResCount)=vFilterRng(i, 1)
           ElseIf Not DDupes.Exists(Key) Then
             DDupes.Add Key
             ResCount = ResCount + 1: Results(ResCount)=vFilterRng(i, 1)
           End If
        End If
     Next i

     If ResCount = 0 Or Len(OutColName) = 0 Then Exit Function

     ReDim Out(1 To ResCount, 1 To 1)
     For i = 1 To ResCount: Out(i, 1) = Results(i): Next i 'copy over

     '**only the following block needs to be commed out to test in VB6**
     Columns(OutColName).ClearContents
     With Range(OutColName & "1").Resize(ResCount, 1)
       .Value = Out
       .NumberFormat = "0000000000000" '..optional
       .EntireColumn.AutoFit '..optional
     End With
     '************ End of the Excel/VBA-related code-block *************

     FilterMatches2 = ResCount
End Function

Olaf

Back to comp.lang.basic.visual.misc | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-24 04:02 -0800
  Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-24 14:06 +0000
    Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-25 03:07 -0800
      Re: What is a class? Helmut_Meukel <Helmut_Meukel@bn-hof.invalid> - 2012-02-25 14:19 +0100
      Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-25 21:26 +0000
        Re: What is a class? Jim Mack <no-uce-ube@mdxi.com> - 2012-02-25 18:58 -0500
          Re: What is a class? GS <gs@somewhere.net> - 2012-02-25 19:39 -0500
          Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-26 03:09 +0000
            Re: What is a class? Jim Mack <no-uce-ube@mdxi.com> - 2012-02-25 22:35 -0500
              Re: What is a class? GS <gs@somewhere.net> - 2012-02-25 22:55 -0500
                Re: What is a class? "Farnsworth" <nospam@nospam.com> - 2012-02-25 23:27 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-26 01:30 -0500
                Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-26 04:41 +0000
                Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-25 23:05 -0600
                Re: What is a class? "Henning" <computer_hero@coldmail.com> - 2012-02-26 11:24 +0100
                Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-25 23:03 -0600
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-26 01:26 -0500
                Re: What is a class? "Stuart McCall" <smccall@myunrealbox.com> - 2012-02-27 04:15 +0000
                Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-27 00:48 -0600
                Re: What is a class? "Mayayana" <mayayana@invalid.nospam> - 2012-02-27 08:57 -0500
                Re: What is a class? "Stuart McCall" <smccall@myunrealbox.com> - 2012-02-27 19:19 +0000
                Re: What is a class? "Stuart McCall" <smccall@myunrealbox.com> - 2012-02-27 19:12 +0000
                Re: What is a class? "Bob Butler" <bob_butler@cox.invalid> - 2012-02-27 06:06 -0800
                Re: What is a class? "Stuart McCall" <smccall@myunrealbox.com> - 2012-02-27 19:24 +0000
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 11:01 -0500
                Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-27 11:07 -0600
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 13:10 -0500
                Re: What is a class? Schmidt <sss@online.de> - 2012-02-27 19:09 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 13:29 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 14:43 -0500
                Re: What is a class? Schmidt <sss@online.de> - 2012-02-27 22:14 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 16:45 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 18:23 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-27 23:26 -0500
                Re: What is a class? Schmidt <sss@online.de> - 2012-02-28 13:54 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-28 08:37 -0500
                Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-28 09:19 -0600
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-28 10:42 -0500
                Re: What is a class? Schmidt <sss@online.de> - 2012-02-29 06:47 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-29 09:39 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-29 13:33 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-29 14:43 -0500
                Re: What is a class? Schmidt <sss@online.de> - 2012-02-29 21:39 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-29 16:15 -0500
                Re: What is a class? Schmidt <sss@online.de> - 2012-02-29 23:20 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-28 00:16 -0500
                Re: What is a class? Jim Mack <no-uce-ube@mdxi.com> - 2012-02-27 13:26 -0500
                Re: What is a class? "Stuart McCall" <smccall@myunrealbox.com> - 2012-02-27 19:33 +0000
                Re: What is a class? Jim Mack <no-uce-ube@mdxi.com> - 2012-02-27 15:50 -0500
                Re: What is a class? Helmut_Meukel <Helmut_Meukel@bn-hof.invalid> - 2012-02-26 11:02 +0100
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-26 14:23 -0500
                Re: What is a class? GS <gs@somewhere.net> - 2012-02-26 17:09 -0500
            Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-25 22:21 -0600
              Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-26 04:51 +0000
                Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-25 23:10 -0600
        Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-26 04:11 -0800
          Re: What is a class? Helmut_Meukel <Helmut_Meukel@bn-hof.invalid> - 2012-02-26 16:37 +0100
          Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-26 18:36 +0000
            Re: What is a class? ralph <nt_consulting64@yahoo.net> - 2012-02-26 14:11 -0600
            Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-27 03:11 -0800
              Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-27 16:33 +0000
                Re: What is a class? Jim Mack <no-uce-ube@mdxi.com> - 2012-02-27 13:33 -0500
                Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-28 04:07 -0800
    Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-25 03:33 -0800
    Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-25 04:19 -0800
  Re: What is a class? "Ivar" <ivar.ekstromer000@ntlworld.com> - 2012-02-24 15:15 +0000
    Re: What is a class? GS <gs@somewhere.net> - 2012-02-24 14:52 -0500
      Re: What is a class? "Ivar" <ivar.ekstromer000@ntlworld.com> - 2012-02-24 23:10 +0000
        Re: What is a class? GS <gs@somewhere.net> - 2012-02-24 19:46 -0500
          Re: What is a class Way off Topic "Ivar" <ivar.ekstromer000@ntlworld.com> - 2012-02-25 02:20 +0000
            Re: What is a class Way off Topic GS <gs@somewhere.net> - 2012-02-25 16:57 -0500
        Re: What is a class? Helmut_Meukel <Helmut_Meukel@bn-hof.invalid> - 2012-02-25 11:36 +0100
    Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-25 03:35 -0800
  Re: What is a class? Jim Mack <no-uce-ube@mdxi.com> - 2012-02-24 15:23 -0500
    Re: What is a class? "Auric__" <not.my.real@email.address> - 2012-02-25 01:33 +0000
    Re: What is a class? Peter Nolan <peter.nolan40@gmail.com> - 2012-02-25 03:09 -0800
  Re: What is a class? "Mayayana" <mayayana@invalid.nospam> - 2012-02-24 20:05 -0500

csiph-web