Horrible speeds using a purchased COM object. Is there a better way?

• • Reply

  14 years ago

  by Trilarian

  Trilarian

  New Orleans, United States

  Joined 15 years ago

  Aside from coding the COM object myself, does anyone have any suggestions?  I've only been allotted a small amount of time to script this COM object so am stuck using it.  The object is ActiveCase 3.5 from this site, and it basically takes a string in and bounces it against tables to style and punctuate names. 
  
  So my approach has been to connect to the DBF(I have to stick with a flat DBF format) using ADODB, start at the top, and scan to the bottom feeding the COM object one name at a time.  The company that sells the COM object claims their COM object should work very fast(or atleast better than my results), so my question is do you see anything in my code below that could be slowing it down other than the COM itself being slow?  The code took about 20 seconds to propigate a 500 record database, and I killed the code at 20 minutes when trying a 100,000 record database.  The average database this will be used on is between 70,000 - 200,000 records.
  
  Thanks for any input.
  
  
  
  Private Sub Style(FileName As String)
      Dim ActiveCase As ActiveCase.Convert
      Dim conRecordset As New ADODB.Connection
      Dim rsRecordset As New ADODB.Recordset
      Dim strSQL As String
      
      Set ActiveCase = New ActiveCase.Convert
      ActiveCase.StaticKeyName = "xxx"
      ActiveCase.StaticKey = "xxx"
      Set conRecordset = New ADODB.Connection
      Set rsRecordset = New ADODB.Recordset
      strSQL = "SELECT * FROM " & FileName
  
      conRecordset.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & JobListArray(z).DataPath & ";Extended Properties=DBase IV"
             
      rsRecordset.CursorLocation = adUseClient
      rsRecordset.Open strSQL, conRecordset, adOpenKeyset, adLockOptimistic
      rsRecordset.MoveFirst
              
      On Error GoTo TransError
  
      conRecordset.BeginTrans
  
      While Not rsRecordset.EOF
          ActiveCase.TextIn = rsRecordset!Name
          ActiveCase.TextStyle = "Name"
          ActiveCase.Convert
          If ActiveCase.ReturnCode <> "" Then
              Select Case ActiveCase.ReturnCode
                  Case "R35": MsgBox "Reference File Not Found", vbCritical, "ActiveCase - Sample"
                  Case "T00": MsgBox "Case Table Limit: 16,384", vbCritical, "ActiveCase - Sample"
                  Case "L00": MsgBox "Demo Expired", vbCritical, "ActiveCase - Sample"
                  Case "L01": MsgBox "Static Key Validation Failed", vbCritical, "ActiveCase - Sample"
                  Case "L50" To "L69": MsgBox "ActiveCase.ReturnCode: " & ActiveCase.ReturnCode & vbCrLf & "License Validation Failed", vbCritical, "ActiveCase - Sample"
                  Case Else: MsgBox "ActiveCase.ReturnCode: " & ActiveCase.ReturnCode, vbCritical, "ActiveCase - Sample"
              End Select
          End If
          rsRecordset!Name = ActiveCase.TextOut
          rsRecordset.Update
          rsRecordset.MoveNext
      Wend
      
      conRecordset.CommitTrans
  
      rsRecordset.Close
      Set rsRecordset = Nothing
      conRecordset.Close
      Set conRecordset = Nothing
      Set ActiveCase = Nothing
  
      Exit Sub
  
  TransError:
      conRecordset.RollbackTrans
      Set rsRecordset = Nothing
      conRecordset.Close
      Set conRecordset = Nothing
      Set ActiveCase = Nothing
      MsgBox (Err.Description & vbCrLf & "Above error occurred while trying to Stylist your file." & vbCrLf & " >> " & FileName & " <<")
      End
  End Sub
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  14 years ago

  by Trilarian

  Trilarian

  New Orleans, United States

  Joined 15 years ago

  Update: I have been successful in speeding up the code using FoxPro, so the slow down is defiantly in my VB script and not the COM object.  I have a temporary solution of making a EXE from FoxPro that references the DLL to be called from VB(going to convert to a DLL when I get a chance), but I wouldn't mind knowing what was slowing down the VB script if anyone sees anything.  The FoxPro is below for those that care.

  
  
  PARAMETERS indir, infile
  
  CLOSE ALL
  SET SAFETY OFF
  SET CPDIALOG OFF
  CLEAR
  
  objStyle = CREATEOBJECT("ActiveCase.Convert")
  objStyle.Static_Key_Name = "xxx"
  objStyle.Static_Key = "xxx"
  objStyle.Text_Style = "Name"
  
  CD &indir
  USE &infile
      GOTO top
          DO WHILE .not. EOF()
          objStyle.Text_In = NAME
          objStyle.Convert
          replace NAME WITH objStyle.Text_Out
          SKIP
      ENDDO
      objStyle.Clear
  USE
  
  SET SAFETY ON
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  Report abuse