| || |
Speed to load data into QTP
QTP 10, 11, or 12.
I have thousands of pieces of data that I want to load into memory at start up. I want it all in an array or some type of object.
At this time I'm going from MS Excel to a QTP array.
It takes about 30 seconds to do the load.
What is a faster combination? How about XML to an array or some other container.
I expect some feedback asking why I want to load at the start and why I cannot just get what I need as it is required. The design is already in place. Now I want to find the best way to do this.
Would a connection to a database, Access, XML file be faster than loading from MS Excel?
if you have thousands of records, loading it all into an array in memory might slow the tests down by causing memory paging.
Originally Posted by bklabel1
You'll probably want to load it into a high speed database to make it easy to query and fetch the data. Another option is to use ODBC driver for Excel that allows Windows to access Excel as a Database with remarkable speed.
Connecting excel as database would be an good and speedy approach, I am using this approach for one of my framework.
I'm not too worried about speed except at the point where I am stuffing the array one time. I remember seeing an option someplace in QTP settings where it had a fast mode. QTP ignores breakpoints. I don't know how much performance improvement this gives me. I want to look into seeing if there is a way to toggle this during run time through the QTP Object Model. I would like to shut off debug when loading the array.
The idea of working with Excel as a data base is great.
The database idea is great. I have data that I need to massage one time at the start and get it into an array. I may be able to do it with Excel, but I'd rather do the work upfront.
While on the topic, would you know if fetching a cell using the Excel Data Base approach is faster than DataTable("Column", "table") style?
Also how does it compare in performance to the DOM code to connect to the workbook, make a sheet object. Start getting values.
Connecting to excel as a database is a lot faster than doing anything with the datatable or connecting to the excel API. The downside I've found is that excel likes to monkey with your data. Any time I changed a field, I had troubles with that field or possibly with any other field that excel decided looked like some other type of data. I had to give up that approach because I just couldn't get excel to leave things alone.
If you're looking to improve the speed of loading from a file, you might try a csv (or some other delimited text file). You can open and edit it in excel and QTP can read it very quickly. I usually read the entire file at once into a string, then split that string on vbcrlf to get an array of rows, then split each row on "," to get each field. I like arrays of dictionaries, so I usually take the first row as keys for the dictionary and all the other rows as data.
Feel free to try it out...
NOTE: I could probably speed this up a bit by only doing the ReDim of the returned array once before I loop through the data rows but I haven't wanted to fix what was already working. I've got plenty of things that aren't working to worry about.
'Input: sFile -- the path and filename of the file to load
' sFieldDelimiter -- delimiting character(s) between fields
' sLineDelimiter -- delimiting character(s) between lines
'Output: Reads the contents of a file into an array of dictionaries
'Created By: Dennis Belew
Public Function gblDelimitedFileToArrayDict(sFile, sFieldDelimiter, sLineDelimiter)
sFileContents = gblFileLoad(sFile) ' this is a function that reads the full contents of a text file into a string using FSO. null if file doesn't exist.
If isnull(sFileContents) Then
gblInsertReportItem "FAIL", "Delimited File to Array of Dictionaries -- File did not exist", "Delimited File to Array of Dictionaries -- File did not exist"
gblDelimitedFile = array()
arrReturn = Array()
arrFileLines = split(sFileContents, sLineDelimiter)
arrHeaders = split(arrFileLines(0), sFieldDelimiter)
For iRow = 1 to ubound(arrFileLines)
Set dRow = CreateObject("Scripting.Dictionary")
arrRow = split(arrFileLines(iRow), sFieldDelimiter)
For iField = 0 to ubound(arrRow)
dRow(arrHeaders(iField)) = arrRow(iField)
If ubound(arrReturn) = -1 Then
ReDim Preserve arrReturn(ubound(arrReturn)+1)
Set arrReturn(ubound(arrReturn)) = dRow
gblDelimitedFileToArrayDict = arrReturn
I appreciate you providing the code example.
I have some questions.
Have you ever tried having Excel programmatically export the file as csv?
What does the line:
arrReturn = Array()
Kevin, certainly data base approach is much faster than datatable approach. Example we used excel for test scripting while importing excel and executing using QTP datatable encountered with performance issue, later we used database approach resulted in better performance.
For one scenario execution time 5 mins(datatable approach) same scenario executes in 3-4 mins(database approach).
Not required to import the excel and iterate all rows.
The disadvantage we faced in database approach is it cannot support excel formula. Another concern here is need to be precise with your datatype.
I have, in fact, had excel write things out to CSV from an XLS file. It's just a matter of changing a parameter on the save function from excel. Example, where xl is an instance of Excel object:
In my example code in the post above, arrReturn = Array() assigns arrReturn to an empty array (an array with no elements). I later ReDim it to whatever size I need. I ran into some issues when i just tried Dim arrReturn(). I may never understand how vbscript deals with arrays.
xl.SaveAs filepath & filename, 6, , , , , ,1