hexagon logo
Register Sign in
  • Replies 21 replies
  • Subscribers 33 subscribers
  • Views 460 views
  • Users 0 members are here
Options
Related

Why is writing to PC-DMIS from VBA so slow?

DaSalo
I have two scripts that do very similar things : Read scan point data from PC-DMIS and then write scan point data back into PC-DMIS. One version of the script is written and executed in PC-DMIS basic. The other is written in Excel VBA and is executed through Excel by calling the macro from a very simple PC-DMIS script.

The PC-DMIS only version writes the scan data out to a temp file and then reads it back out of the file and writes to PC-DMIS. The excel version reads the data out into memory in a 2D array and then writes it back into PC-DMIS from the array, no external files involved.

I figured the Excel version would be faster since everything is in memory. It turns out that it is wicked slow and takes about 11 seconds to write a 64 point scan while the PC-DMIS version does it in less than 1 second. All the time is in the writing via the Puttext method. Reading from PC-DMIS into the array takes a fraction of a second but writing it back out into PC-DMIS takes 11 seconds. In the PC-DMIS only version both operations take a fraction of a second.

Is VBA just inherently slow for this kind of thing? Anyone else experienced this?
  • I've had up to an 8-fold increase in Excel VBA execution speed by using the Application.Visible property. Try setting it to FALSE before your main crunch then to TRUE when done. The PcDmis application window will minimize while FALSE. Thinking that updating the Edit Window was the slowdown I also tried EditWindow.Visible = (True/False) but didn't realize near the same speed gain as with Application.Visible. I'm sure the results can vary depending on what you are doing but in my cases it's become a go-to policy when I don't need to watch PcDmis update while the code is running.
  • Are you suggesting that apply application.visible to the PC-DMIS application object, to the Excel application object, or both? I already have been applying it to the excel object and the speed differential that I note above is with this property set to False. The speed difference is staggering. With a 500 pnt scan it is the difference between a finger snap or get up and get yourself a cup of coffee while you wait. Could there be some virus protection or something of that sort that is getting in the way of outgoing data from VBA? I don't feel like there is anything about this code that should be the least bit computationally intensive. It must be something to do with the interaction between VBA and PC-DMIS. For some reason VBA can pull data without interference but can't push data at the same rate.

    Here is the code that I am using to call the macro from PC-DMIS: 

    Sub Main(Cmd_ID As String, Rtrn_ID As String)
      Dim xl As Object
      Dim xlBook As Object      
      Dim sPath As String

      sPath = "C:\BLADERUNNER\Scripts\"
      Set xl = CreateObject("Excel.application")
      Set xlBook = xl.Workbooks.Open(sPath & "Scan_Utilities.xlsm", 0, True)      
       xl.Application.Visible = False
       xl.run "Module1.CheckFunct", Cmd_ID, Rtrn_ID     
       xl.Quit

      Set xlBook = Nothing
      Set xl = Nothing
End Sub


    And here is the function that is deathly slow in VBA : 


    Function WriteScanFromArray(ByRef Cmd As Object, ByRef ScanArry As Variant) As Boolean

Dim i As Integer
Dim Retval As Boolean

WriteScanFromArray = False

'Verify that target command is a basic scan
    If Not AttachBasicScan(Cmd) Then
        Exit Function
    End If

'Check that Array is allocated and 2D
    If IsArrayAllocated(ScanArry) Then
        If NumberOfArrayDimensions(ScanArry) <> 2 Then
            MsgBox "Array is not 2D!", 0, "Error!"
            Exit Function
        End If
    Else
        MsgBox "Array not allocated!", 0, "Error!"
        Exit Function
    End If
    
'Check Upper and Lower bounds of Array
    If UBound(ScanArry, 2) <> 9 Then
        MsgBox "Array must have 10 values per record!", 0, "Error!"
        Exit Function
    End If
    If LBound(ScanArry, 2) <> 0 Then
        MsgBox "Array must use 0 for lower boundry!", 0, "Error!"
        Exit Function
    End If

'Write array into PC-DMIS feature.
    For i = LBound(ScanArry, 1) To UBound(ScanArry, 1)
        Retval = Cmd.PutText(CStr(UBound(ScanArry, 1) + 1), N_HITS, 0)
        Retval = Cmd.PutText(CStr(ScanArry(i, 0)), THEO_X, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 1)), THEO_Y, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 2)), THEO_Z, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 3)), THEO_I, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 4)), THEO_J, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 5)), THEO_K, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 6)), MEAS_X, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 7)), MEAS_Y, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 8)), MEAS_Z, i + 1)
        Retval = Cmd.PutText(CStr(ScanArry(i, 9)), T_VALUE, i + 1)
    Next i

