[AccessD] Array faster in DLL?

Gustav Brock Gustav at cactus.dk
Tue Nov 1 04:19:21 CST 2005


Hi all

Well, Shamil did the dll in C++ and the result is amazing.

A simple function runs the dll like this and returns the execution time in seconds:

Public Function IArrayTimeLocal_ArrayTimeLocal( _
  ByVal vlngSequences As Long, _
  ByVal lngLoopMax As Long, _
  strTotalCnt As String) As Long

  Dim obj As Object ' ArrayCruncherLib.Test
  
  Set obj = CreateObject("ArrayCruncher.Test")
  IArrayTimeLocal_ArrayTimeLocal = obj.ArrayTimeLocal(vlngSequences, lngLoopMax, strTotalCnt)
  Set obj = Nothing

End Function

It is essential the same as those functions used to test clean VBA and the DLL made with PowerBasic or FreeBASIC, but with an added multiplier, vlngSequences, to prevent too large values for lngLoopMax.

For comparison I ran the test like this:

? IArrayTimeLocal_ArrayTimeLocal(1, 10 ^ 8, a$)

This runs on my machine in 20 seconds!
To summarize, this means that the result table for 10^6 loops now is (with converted time for PowerBasic from Stuart):

  ArrayTimeLocal: 34 seconds
  ArrayTimeDLL: 3.4 seconds (PowerBasic dll measured with 10^7 loops)
  ArrayTimeDLL: 2.2 seconds (FreeBASIC dll measured with 10^7 loops)
  IArrayTimeLocal_ArrayTimeLocal: 0.2 seconds (measured with 10^8 loops)

Or to express it as speed improvement compared to VBA:

  VBA: 1:1
  PowerBasic dll: 10:1
  FreeBASIC dll: 15.5:1
  C++ dll: 170:1

and compared to the FreeBASIC dll:

  C++ dll: 11:1

I knew C++ was fast but not this fast. I doubt you can program this in Assembler to run faster without a very deep understanding of both Assembler and how to optimize code for different Intel processors and even though it would take days if not weeks to program.

Seems like if you do applications where some routines do millions of iterations, you better team up with a decent C++ programmer if not an expert like Shamil and move those functions and classes to external libraries.

/gustav


>>> Gustav at cactus.dk 30-10-2005 21:16 >>>
Hi Marty, Shamil, Fred et all

Well, I did a small and quite surprising test inspired by Shamil.
Here is a test routine which can be seen as typical for what I do.
The array is not large but it is read from and written to a bunch of times.

Here is the local test using standard VBA:

Function ArrayTimeLocal(ByVal lngLoopMax As Long) As Long
    
  Const lngItems                      As Long = 100
  
  Dim alngTmp(1 To lngItems, 1 To 2)  As Long
  Dim lngLoop                         As Long
  Dim lngItem                         As Long
  Dim lngResult                       As Long
  Dim lngSeconds                      As Long
  Dim dblStart                        As Double
  Dim dblStop                         As Double

  dblStart = Timer
  
  For lngLoop = 1 To lngLoopMax
    For lngItem = 1 To lngItems
      alngTmp(lngItem, 1) = lngLoop * 10
      If alngTmp(lngItem, 1) / 10 = 100 Then
        lngResult = 1
      Else
        lngResult = 0
      End If
    Next
  Next
  
  dblStop = Timer
  lngSeconds = CLng(dblStop - dblStart)
    
  ArrayTimeLocal = lngSeconds
    
End Function

And this is the test function using an DLL containing nearly identical code:

Declare Function ArrayTime Lib "arraydll.dll" Alias "ArrayTime at 4" (ByVal lngLoops As Long) As Long

Function ArrayTimeDLL(ByVal lngLoopMax As Long) As Long

  ArrayTimeDLL = ArrayTime(lngLoopMax)

End Function

The DLL was programmed and compiled in FreeBASIC and the FBIde which I earlier posted links for.

Setting lngLoopMax to 10^6 returns these running times for the two functions:

  ArrayTimeLocal: 34 seconds
  ArrayTimeDLL: 2.2 seconds (measured with 10^7 loops)

Thus this simple tool gives a speed improvement of 15.5 to 1 ... not bad!

Nothing comes free, and the trouble is - as far as I can see - that the dll has to be programmed to run rock stable. If it errors out, Access halts or simply quits - no errors, no GPF, just poof away.

With this result I think I'll leave the assembler stuff for now. Still, if anyone with PowerBasic or other compiler could make similar tests and publish the results, we could rank these.

/gustav




More information about the AccessD mailing list