Written by

Sr. Consultant at PICK Programmer's Shop (fundraiseIT.org)
Question Mindy Caldwell · 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 = "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?

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

Comments

Stephen Canzano · Oct 18, 2023

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.

0
Vic Sun · Oct 19, 2023

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.

0
Mindy Caldwell  Oct 21 to Vic Sun

This answer finally made sense to me and I was able to fix a long overdue intermittent bug in our reports.  Belated Thanks!

0
David Hockenbroch · Oct 19, 2023

What indices are defined for your data, and have you tried rebuilding them?

0
Mindy Caldwell  Oct 22, 2023 to David Hockenbroch

No indices so nothing to rebuild.

0