New post
AllAccepted answers
Stephen Canzano · Oct 30, 2020

Some of the reasons why I focus on utilizing class queries include

  • Studio and other editors are much better at providing coloring/syntax checking vs a strategy of setting a tSQL string as some arbitrary SQL statement
  • As mentioned in the original post it provides the separation that allows for easy re-use and testing.  If I have a class query I can decide to allow it to be reflected as a stored procedure, I can expose it to ZEN reports, ODBC/JDBC, JSON projection, %XML.DataSet, I can use it to satisfy a control in ZEN that allows for binding to a class query,  as well as others.  Basically, it provides for great separation.
  • I also like the idea of considering writing the statement using SQL as %Query.  If for some reason I run into performance issues that I cannot overcome I can change the implementation to %SQLQuery and implement COS code, the calling application would see the improved performance but does not have to understand the internal implementation.  However, I've only done this on extremely rare occasions.
  • I think one of the most important aspects of SQL to always read the query plan, it's there where you can understand what the optimizer is going to do.  I care all about the query plan and almost always validate what it reports.  Having a class query allows for easy Show Plan examination whereas it's generally hard to do if you take the approach of setting a tSQL string(sometimes with concatenation over several lines).

Class Queries are really worth investing in IMHO.

Stephen Canzano · Oct 13, 2020

I dont think your solution is a solution that works long term, someone can regenerate the record map and if your script isn't run then the property would be removed.  To answer your last question I think you would have better success if you define the property like

Property InsertDate As %UTC [ ReadOnly, SqlComputeCode = {set {*}=##class(%UTC).NowUTC()}, SqlComputed, SqlComputeOnChange = %%INSERT ];

I'm not 100% certain but the initial expression may only be executed as part of an object implementation but not part of an SQL statement.  If the RecordMap code is actually doing SQL inserts this may produce better results.

Stephen Canzano · Sep 8, 2020

Late in replying but the differences between sourcing data from Cache vs a warehouse/data mart is that Cache will be able to provide you real-time information vs a warehouse/data mart which can have some degree of staleness, but that's likely obvious.  The advantage with a warehouse/datamart is that you could bring in other data and join with that data.  At the same time, there would be nothing to exclude you from bringing in external data in the HSPI namespace.   We at Ready Computing have extensive experience with reporting of the HSPI data.  This includes several ZEN reports, although note that the ZEN reports are just calling SQL Stored Procedures we wrote.  We also have DeepSee cubes defined that provide analysis on both the Patient table as well as the Classified pairs data.  It should be noted that the Classified pairs table has a number of indices defined to support most use cases for SQL queries.  Lastly, we've not found issues with the definition of the Patient table as far as performance goes.

Stephen Canzano · Aug 9, 2020

Ok so this is definetely Centricity Business aka Flowcast and not GroupCast.

Generally speaking your query looks correct but some considerations

The join is incorrect.  Following your exact FROM clause, you would consider

FROM   Registration.Patient reg

       JOIN BAR.Invoice BAR on BAR.GID = Reg.ID

       JOIN Dict.Provider prov on prov.Id=BAR.prov

There is an index on bar.invnum so there is no issue with indices defined.

Note that properties/columns are properly typed in these classes so you could make the statement more concise by doing

SELECT Grp,  

                GID->PatNm As Guarantor,

                 GID->MRN As MRN

                  Prov->Name As Provider,

                  SetDt,

FROM     BAR.Invoice

WHERE  InvNum BETWEEN 63882965 and 64306671

Stephen Canzano · Jul 27, 2020

Unless things have changed with IRIS I generally prefer to use triggers over any of the object implementations.  Properly defined triggers will be executed whether you are doing an object Save or a SQL INSERT/UPDATE/DELETE.  You may only want to perform the code during object save but I find why not implement the code in a trigger where you know it will always be executed.  Additionally triggers provider  

{ColumnName*N}

{ColumnName*O}

syntax which is valuable.

Stephen Canzano · Jul 22, 2020

I used to work at IDX under the division that produced what was called IDX Flowcast.  IDX Flowcast is a practice management system for large practice/academic medical centers.  Groupcast on the other hand was for the small practice environment, I don't recall the exact # of doctors as the threshold cutoff.  I do not believe Groupcast is based on Intersystems Cache, but I could be incorrect.  If the system is Flowcast aka GE Centricity Business, then most all of the data is exposed via Cache classes that are based on %SQLStorage and hence would be exposed via any SQL client interface connecting to Flowcast/GE Centricity Business using ODBC/JDBC.

Stephen Canzano · May 17, 2020

Not to my knowledge.  While there is a global node in the storage map that is used to get the next available Id this would only work on tables/objects based on a single integer id.  At the same time, this is the next available Id and does not account for physical deletes that may have occurred, ie the next Id might be = 101 but you may have fewer than 100 rows/objects as some may have been deleted.  The simplest way to accomplish this would then to perform a SELECT COUNT(*) FROM TableName.  If the table implements this bitmap indices this should be ms.  If you don't get the performance you want you might consider adding %PARALLEL to the FROM clause and let the optimizer decide if it makes sense to split the job.

Stephen Canzano · May 13, 2020

I'm not sure I completely understand your question but one thing I have had to use recently is found here https://docs.intersystems.com/irislatest/csp/docbook/Doc.View.cls?KEY=D2MODEL_prop_for_list

I had a level which was a list and I too wanted to define properties for the level, the property would be for each element of the level/list.  In my case I defined my level to run off of an expression where my expression returned a list of values.  Then for my property definition, I used an expression as well.  In the expression, I called a method passing %value.  

Hope this gives you something to go on.

Stephen Canzano · May 1, 2020

You wrote

BUT the server is 6 times faster if OpenId replaced with simple read of a large global (s p=^someLargeGlobal). Any ideas what makes OpenId so slow only on the server?

while not specifically answering your general question, note that opening an object is very different than 

Set p=^SomeLargeGlobal.

When you call %OpenId

  1. Concurrency is checked.  While not widely used there is a second parameter to the %OpenId method that controls the behavior of concurrency  and you may want to consider providing the desired concurrency option.  In the past if I was not concerned with concurrency as I just wanted to read data I would use option 0.
  2. Each and every property defined in your class is loaded into memory, ie the data is read from the global and then the individual properties are set accordingly.  Depending on how complex your class is the difference between 

Set p=^SomeLargeGlobal 

and %OpenId can be quite different, at least academically.

If your class as relationships and/or serial/embedded classes I do not believe those properties are fully loaded into memory, but I could be incorrect.

 

In practice, if I need an object I use objects, if I need only a small part of the object/row I'll use embedded SQL for better performance.

Again this does not specifically answer your general question but I think it is useful to understand what %OpenId does and why it's not the same as 

Set p=^SomeLargeGlobal.

Stephen Canzano · Apr 15, 2020

with your updated problem description where your globals only contain a single node I would consider the serial/embedded approach.  This still don't address your request for a variable name for the storage, but it does mean for the columns/properties you can define them in the serial class once and then embed in the class that represents global a and global b.  It also means in the storage map you just have to define the data node as the serial property. 

Let's say we could get it to work such that the storage map is based on a variable, I suspect other things wouldn't work.  For example, in Documatic when you view a class you can select the Storage checkbox and get a display that looks like 

I suspect if somehow you can get the storage map to be dynamic and based on a variable name this display would fail or not show you the value of the variable.

Stephen Canzano · Apr 15, 2020

Depending on what you exactly mean by the same structure you consider using a Serial Class definition that is embedded in both of your classes.  However, if the structure is stored across several global nodes I do not think you could use this as the Serial class would define the pieces(whether delimited or $ListBuild pieces) and then the serial property is described in your 2 classes to occupy a single node.

You could also consider defining a single abstract class that describes the properties and have your 2 classes inherit from your abstract class.

With regards to having a variable defining the data location I suspect that it may not be doable, even if it were I don't see how it improves things in a significant way.

Stephen Canzano · Jan 10, 2020

I'm generally looking at the query plans either from the SMP or from the context menu in Studio while writing class queries/embedded sql statements.

One issue that I've seen is that while the query plan is very good, an in many cases better than what other dbms's provide, when a sub-query is part of your query statement it's not exactly clear when it is utilized in the query plan.  For example, I have this query plan

I cannot tell with 100% certainty where "subquery" is called.