QTP datatable operations *extremely* slow (much better under MMDRV batch executor)?

Possibly a smashing story -- QTP seems to waste our worktime for no reason:

Consider this script, having a datatable of exactly one global row with 26 columns named "A" to "Z" filled with any value:

Print "Started"
Services.StartTransaction "Simpletest"
Set G=DataTable.GetSheet ("Global")
For J=1 to 26   
    For I=1 to 100
        Set P=G.GetParameter (Chr (J+64))
        If P.Value = "Hi" Then
        End If
    Next
Next
Services.EndTransaction "Simpletest"
Print "Ended"

Executing this under QTP 10 takes 15.1 s开发者_C百科econds on my blaster. (Animated run is off, of course.)

Now I execute this using mmdrv.exe from QTP's bin folder, giving it the parameter "-usr ''" with being the full name including path to the QTP test .usr file.

That takes 0.07 seconds.

Hello? That's a 215-fold performance boost, but identical functionality. How comes?

I am digging around here since we do some exotic stuff with QTP data tables, and face serious performance problems under QTP. I believe to have tracked down the cause to the DataTable.GetSheet and DTSheet.GetParameter properties/methods.

Now that I see that the MMDRV, which is for executing QTP tests from within LoadRunner scenarios, does not have that performance penalty, I wonder about the following:

• Is there a 1:1 alternative for accessing xls files?
• Shouldn't somebody at Ex-Mercury/HP notice that data table access under QTP is very inefficient, as MMDRV.EXE demonstrates, and do something about it?
• As far as I can see, all other QTP functionality is of comparable speed under MMDRV and QTP. Can anybody acknowledge that? *Does anybody else know about this?

Thanks for any replies, no matter how disturbing they might be.

* UPDATE * Executing with QTP invisible takes 1.54 seconds. That's a 10-fold improvement just by hiding QTP as outlined in one of the answers. Sigh.

We are having the same performance issues with QTP. After investigations, we cornered the problems in 2 areas.

• The Data Table (horrible performance)
• QTP is visible/invisible

We found that QTP runs 5-6 times faster when it's hidden

We made a small script to toggle QTP visibility while we develop/debug (because you can always force QTP to be hidden in the remote agent settings) 'This script is used to show/hide the QTP window 'QTP runs much faster when hidden

Dim qtApp
Set qtApp = CreateObject("QuickTest.Application")
qtApp.Launch            ' Start QuickTest
If qtApp.Visible = False Then  ' Make the QuickTest application invisible/visible
    qtApp.Visible = True
Else
    qtApp.Visible = False
End If

Would you kindly share the idea of caching the DataTable as we are thinking to develop the same mechanism and would benefit to see such an example.

Kind regards, Achraf

Running with a full GUI Development environment extracts a performance penalty. You can observe this difference in VUGEN in LoadRunner as well, with running at MDRV providing a substantial performance boost where complex code is used. You will also see people quite often complain that VUGEN is "slower than the actual application."

So, if this surprising to me? Not really. What is interesting is that I had not considered the existence of MDRV on the QTP install, but that makes since given the common QTP heritage with the TULIP technology which came out of QUICKTEST for Web. That tulip base has been the basis for QuicktestPro on the functional side and some of the newer web HTTP technology on the load side.

The 200-fold performance penalty comes from DataTable operations. Other operations still are slower under QTP than under MMDRV, but not with such a horror factor.

I worked around that by "caching" all DataTable calls in a custom structure (really an object collection). Building that one takes 5 seconds since I query lots of Sheets and parameter properties. Processing my structure instead of calling DTSheet and DTParameter properties is way faster, indeed fast enough.

I suspect under QTP all data table accesses are done through the custom Excel control they (HP) licensed from a third party, while under MMDRV they use code that is integrated more tightly, leading to less overhead per call.

Duke would say: "Hahaha, what a mess."

** Update ** Upon request, here is an outline of what I mean by "caching" DataTable calls. It's quite some code, so be prepared...

Here is that mess (sorry, don't have time to translate German inline comments right now) (and sorry for the formatting, I obviously cannot do much about it, maybe you want to cut and paste this in QTP's editor):

It all starts with a generic Container class in which I can store (and access by index) N object references:

