I've run into something similar but with a different streamlet type.  The subscript error indicates that it is getting a null value which it expects to be non-null.  I would suggest looking at the medication streamlet and ensuring that all of the required fields are defined.  It might be the Medication.OrderItem.Code is null.

I cant speak for Templates but in the same area I do have Studio Add-Ins in use in VS Code.

I too am not entirely sure what your issue is but when I have particularly complex reports I leverage the ability to define in the ReportDefinition multiple <group> entities.  Consider for example you have a data model that has

• Batch
• Claim
• ClaimLine
• ClaimLineAdjudication

while you could define a single entity <group> which calls a single query, in my case I prefer to use Class Queries and then leverage breakOnField and create multiple <groups>, I find it makes more sense to have something like this, albeit this is psuedo code and not 100% what is entered in a Report Definition.

<group name="Batches" queryName="GetBatches">

<group name="Claims" queryName="ClaimsByBatch">

<parameter field="Batch">

<group name="Claim" 

<group name="Lines" queryName="LinesByClaim">

<parameter field="Claim">

<group name="Line">

<group name="LineAdjudications" queryName="AdjudicationByClaimLine">

<parameter field="Claim">

<parameter field="Line">

<group name="Line">

</group>

</group>

</group>

</group>

</group>

</group>

</group>

In this manner each level/group is responsible for doing one thing.  If needed I've also added to the <group> tag a filter element.  An actual example I have is this

fields="ClassName,LocalTimeChanged,RemoteTimeChanged,Description,LocalFkeys,RemoteFkeys,LocalIndices,RemoteIndices,LocalMethods,RemoteMethods,LocalProperties,RemoteProperties,LocalQueries,RemoteQueries,LocalParameters,RemoteParameters,LocalTriggers,RemoteTrigger" filter="..Filter(..FilterSpec)">

whereby I send to my method Filter the field values specified in the attribute fields as well as a FilterSpecification to test the filter.  You don't have to do it this way, its just an example of using the fields attribute and the filter attribute which is any valid objectscript expression.

Based on 

it looks like History.Views may not have indices or at least doesn't have a bitmap index.  If the table had a bitmap index it shouldn't take 30 seconds to count the rows. ... but maybe I'm incorrect.... seeing the query plan would have provided that insight.

You report that the now generates in 60 seconds and previously it 28 minutes for a twelve-page report.  Was the primary problem /time consumed in gathering the data or something else?

In the past, I have utilized %CSP.StreamServer to accomplish this.  While this may have a %CSP package name its perfectly usable in a ZEN context.

some thoughts on what you have presented.

1. It doesn't seem like the usedclassesdef.zip is available any longer

2. When examining a query plan I mostly do CONTROL-F and then search for the word looping. The statements that say "using the given ideky value" generally are not going to be of concern.  I see in your query plan it has "Read index map MSDS_Serenity.CustomeInstrument.InstIndx looping on Instrument and ID."  This seems like one area of concern.  This is telling us I believe that a table scan is being don on CustomerInstrument.  Should there be a join between Instrument and CustomerInstrument?  

3. I don't think it technically makes a difference in performance but I like to write my queries with a JOIN clause so that I have a clear understanding with the relationships between tables vs. conditions that would remove rows.  So I might write it like

 FROM MSDS_Serenity.Kit k

  JOIN  MSDS_Serenity.KitContent KC on Kc.Kitid=k.Id

 JOIN   MSDS_Serenity.Instrument I on I.Id=kc.Instrument

 JOIN   MSDS_Serenity.InstrumentGroup G on G.Id=I.GroupId

  JOIN  MSDS_Serenity.CustomInstrument CI on CI.    >>>maybe I'm incorrect but I don't see where the ci table is related to any other table, this is what is likely causing the table scan, if this table has a large number of rows this could very well be causing an issue

WHERE i.active=1 and (i.IsImpants is null or i.IsImplans!=1) 

    and k.loc=5 and k.id=9237

Again I don't think this makes any technical difference but it does allow me to isloate the relationship between tables vs the filtering that occurs due to a WHERE clause.

4. I'm not saying this is absolutely true but when I see DISTINCT in a query I wonder why it was added.  Is it because of a lack of join that was producing duplicate rows and DISTINCT was a convenient way to "hide" the logic flaw?  I could be completely off base here so please ignore if 'm completely out of line.  This is a good article on DISTINCT https://wiki.c2.com/?SelectDistinctIsaCodeSmell

5. Without knowing what the stored procedures are doing this could be a place where performance is encountering issues.
 

IRIS supports 

Create View

Create Table

Create Function

Create Query

At the same time if you have the proper IRIS license you can create analytics cubes within IRIS and as you say you can connect directly and leverage those analytics cubes.

I'm not sure when using %Library.ResultSet but there are several reasons you might want to consider using %SQL.Statement including with %SQL.Statement you can use the instance method %GetImplementationDetails which should provide you with what you are looking for.

and since others will view this we really should include in the method GetPropertiesFromRelationship the call to %UnSwizzleAt. I don't think in the above example it would improve performance in a positive meaningful way but when you encounter large relationship graphs this can be very important.

how about adding a note about the fact that each index implements a 

IndexNameExists(value,.tId)

method and can be used to quickly determine if a value exists in the index?

how about adding a note about the fact that each index implements a 

IndexNameExists(value,.tId)

method and can be used to quickly determine if a value exists in the index?

You might also find some relief by querying %Dictionary.MethodDefinition WHERE SQLProc=1 or %Dictionary.QueryDefinition.  If it's a really old version you might look in %Library schema.

This article refers to https://github.com/es-comunidad-intersystems/IRIS-in-Astronomy but when I tried going there you get a 404 Page not found.  Maybe the repo is not public?

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.