QTP Sort

[MattO1]

In a message dated 4/28/98 9:21:29 AM Central Daylight Time,
kaffenbe@kci.wayne.edu writes:

<< I need to sort on multiple data items in order to eliminate duplicate
 records.  From an efficiency standpoint, is it better to sort on the
 data items separately or is it better to concatenate them into 1 big
 field and then sort?  Or does it really matter?
  >>
Hi  Carol,
As David Maclary stated, for efficiency, one big item or presorting in Quiz
will be faster for the sort, but it may not be faster for the overall
processing.  I you presort in Quiz, you will of course have to run an
additional process. If you concatenate the items together in QTP and sort on
the single item, the sort will process faster, but you will only have the
lowest level control break available to you.

For sort performance in both Quiz and QTP the major concern is not usually the
number of sort items, but the length of each transaction record and the number
of transactions being sorted.  If you are linking several files together, try
accessing just the primary, "driver"  file and writing out just the required
items out to a subfile and sorting them first.  By "required items" I mean the
items you need to sort on and the key values you will use to link back to the
other file(s) in your original Access statement.  Whatever file has these
required items is your "driver" file.

Then, on a second pass, link from the sorted subfile to the other files in
your original Access statement; and tell QTP that your transaction set is
SORTED on the items you chose.
For example:
(original)
Access Order_Header Link to Order_Detail
Sort on Region_Code, Customer_ID, Order_Date
Output Reg_Cust_Sales ADD AT Order_Date
  Item  Total_Cust_Daily_Sales  Final Subtotal Line_Amt of Order_Detail

(faster)
Request A
Access Order_Header
Sort on Region_Code, Customer_ID, Order_Date
Subfile SubSorted  Include Region_Code_Customer_ID, Order_Date, Order_Number

Request B
Access *SubSorted Link Order_Number of SubSorted &
         to Order_Number of Order_Detail
SORTED on Region_Code, Customer_ID, Order_Date
Output Reg_Cust_Sales ADD AT Order_Date
  Item  Total_Cust_Daily_Sales  Final Subtotal Line_Amt of Order_Detail

In a large number of cases, this will give you significant performance gains.
I has the usual effect of creating shorter transactions (since you send fewer
items out to the subfile) and fewer transactions in the first pass (as long as
there was at least one one-to-many linkage in the original Access statement).

Hope that helps,
Matt 8-)

Matt Ohmes
Matt.Ohmes@Cognos.Com
MattO1@AOL.COM (that's "Oh" 1, not "Zero" 1)

Cognos Corp.
909 E. Las Colinas Blvd.
Suite 1900
Irving, TX  75039
(972) 556-1122

"The views of Matt do not represent the opinions of Cognos in any way.  In
fact we refuse to admit he even works for Cognos. Don't trust him and never
loan him money!"
= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =
Subscribe: "subscribe powerh-l" in message body to majordomo@lists.swau.edu
Unsubscribe: "unsubscribe powerh-l" in message to majordomo@lists.swau.edu
powerh-l@lists.swau.edu is gatewayed one-way to bit.listserv.powerh-l
This list is closed, thus to post to the list, you must be a subscriber.