Looking at your example class, and maybe its not practical as we aren't seeing the entire context of your real world usage but might you see better resuilts if you were using instead of a composite index on prop1, prop2, prop3 but rahter bitmap indices on prop1 as it seems like it is the field name and would have a suffeceintly small number of values, a bitmap index on prop2, and a traditional index on prop3.  

If you were to show the query plan that would also provide some insight.

I would imagine if you have a license from Intersystems you could install Cache, mount the DB, and then the next question is "Was the application developed back in 2017" with an SQL interface, ie are there SQL tables that exist for the database that you have?"  If so you could connect to the db via ODBC and extract the data.

If not then that's a much large endeavor as you would need to have the tables defined and depending on how much meta-data/documentation you have for the data that could be quite involved.

Joel,

That's assuming your class query is based on %SQLQuery.  If your class query is based on %Query then you could have a single class query.

some thoughts on using custom ZEN Components that I follow that might help.

1. In my custom component I have 

   ///  If true, then the include files generated for this component, are placed in the
   ///  common /csp/broker directory and not the local /csp/*namespace* directory.<br/>
   ///  This is intended for use with packages that are mapped so as to be visible to every namespace.<br/>
   ///  All Zen classes within the same package must have the same value for this parameter.
   ///  It is the developer's responsibility to ensure this.
   Parameter USECOMMONDIRECTORY As BOOLEAN = 1;

   and

/// XML namespace for component.
Parameter NAMESPACE = "http://www.readycomputing.com/zen";

2. In my ZEN page in 

<page xmlns="http://www.intersystems.com/zen" xmlns:RC="http://www.readycomputing.com/zen"

if you are using Package Mapping with a common database for your classes I found using USECOMMONDIRECTORY neccessary.

Is this a CSP class or a class that extends from %ZEN.Component.page, it looks like you might be have a CSP page that wants to use ZEN components and do not think that is possible.. but I could be incorrect.  Regardless, is the &html ON LINE 72 closed with the > symbol on line 92?

How 'bout https://docs.intersystems.com/iris20241/csp/docbook/Doc.View.cls?KEY=GTS...

Maybe one good reason to have all of this in a class is normally you would make a change to the definition which write to the class, you then commit your changes, ie commit the class, and then promote the class using your normal class deployment strategy.

Still not fully able to grasp what you are doing but if are trying to address 

log.cls_id IN (61) AND log.q_id IN (19, 25, 27)

I often find myself doing something like

