You can also reference https://docs.intersystems.com/iris20251/csp/docbook/Doc.View.cls?KEY=GC…; While this documentation is for IRIS all of the information should be applicable for Cache as well.

A couple of thoughts to this

1. Clearly all of the metrics depend on the presence of any indices.  For Query #1 it may have done a table scan vs a (bitmap) index scan.  However, you are comparing the same query so it should be an apples to apples comparison.

2  Did you perform the test and ensure the global cache was flushed.  For Query #1 if you ran the embedded query firstly, the side effect of that is much of the data was in the global cache which mean the Dynamic SQL query was able to do logical reads vs physical reads.  This could explain why it is faster.  I would expect that both techniques used the same exact Query plan and likely used the same exact generated query routine (%sqlcq*).  For that reason I wouldnt expect the times to be so very different.  BTW I dont know of a way to flush the global cache other than to restart IRIS and perform the statement again.  Another way of lookng at this is if you run a sql statement(whether Embedded or Dynamic) twice, the second time will always be faster as the data is now in the logical global cache.

3. I dont agree with "In general, we can see that parent-children relationship is a lot slower from the side of the children property, even though all the data of Patient and Visit is stored in the Patient global.".  The devil is in the details and the indices, where clause. 

For any of these queries without seeing the query plan we dont have the full story.  We also dont know

1. the return for the count(*)

2. were the queries purley index satisfiable or did they have to go back to the table(mastermap).  If they had to go to the table how "fat"(how many total columns and the overall lentgh of a row) was the table and how tall(how many total rows) was the table.    

Not enought detail here to see exactly what you are encountering but %LIbrary.DynamicObject in the %Set method supports

 method %Set(key, value, type) as %DynamicAbstractObject

Create a new value or update an existing value.

key The key name of the value you wish to create or update.

value The new value with which to update the previous value or create a new value.

type OPTIONAL, the 'type' of the value being assigned.

If the 'type' argument is present then its value must be one of the following string values: ""          - %Set(key,value) without conversions
"null"        - "" becomes null; else assign with no conversion
"boolean"      - 0/nonzero becomes JSON false/true
"number"      - Convert to numeric value
"string"       - Convert to text string
"string>base64"  - Convert to text string then encode into base64
"string<base64"  - Convert to text string then decode from base64
"stream"      - %Stream contents converted to text string
"stream>base64" - %Stream contents are encoded into base64 string
"stream<base64" - %Stream is decoded from base64 into byte string

 so if one if your fields is greater than the max string lenght usage of type properly would place the data in a stream object. 

Another alternative is to not use %LIbrary.DynamicObject but rather define classes and extend from %JSON.Adaptor and define your properties with the correct data type, again for the field larger than Max. String Length as %Stream.GlobalCharacter.

While %Library.DynamicObject is quick and (not dirty) I prefer the approach of using %JSON.Adaptor except for the cases where the payload can change for some reason or another or over time.  The approach of defining your classes is purposeful as it defines the schema concretely(whether you think this is good or bad is up to you).

Yes this is true and a formal way to do this is really the thing you queue, whether a function or a class method should return a %Status to signal to the wqm that an error occured.  In practice the usage of wqm.Queue should not only call a function or class method but pass some parameters.  I often times have my function/class method act on a collection/list of rowids so my call to .Queue includes the function/classmethod as well as the list of rowids to process.

Can you share the query plan?

What mode are you running in the query in(Logical/ODBC/Display)

Maybe someone is looking for job security.  

Regarding 

You will miss out on DTL specific functions (example lookups), albeit you could call the look up code in COS

some might think that using classes with COS code is better as it allows you to call any funtion you like. I think that's demonstrating a lack of knowledge.  If you created a class that extends Ens.Rule.FunctionSet , the functions then become available to anyone using the DTL or Rule Editor.

Additionally the new Rule Editor allows for ForEach whch otherwise might have been difficult to implement in the past.

I'd much rather be spending my time working on more advanced parts of an implementation instead of DTLs and Rules.

The only odd thing about DTLs and Rules is when you ask a business analyst to be involved they might not feel as strongly about following a rigorous CI/CD process. In my experience, some folks like to make changes to the DTLs and Rule and not commit to a feature branch and promote up thru the different environment(TEST,STAGE,PROD) as would normally be done if one was striclty in a class, even thought DTLs and Rules are in fact classes.

will this implementation be employed for the HealthShare ATNA tables as these have billions of rows at customer sites

Isnt this the data from the class/table %SYS.Task and why not just use SQL or Objects?

In the past what I have done is define a Web Application with a REST Dispatch class and in that class serialize the data from the JSON payload into a real message class with properties.  In this way the Business Service receives a request object with the properties defined and now the Message Viewer has the expected functionality.  This also means that the business service is not purlely a tied to REST.  Anyone that sends a proper pRequest object can use the business service.

Have you looked at utilizing

https://docs.intersystems.com/iris20243/csp/docbook/Doc.View.cls?KEY=GS…

The video talks about using the published APIs.  I assume this specifically referes to the APIs in the Security package.  My specific question is will we still be able to write SELECT statements against %SYS.Audit and will anything special need to be done to be able to do so.  Will we have to update users to add resources or grant permissions?

which is responsible for some batch operations with an SQL table

Is this a native IRIS SQL Table and have you examined the Query plan for the SQL Table?

If you run the report via the browser 

MSDS.Maintenance.Report.CBMReport.cls?LocID=loc&WOID=wo&LineID=lineId&$MODE=PDF&$LOG=1

where the values of loc,wo, and lineId

is there anything helpful reporting in the log.

Might you be able to share the 2 sql statements and the query plans?

If you are running the queries in the System Management Portal there is a limit that you could set 

but I dont imagine that is the issue.

Where possible a COUNT(*) query could chose to use your indices and in theory your indices might be different from your table data but that would be highly suspicios under normal circumstances as the writing to the table and the indices are in a database transaction so it should have all been written or not.  

To your specific question

Is there something inherent with the queries that limits the result set size, or anything I can do to make sure I'm getting back all the results? Rather, than trying to run it about 100 times limiting all previously gotten accounts.

Again if you are running via the System Management Portal there are times I run the query with a limit of 100 for example and once the results are presented I select the Print hyperlink to run the entire query results to a file

when you say using IPM do you mean perfoming "install -v moduleName" on IRIS or do you mean creating a registry on IRIS earlier than 2022.1

Using the message viewer you can add criteria and search by 

I'm assuming folks from InterSystems have a more formal way of saying "you have a partner with InterSystems".  When the occasional crisis occurs, the support of many folks from WRC rally around your issue and see it to the end.  Having the ability to contact someone on the other end and resolve crisis issues is not something you are likely to get from other vendors.

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.