You can also reference https://docs.intersystems.com/iris20251/csp/docbook/Doc.View.cls?KEY=GCO.... 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.
The Data keyword means to capture/store the values of City and State in the index but not as a key. if your sql query needs to query for the ZipCode, City, and State with Data=(City,State) the query can be index satisfiable, meaning it will read data from the index and get the City and State from the index vs. having to go to the master map/Table to get the City and State.