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)
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 = "http://www.intersystems.com/zen/report/definition" ]
{
<report xmlns="http://www.intersystems.com/zen/report/definition"
name="OrderSlip"
sql='SELECT WorkOrder->ID, WorkOrder->WorkId, WorkOrder->SalesRepName, WorkOrder->SalesRepLastName, WorkOrder->PhoneNumber, WorkOrder->TeamName, Customer, CustPhone, ItemId, ItemDesc, ItemQty
FROM FMS.WorkOrder_LineItems
WHERE WorkOrder->WorkId %STARTSWITH ? '
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?
Not sure of your specific question but I have always considered having ReportDefinition not sort the data but instead implement ordering as part of the ReportDisplay when using a <table>. orderby in a table performs an xsl sort and feel like it's the better place to perform this so that I'm not asking the database to perform the ordering.
For posterity, the solution WRC proposed was to implement a CreateRS method allowing for the sort order to be modified by changing the SQL query.
What indices are defined for your data, and have you tried rebuilding them?
No indices so nothing to rebuild.