WriteScanFromArray = True
End Function
  • I meant to apply the Application.Visible property to the PcDmis object.

    The slowdown may be due to using a generic object in the function for the passed command. You could try the following and see if it helps any... 

    Function WriteScanFromArray(ByRef Cmd As [COLOR="#FF0000"]PCDLRN.Command[/COLOR], ByRef ScanArry As Variant) As Boolean


    You will need the reference to Pcdlrn.tlb (PC_Dmis <version> Object Library).

    I have a VBA routine that extracts all data from all commands in a program(features, hits, alignment matrices, probes, legacy dims, FCF dims). Basically everything except derived coordinates for CLEARPLANE moves. It takes 3.7 seconds for a program with 1836 commands using Application.Visible = FALSE. Application.Visible=TRUE takes 33 seconds. This is on a box running @ 4.4 GHz. I haven't tried invoking code as you have in your example so cant predict what your results might be. Hope it helps.
  • You nailed it. Setting app.visible to false for the PC-DMIS App object cut the run time to less than a second : at least a 10x improvement. I find it a bit annoying to see the PCDMIS window flashing on and off but seems an acceptable price to pay. Perhaps I'll set excel to visible and use a userform to create a status window that can keep the focus.

    Thanks very much for your help. I never would have stumbled on that on my own.
  • Glad it worked for you. Just another case of try this and settle for that. I've learned a lot from these forums and enjoy passing along the little I've figured out.
  • you may get away with just making the edit window invisible since I bet most of the time is updating it.
  • The point that I don't understand is why I don't see this speed difference when running very similar code in the Pcdmis scripting environment. Setting app.visible to false in VBA gave me the same speed as running with app visible in the pcdmis environment. What is the difference between Pcdmis scripting and VBA scripting other than the details of the language syntax?
  • My guess would be pcdmis knows it's running a basic script and suppresses some things while it executes. It does not know what an external exe is doing. You don't mention how it is being used in the part program.
  • I am just writing a collection of utilities that I can use to manipulate scans. I am testing the foundation of this right now on a simple offline program that contains two scans and nothing much else. The script reads the point data from one and then writes it to the other during program execution. That's all.

    Pcdmis is running a script during this time. It is running the script that calls Excel. So it isn't a difference of running a script in pcdmis vs not, it is a difference of which environment is calling which command. Perhaps it is the difference of creating the pcdmis app object through VBA instead of through pcdmis that causes the difference.

    Anyhow, it's working great now so I'm not going to lose any more sleep over it.
  • I think the difference could possibly be due to just using the COM object in VBA. PcDmis script has an inside track.

    Command.FeatCmd.SetHit could combine 3 separate Command.PutText calls into one. Incorporating a With/End With block would also cut down on the overhead associated with accessing objects. 

    dim oFeatCmd as PCDLRN.FeatCmd
Set oFeatCmd = Cmd.FeatureCommand

With Cmd.oFeatCmd
	For i = LBound(ScanArry, 1) To UBound(ScanArry, 1)
		.SetHit(i, FHITDATA_CENTROID, FDATA_THEO, CStr(ScanArry(i, 0)), CStr(ScanArry(i, 1)), CStr(ScanArry(i, 2)))
		.SetHit(i, FHITDATA_VECTOR, FDATA_THEO, CStr(ScanArry(i, 3)), CStr(ScanArry(i, 4)), CStr(ScanArry(i, 5)))
		.SetHit(i, FHITDATA_CENTROID, FDATA_MEAS, CStr(ScanArry(i, 6)), CStr(ScanArry(i, 7)), CStr(ScanArry(i, 8)))
		Retval = Cmd.PutText(CStr(ScanArry(i, 9)), T_VALUE, i + 1)
	Next i
End With


    I would try to avoid using the Variant array if possible by passing .NumHits to the function and dimming ScanArry as Single, Double or String. Using generic objects and variants creates some additional overhead while determining what type of object or datatype is being dealt with. It might also allow removing the CStr cast.

    A search for VBA MicroTimer yields a good way to time execution speed in milliseconds. It uses API calls but helps with short segments of code. Most of the time I don't use it and just move on to the next task. Sometimes it will unveil a technique that can improve everything you do thereafter.
© 2024 Hexagon AB and/or its subsidiaries. Privacy Policy | Cloud Services Agreement