· Oct 16, 2023

ZEN Reports orderby vs ORDER BY

Our application has a ZEN report that accepts a sorting parameter and a fundraiser parameter.  Sorting tells us which order the pages are to be in and the fundraiser limits the data shown  (e.g. which user has requested the report)

/// the passed in fundraiser parameter
Property fundraiser As %ZEN.Datatype.string(ZENURL = "FID");

Property SortOrder As %ZEN.Datatype.string(ZENURL = "SORTME") [ InitialExpression = "WorkId" ];

/// This XML defines the logical contents of this report.
XData ReportDefinition [ XMLNamespace = "]
<report xmlns=""
sql='SELECT WorkOrder->ID, WorkOrder->WorkId, WorkOrder->SalesRepName, WorkOrder->SalesRepLastName, WorkOrder->PhoneNumber, WorkOrder->TeamName, Customer, CustPhone, ItemId, ItemDesc, ItemQty
FROM FMS.WorkOrder_LineItems 
orderby="!..SortOrder" >
 <parameter expression = '..fundraiser/>

The sorting option is either SalesRepLastName  OR    TeamName,SalesRepLastName

For many years, this report has run without issue.  This fall, one of the larger (but not the largest data groups) reported an issue with the report failing.  If the report was run sorted by SalesRepLastName, the resulting pdf was returned in in less than 30 seconds and all was well.  If the report was run sorted by TeamName,SalesRepLastName the report fails.  (I can run it in a terminal session using GenerateReport and have determined it takes 137 seconds to complete).

My first thought was the size of the data was too much but then I discovered the largest data group runs either sort option successfully in less than 30 seconds. So it's not the amount of data.

A review of the TeamName data shows nothing unusual - no control characters, no extended ascii characters.  just a-z, 0-9 and spaces.

I have been unable to find a way to test/debug/understand exactly how the "orderby" option varies from using ORDER BY in the sql statement (other than some generic explanation in the documentation that "orderby" overrides "ORDER BY"

Anyone have any insight on how to trace what is really going on and where the report might be getting slowed down?

Product version: Caché 2017.1
$ZV: Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2017.2 (Build 744U) Fri Sep 29 2017 11:06:08 EDT
Discussion (4)2
Log in or sign up to continue