Excel Data transfer performance

Add-in Express™ Support Service
That's what is more important than anything else

Excel Data transfer performance
 
Charles Williams




Posts: 3
 Joined: 2009-06-29
Hi,
I am benchmarking various technologies for excel UDFs

So I am looking for the fastest way of transferring data from Excel to an Addin Express XLL or Automation addin.
Here is my xll function: at the moment this is about the same speed as a VBA XLA and a lot slower than a c++ XLL+ or a VB6 Automation addin)
(VBA 109 Addin Xpress Vb.Net XLL 100 c++ Xll+ 37 VB6 66)
(VS2005 Excel 2003)

How do I make it perform faster? 


Public Shared Function AverageTolVBXLL(ByVal theRange As Object, ByVal dTol As Double) As Object
            Dim xlErrNA As Object

            Dim vArr As Object(,)
            Dim v As Object
            Dim d As Double
            Dim r As Double
            Dim j As Integer
            Dim k As Integer
            Dim Res As Object
            Dim lCount As Integer
            On Error GoTo FuncFail
            '

            'vArr = cType(theRange.Value2, Object(,))
            'vArr = theRange.GetValue
            vArr = theRange
            'Dim objArray As Object(,)
            'objArray = CType(rng.Value, Object(,)) ' <-- This is the key!
            '


            For j = 0 To UBound(vArr, 1)
                For k = 0 To UBound(vArr, 2)

                    d = CDbl(vArr(j, k))
                    If System.Math.Abs(d) > dTol Then
                        r = r + d
                        lCount = lCount + 1
                    End If
skip:
                Next k
            Next j
            Res = r / lCount
            Return Res
            Exit Function

FuncFail:
            'AverageTolVBCom = CVErr(xlErrNA)
        End Function
Posted 29 Jun, 2009 19:58:37 Top
Andrei Smolin


Add-in Express team


Posts: 19138
 Joined: 2006-05-11
Hi Charles,

How do you measure the time?


Andrei Smolin
Add-in Express Team Leader
Posted 30 Jun, 2009 12:34:13 Top
Charles Williams




Posts: 3
 Joined: 2009-06-29
Hi Andrei,

I used Windows API high-res timer QueryPerformanceCounter to get the median of 5 trials using Range.calculate on 10 Function calls on a range of 32000 cells. This provides reasonably accurate and repeatable timings.

Charles
http://www.DecisionModels.com
Posted 30 Jun, 2009 12:55:31 Top
Andrei Smolin


Add-in Express team


Posts: 19138
 Joined: 2006-05-11
Hi Charles,

Here is when an XLL add-in developed in .NET loses time:

- when the control is passed from an unmanaged code to the managed one
- when data are marshaled between the managed and the unmanaged parts (in your case, 32 000 cell values are marshaled one by one)
- when your .NET code is executed (it's usually slower than an unmanaged
code)

I believe that your code can't be further optimized. You can try AcceptAsArray = False for theRange. In this case, the parameter returns an ADXExcelRef, try using ADXExcelRef.GetValue.
Sorry, I have never tested this so I don't know what the results will be.


Andrei Smolin
Add-in Express Team Leader
Posted 01 Jul, 2009 13:34:01 Top
Charles Williams




Posts: 3
 Joined: 2009-06-29
Hi Andrei,

Thanks: interesting to get a reference instead of an array, but the execution speed is basically the same or very slightly slower (103 millisecs vs 100 millisecs): I guess its using the same code under the covers.

I also get a higher overhead per UDF call than with VBA or VB6.

Looks like managed code UDFs have a bit of a performance overhead compared to other Excel UDf solutions

Posted 02 Jul, 2009 09:05:14 Top