Hi Mohamed,

there is no reason to start any new projects with InterSystems Caché. It's a platform that has proven itself for numerous years, but it's now being superseded by InterSystems IRIS, which builds on the same core strengths but has also been bolstered by pretty much all the new development at InterSystems over the past three years. 

This said, to complement Kevin's excellent list of links, you can also check out https://www.intersystems.com/try for a free trial hosted by us (so you won't need your cloud account just yet) and https://gettingstarted.intersystems.com/ with more entry-level documentation and tutorials.



Class Test.Person Extends (%Persistent, %XML.Adaptor)
Property Name As %String; 
Property Id As %Integer [ Identity ]; 

I agree entirely a separate table with FK links between the two is the SQL-friendliest way to go about this today.

With expression indices (and later next year LATERAL JOIN support), we will soon have a practical way to leverage these Object-ish data models in all their beauty from SQL too!

This is not possible today, but happens to be something due for 2020.4 or 2021.1 in the form of expression indices.

The documentation may be slightly confusing here as the BuildValueArray() mechanism applies to non-collection properties and not "array of" or "list of" properties. The section that describes it just happens to be placed right after the section describing how to use the ELEMENTS trick to index them in their entirety. Note that you can use BuildValueArray() for %ArrayOfObjects properties, but those aren't projected as a child table.

A possible workaround would be through a separate property that's just there as an intermediary for your index:

Property AR As array Of Sample.Address;
Property AllStates As %String [ Calculated, ReadOnly, SqlComputeCode = {set {*} = {%%ID}}, SqlComputed ];
Index AllStatesIDX On AllStates(ELEMENTS);
ClassMethod AllStatesBuildValueArray(value, ByRef valueArray) As %Status
  kill valueArray
  set tObj = ..%OpenId(value), tKey = ""
  for {
    set tAddress = tObj.AR.GetNext(.tKey)
    set valueArray(tKey) = tAddress.State
  quit $$$OK

Then you can include it in queries:

select * from sample.person where FOR SOME %ELEMENT(AllStates) (%VALUE = 'KY')

the contents of those %Dictionary tables is a little geared towards class/object models. If you want a more SQL-focused view on your tables, you can look at the INFORMATION_SCHEMA package, which adheres to mainstream JDBC/ODBC dictionary structure and is used by mainstream SQL and BI tools like DBeaver, VSCode-SQLTools, Tableau, PowerBI, etc

Note you'll need to tick the "System" checkbox when browsing this schema in the System Management Portal.

Hi David, maybe you can elaborate a little more on the particular latency challenge you faced?

As Dmitriy mentioned, sharding will spread your data and corresponding query workload across multiple nodes in order to achieve higher efficiencies on very large datasets and is especially fit for read-mostly workloads. ECP, when used in a typical application server setup, is meant to distribute user-bound workload across multiple servers, so serving a slightly different goal. Depending on your use case, either of those (or a combination of them) can be more appropriate. See also this overview and introductory video for more info.

Could you add a little more detail on what you mean with "classes" and "used"?

If you are talking about generic ObjectScript classes and look at usage as plain invocations from any ObjectScript code, you may not be able to find that at all. If, on the other hand, you're looking for Business Services, Processes and Operations classes, there's a good chance we can pull much of that from available metadata (up to your most recent purge). Also, for persistent classes, you may be able to find whether they were recently accessed through SQL by looking into the Statement Index, but that of course only is about SQL and doesn't guarantee you they weren't used otherwise.

Indeed. An incredible amount of things have happened between that version and the current IRIS release, one being automatic parallelization of eligible SQL queries, which likely does exactly what you're looking for.