Recent posts:
Recent replies:

You can look at the contents of zenutils.js to see the actual details of the zen(id) function.

The IDX system is oftentimes partitioned by Group(GRP).   Additionally I suspect the 86M records do not represent invoices for a single year.  Using %SYSTEM.WorkMgr you could break the job up in to smaller jobs by GRP and or InvCrePd or YEAR(BAR.Invoice.SerDt) 

Some of the reasons why I focus on utilizing class queries include

  • Studio and other editors are much better at providing coloring/syntax checking vs a strategy of setting a tSQL string as some arbitrary SQL statement
  • As mentioned in the original post it provides the separation that allows for easy re-use and testing.  If I have a class query I can decide to allow it to be reflected as a stored procedure, I can expose it to ZEN reports, ODBC/JDBC, JSON projection, %XML.DataSet, I can use it to satisfy a control in ZEN that allows for binding to a class query,  as well as others.  Basically, it provides for great separation.
  • I also like the idea of considering writing the statement using SQL as %Query.  If for some reason I run into performance issues that I cannot overcome I can change the implementation to %SQLQuery and implement COS code, the calling application would see the improved performance but does not have to understand the internal implementation.  However, I've only done this on extremely rare occasions.
  • I think one of the most important aspects of SQL to always read the query plan, it's there where you can understand what the optimizer is going to do.  I care all about the query plan and almost always validate what it reports.  Having a class query allows for easy Show Plan examination whereas it's generally hard to do if you take the approach of setting a tSQL string(sometimes with concatenation over several lines).

Class Queries are really worth investing in IMHO.

Followers:
Following:
Stephen has not followed anybody yet.
Global Masters badges: