New post
AllAccepted answers
Benjamin De Boe · Aug 25, 2020

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)
    quit: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')
Benjamin De Boe · Jul 17, 2020

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.

Benjamin De Boe · Jul 15, 2020

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.

Benjamin De Boe · Jul 9, 2020

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.

Benjamin De Boe · Jul 9, 2020

if that column is currently in a string format (you can tell from the column/resultset metadata), you can use a different pattern to check if it meets the raw format:

SELECT CASE WHEN '01/01/2020' %PATTERN '1.2N1"/"1.2N1"/"4N' THEN 'valid' ELSE 'invalid' END

if you also want to check if that resolves to a valid date, you can use CAST or TO_DATE, but those will throw errors if they don't resolve to a valid date, so a small custom function in ObjectScript is perhaps not a bad idea

Benjamin De Boe · Jul 8, 2020

Examples where System is useful include when you have a set of classes that are used in compile-time tasks such as method generators and projection classes, as used by several InterSystems components such as BI cube definitions and NLP domain definitions. The Class Parameter expression example you describe is probably another example in the same category.

Benjamin De Boe · Jun 22, 2020

Great suggestions @Eduard Lebedyuk !

There's a vast surface area here, so we're really looking forward to something that showcases innovation on the technology end, something that carries a real-world data set/feed, or both! I can't wait until the week is over :-)

PS: don't hesitate to use this channel for any questions you have. Some of our technology in this area is still fresh and we're always happy to learn how we can make it easier to use.

Benjamin De Boe · Jun 8, 2020

Can you tie those performance regressions to the new (one-time) embedded SQL late compilation behaviour or is there anything else happening?

As a ground rule, we consider performance regressions upon upgrading to be bugs and will happily work with you to get to the root cause and reconsider optimizer changes and updated heuristics. Please reach out through the WRC if you think we can help.

Benjamin De Boe · Jun 8, 2020

You can force embedded SQL to be compiled along with the embedding application code using a compile flag /compileembedded=1. This is not recommended though, so we'd really like to know more about why the current model doesn't suit you. In fact this first-run compiling would allow your customers to leverage the statistics for their data rather than what you baked into it when compiling the embedded SQL. See also this article.

Benjamin De Boe · May 11, 2020

Depends on how you're set up. There is a setting to allow specifying those values that is meant for this type of bulk load scenario.

@Daniel Buxton : the multi-row insert syntax you're asking about is currently not supported by IRIS SQL (as you figured by now :-)). Robert's approach using UNION all is a creative workaround, but depending on where your big INSERT is coming from you might also just be able to generate / rewrite it into separate INSERTs? Once you have that in place, you can use the SQL Shell's RUN command or the ObjectScript $SYSTEM.SQL.DDLImport() method to execute your script file.

Benjamin De Boe · Apr 27, 2020

I don't want to discourage creativity, but this approach feels very risky to me. When you issue any SQL against this table before setting the % variable, it's going to cause ugly errors or unpredictable behaviour. I also wouldn't bet my money on this working in all possible parallel query execution scenarios (likely some, likely not all).

FWIW, within InterSystems development, we typically call % variables that survive between method calls a leak rather than single-rivet-keeping-your-skyscraper-together :-). Again, apologies for putting it a little strong here, but I think most use cases asking for flexibility can be addressed with more robust solutions, such as temporary tables, class inheritance (with the NoExtent keyword), etc. 

Benjamin De Boe · Apr 27, 2020

If you need a programmatic entry point for dealing with complex filter conditions, take a look at %SQL.AbstractFind. It allows you to invoke ObjectScript code to populate bitmaps based on (any number of) user-provided arguments through a %FIND predicate. It is built on by iFind and this older (but worthwhile) community article on custom spatial indices, although in both cases it works hand-in-hand with a custom index (using %FunctionalIndex). That isn't required, but usually the flip side of the same use case coin.