log.cls_id %INLIST $LISTFROMSTRING(':Param1,',')

AND log.q_id %INLIST $LISTFROMSTRING(':Param2,',')

and then set Param1=61 and Param2 = "19, 25, 27"

so long as you know the values of Param1 and Param2. 

%INLIST is documented https://docs.intersystems.com/iris20241/csp/docbook/Doc.View.cls?KEY=RSQ...

and

$LISTFROMSTRING is documented here https://docs.intersystems.com/iris20241/csp/docbook/Doc.View.cls?KEY=RSQ...

Regarding

If the first bullet item in the Query Plan is “Read master map”, or the Query Plan calls a module whose first bullet item is “Read master map”, the query first map is the master map rather than an index map. Because the master map reads the data itself, rather than an index to the data, this almost always indicates an inefficient Query Plan. Unless the table is relatively small, we should create an index so that when we rerun this query the Query Plan first map says “Read index map.”

I think it's more subtle than that.  My general plan of attack is to review the results of Show Plan and then search for Looping.  If the first bullet item in Show Plan is one of the following

• Read master map Ens.MessageHeader.IDKEY, using the given idkey value.
• Read master map Ens.MessageHeader.IDKEY, looping on ID (with a range condition).

I'm not immediately concerned.  In the first case this is going directly to the row which is perfectly fine.  

In the second case so long as the range condition is not going to read the entire extent I can accept that and not look for a better query plan.

Honestly, where its 

Read master map Ens.MessageHeader.IDKEY, looping on ID (with a range condition).

or

Read index map Ens.MessageHeader.TimeCreated, looping on TimeCreated (with a range condition) and ID.

I don't care if its the master map or the index map, what I'm interested in is Looping and does looping cause the engine to look at the entire extent or index.

You wrote

The module.xml has the one line which describes unit tests:

<UnitTest Name="src" Package="UnitTests" Phase="test"/>

This line means that  ZPM will look in /src folder of the repo and will run all the tests of all the classes which are included in UnitTests packageIs 

Is this to say the Name="src" means it will look on the machine where the module has been installed in the /src folder?  If this is so this is problematic for us I believe.  We are trying to protect our IP and as part of the Module.xml definition we have 2 Invokes that help with this effort

1. <Invoke Class="RCIPM.Utils" Method="MakeModuleDeployed" Phase="Compile" When="After" CheckStatus="false">  We do this because there are some classes which cannot be marked as Deployed as they need to be compiled additional times on the application namespaces after we have run the install command in HSCUSTOM

2.  <Invoke Class="RCIPM.Utils" Method="DeleteModulesFolder" Phase="Activate" When="After" CheckStatus="true">

If we don't delete the Module folder then our classes are there in plain text and could be lifted off of the server.

so if Name="src" mean look in the /src folder on the target machine(not the registry) then this wouldn't work for us.

That's not to say that DeleteModulesFolder doesn't cause other issues but its currently what we feel we need to do to protect our code.

Just one person's opinion but most all of my SQL development for SELECT statements is done using class queries. Several compelling features of class queries include

1. When at least using IRIS Studio you can right-click on the text and call Show Plan.  I believe that the first thing that matters for a SQL statement is the actual plan, the relative cost is relative and not so interesting to me.  What the Query plan says that it is doing tells me all that I need to know.  If you are using Dynamic SQL and building a SQL string this is much harder to do.

2. In IRIS Studio and VS Code there is a modest amount of syntax checking and coloring in class queries whereas approaches that use some string creation for the statement do not have this.  If I were to incorrectly write

set sql = "select Product->Name, Outlet->City, AmountOfSale, UnitsSold "_
        "from HoleFoods.SalesTransaction wher Actual = 1 "

ie, I've misspelled WHERE I won't see this error until after I've run the code.

This is something that can't easily done with Dynamic SQL.

3. If I choose to mark the class query as a SQLProc then I can perform a form of test using System Explorer->SQL by selecting the stored procedure and then selecting RUN.

4. I'm really liking Table Valued Functions(TVF).  Some documentation is here https://docs.intersystems.com/iris20233/csp/docbook/DocBook.UI.Page.cls?... .  I couldn't find better info in DOCBOOK but from the interweb 

The simple definition of the table-valued function (TVF) can be made such like that; a user-defined function that returns a table data type and also it can accept parameters. TVFs can be used after the FROM clause in the SELECT statements so that we can use them just like a table in the queries.The first thing that comes to our mind is that, what is the main difference between the view (Views are virtual database objects that retrieve data from one or more tables) and TVF? The views do not allow parameterized usage this is the essential difference between views and TVFs. 

so if I have

Class Sample.Person Extends (%Persistent, %Populate)
{
Property Name As %String(POPSPEC = "LastName()");
Property Age As %String [ Calculated, SqlComputeCode = { w:{Name}="Uhles" 1/0 Set {*}=""}, SqlComputed ];
Query ByName(LastName As %String) As %SQLQuery(CONTAINID = 1)[SqlProc]
{
SELECT 		%ID,Age,Name 
FROM 		Sample.Person
WHERE  		Name %STARTSWITH :LastName
ORDER By    Name
}

I can run in SQL

SELECT * FROM Sample.Person_ByName('Test')

and I can also include columns that the TVF provides in other portions of the Query so I could 

SELECT   Age,Name
FROM     Sample.Person_ByName('Test')
ORDER BY Age

as well as use the TVF as part of a JOIN with other tables.

5. Class Queries SQL statements are visible in DOCUMATIC.  Some less than experienced SQL authors, might be able to have more success if they see a class query that has the best-case example of how to write the JOINs.  Dynamic SQL doesn't provide any UI for folks to view other than being able to view the class.

6. Class queries afford re-use.  I see often folks including SQL statements midway through a method.  A class query allows you to separate the SQL statement from your method code and can be utilized in so many things

• Class queries as SQL Procs in any xDBC client/reporting tool
• REST APIs
• ZEN Reports.. yes I know deprecated
• ZEN UI pages .. yes I know deprecated
• ObjectScript code
• etc.

Maybe I've sold you on the idea, maybe not.

and that screen capture demonstrates why good class documentation especially method descriptions are super valuable including when you are using VS Code.

This information is found in the class

Ens.ServiceRegistry.External.Service