' Container-Klasse, die N Objektreferenzen aufnehmen kann. 
Class TContainer
  Public iItems() ' Array, das die Objektreferenzen aufnimmt
  Private iItemsHaveUBound ' True, wenn das Array mindestens ein Element hat 

  ' Konstruktor
  Private Sub Class_Initialize 
    iItemsHaveUBound=false ' Kein Element in iItems vorhanden 
  End Sub

  ' Anzahl der enthaltenen Objektreferenzen?
  Public Property Get Count
    If iItemsHaveUBound Then ' Nur wenn > 0 Elemente enthalten sind (also mindestens einmal ReDim Preserve für iItems gelaufen ist),
      ' können wir UBound aufrufen. Macht keinen Sinn, ist aber so, ein UBound (E) liefert für ein frisches Private E() einen Subscript error...
      Count=UBound (iItems)+1 ' Grösstmöglicher Index+1, da Zählung bei 0 beginnt, und 0-basierender Index+1 = Abzahl
    else
      Count=0 ' Jungfräuliches iItems(), direkt 0 liefern
    End If
  End Property

  ' Getter für indizierte Referenz (Index ist 1-basierend!)
  Public Default Property Get Item (ByVal Index) 
    Set Item=iItems(Index-1)
  End Property

  ' Setter für indizierte Zuweisung (Index ist 1-basierend!)
  Public Property Set Item (ByVal Index, ByVal Val)
    ' MBLogDebugComment "SetItem","Index=" & Index 
    If Count <= (Index-1) Then
      ReDim Preserve iItems (Index-1)
      iItemsHaveUBound=true
    End If
    Set iItems(Index-1)=Val
  End Property

  Public Property Get AddItem (ByVal Val)
     Item(Count+1)=Val
     Set AddItem=Val
  End Property

End Class

I using special column names to give the columns special meanings. DetectColumnKind detects that meaning according to the name and spits out an "enum". This is it (I won't show DetectColumnKind here):

' Von MBCollectAllTestData unterstützte Spaltenarten in Datentabellen
Private Const ckData = 0
Private Const ckReference = 1 
Private Const ckComment = 2 

Now comes the real stuff:

A container holding N sheet representations. I collect each sheet's properties and store them in that container.

' Klassen, die die Tabellenbkattstrukturen repräsentieren. Hintergrund ist ein ganz abgefahrener: Der Kollektor muss sich die Spaltenstrukturen aller
' intensiv anschauen, um seinen Job zu machen (Verweise verstehen, Klassencode generieren, Zuweisungscode generieren). Dafür greift er wieder und wieder
' auf DTSheet- und DTParameter-Instanzen zu. Das ist performancemässig aber sehr, sehr teuer (warum auch immer!). Um erträgliche Laufzeiten zu erhalten,
' enumeriert der Kollektor im helper BuildTestDataDescr die Sheets und deren Spalten und merkt sich in eigenen Datenstrukturen alles, was er später
' über die Spalten so wissen muss. Anschliessend macht der Kollektor seinen Job anhand dieser Repräsentationen, nicht mehr anhand der 
' DataTable-Eigenschaften. Das ergibt funktional das gleiche, macht aber performancemässig einen Riesen-Unterschied.

' Klasse, die eine Tabellenblattspalte repräsentiert
Class TestDataColumnDescr 
    Public Column ' as DTParameter; Referenz auf die Original-Spalte
    Public ColumnName ' as String; der Name der Spalte
    Public ColumnKind ' fertig ausgerechnete Spaltenart in Sachen Kollektor
    Public ColumnRefdSheet ' as DTSheet; bei Verweisspalte: das verwiesene Sheet
    Public ColumnRefdSheetName ' as String; bei Verweisspalte: der Name des verwiesenen Sheets
    Public ColumnRefdSheetDescr ' as TestDataSheetDescr; bei Verweisspalte: Referenz auf den TestDataSheetDescr-Descriptor des verwiesenen Sheets
    Public ColumnRefdSheetIDColumn ' as DTParameter; bei Verweisspalte: Referenz auf die Original-ID-Spalte des verwiesenen Sheets
    Public ColumnRefdSheetPosColumn ' as DTParameter; bei Verweisspalte: Referenz auf die Original-Pos-Spalte des verwiesenen Sheets (Nothing, wenn 1:1)
    ' Konstruktor
  Private Sub Class_Initialize 
  End Sub
End Class

' Klasse, die ein Tabellenblatt repräsentiert
Class TestDataSheetDescr 
    Public Sheet ' as DTSheet; Referenz auf das Original-Sheet
    Public SheetName ' as String; Name des Sheets
    Public SheetRowCount ' as Integer; Anzahl Zeilen im Original-Sheet
    Public SheetColumnCount ' as Integer; Anzahl Spalten im Original-Sheet
    Public SheetColumn ' as TContainer; Container aller Spaltendescriptoren (TestDataColumnDescr)
  ' Konstruktor
  Private Sub Class_Initialize 
        Set SheetColumn=New TContainer
  End Sub
End Class

Here is the stuff for building up the container's contents:

' Container aller Tabellenblattrepräsentationen
Dim TestDataDescr ' wird in BuildTestDataDescr instanziiert

' Aufbau von Tabellenblattrepräsentationen, damit Kollektor nicht dauernd DataSheet-Funktionen aufrufen muss. TestDataDescr instanziieren, aufbauen.
Public Sub BuildTestDataDescr
    ' Build N Sheet Descriptors
    Dim SheetIndex
    Dim ColumnIndex
    Dim S
  Dim S1
  Dim S2
    Dim Index
    dim SheetDescr, ColumnDescr
    ' Zunächst die N Sheet-Descriptoren mit ihren Spaltendescriptoren anlegen

    'Services.StartTransaction "BuildTestDataDescr"
    Set TestDataDescr = New TContainer
    For SheetIndex=1 to DataTable.GetSheetCount
        set SheetDescr = New TestDataSheetDescr
        With TestDataDescr.AddItem (SheetDescr)
            Set .Sheet=DataTable.GetSheet (SheetIndex)
            .SheetName=.Sheet.Name
            .SheetRowCount=.Sheet.GetRowCount
            .SheetColumnCount=.Sheet.GetParameterCount 
            Set S=.Sheet ' .Sheet ist im folgenden With nicht erreichbar, keine Ahnung, warum (nested Withes funken nicht anscheinend)
            For ColumnIndex=1 to .SheetColumnCount
                set ColumnDescr = New TestDataColumnDescr
                With .SheetColumn.AddItem (ColumnDescr)
                    Set .Column=S.GetParameter (ColumnIndex)
                    .ColumnName=.Column.Name
                End With
            Next
        End With
    Next

    ' Jetzt etwaige Verweisspalten mit zugehöriger Info anreichern (wir machen das in einem zweiten Schritt, damit wir garantiert zu allen
    ' verwiesenen Blättern einen Descriptor finden -- ohne Rekursion und komplizierten Abbruchbedingungen bei zyklischen Verweisen...); ferner
    ' müssen die Namen von auswahltabellenbasierten Spalten angepasst werden:

    For SheetIndex=1 to TestDataDescr.Count
        With TestDataDescr(SheetIndex)
            For ColumnIndex=1 to .SheetColumnCount
                Set S=.Sheet ' .Sheet ist im folgenden With nicht erreichbar, keine Ahnung, warum (nested Withes funken nicht anscheinend)
                With .SheetColumn(ColumnIndex)
                    .ColumnKind=DetectColumnKind (.ColumnName,S1,S2)
                    Select Case .ColumnKind
                        Case ckComment
                            ' Nuttin', weil: Ist ja eine Gruppier- oder Kommentarspalte -- ignorieren
                        Case ckData
                            ' Datenspalte -- hier nichts weiter zu tun
                            .ColumnName=S1 ' ausser: Namen bereinigen (hat nur Folgen für auswahllistenbasierte Spalten)
                        Case ckReference 
                            ' Verweisspalte -- merken, was später immer wieder an info benötigt wird
                            .ColumnName=S1
                            Set .ColumnRefdSheet=MBFindSheet (S2)
                            If .ColumnRefdSheet is Nothing Then
                                MBErrorAbort "MBUtil.MBCollectAllTestData", _
                                    "Fehler beim Definieren von Klassen;" & vbNewline _
                                    & "Spalte '" & .ColumnName & "' definiert einen Verweis auf Datentabellenblatt '" & S2 & "', welches nicht existiert." & vbNewline _
                                    & "Bitte überprüfen Sie die entsprechenden Datentabellenblätter" 
                            End If
                            .ColumnRefdSheetName=.ColumnRefdSheet.Name
                            Set .ColumnRefdSheetIDColumn=.ColumnRefdSheet.GetParameter ("ID")
                            Set .ColumnRefdSheetPosColumn=MBFindColumn (.ColumnRefdSheet,"Pos")
                            For Index=1 to TestDataDescr.Count
                                If TestDataDescr(Index).SheetName = .ColumnRefdSheetName then
                                    Exit For
                                End If
                            Next
                            Set .ColumnRefdSheetDescr=TestDataDescr(Index)
                    End Select
                End With
            Next
        End With
    Next
    'Services.EndTransaction "BuildTestDataDescr"
End Sub

Based on the info in the container, I use the structures in TestDataDescr to iterate over columns, etc.

Like in this example, which receives one container element (SourceSheetDescr), looks at every column, and does "something" according to the column kind, which is part of the info built into the container element:

  For ParamIndex=1 to SourceSheetDescr.SheetColumnCount
        With SourceSheetDescr.SheetColumn(ParamIndex)
            Select Case .ColumnKind
                Case ckComment
                    ' Do something
                Case ckData
                    ' Do something else             Case ckReference 
                    ' Do other stuff                            End Select
        End With
  Next

This way I avoid having to query DTSheet.GetParameter (), and acoid to call any other DataTable methods. This, of course, is just one example of using the info the container holds.

In our typical usecases, this tripled performance in comparison to calling DataTable methods, and that even though we had already avoided all redundant calls, and did all other obvious optimizations in the traditional data table access code.

Data Table operations are very slow in QTP. So use Excel Formulas in your Datatable to speed up the operation.