New post
AllAccepted answers
Alexander Koblov · Aug 4, 2016

That is good question, Joel. I don't know why.

I can speculate that this is ^%SYS.MONLBL being not very accurate -- notice, amount of lines for example is not exactly 2'000'000. But that is just speculation -- I don't know for sure. Maybe someone who understands how this metrics in ^%SYS.MONLBL are calculated can comment.

I've just repeat the test on another system with Caché 2016.2 FieldTest and got pretty much the same results -- ~40 seconds with $Increment and ~5 seconds with $Sequence.

I compiled %Library.PopulateUtils with source code:

do $system.OBJ.Compile("%Library.PopulateUtils","ck")

And ran ^%SYS.MONLBL.

Call to FirstName took 31.62 for ten processes with $Increment. And 17.37 for ten processes with $Sequence.

Here is counts for FirstName when we use $Increment:

; Call to FirstName
11      1992818   8.744534  31.619480          set name = ##class(%Library.PopulateUtils).FirstName()

; Source code of FirstName
95      1983658   8.763054   8.776989  New list
96      1993042   3.019774   3.020288  s:$g(gender)="" gender=($zu(165,1,2))+1
97      1993392   1.112629   1.112751  If (gender = 1) {
98      1000154   1.411869   1.411946  Set list = $LB("Al","Alfred","Alvin","Andrew","Angelo","Aviel",
99            0   0          0         "Bart","Bill","Bob","Brendan","Brian","Buzz","Chad",
100           0   0          0         "Charles","Chris","Clint","Dan","Dave",
101           0   0          0         "David","Dick","Dmitry","Ed","Edgar","Edward",
102           0   0          0         "Elmo","Elvis","Emilio","Fred","Geoffrey",
103           0   0          0         "George","Howard","James","Jeff","Joe","John",
104           0   0          0         "Jose","Joshua","Jules","Keith","Kenny","Kevin",
105           0   0          0         "Lawrence","Mark","Mario","Martin","Marvin","Michael","Milhouse",
106           0   0          0         "Mo","Neil","Norbert","Orson","Patrick","Paul",
107           0   0          0         "Peter","Phil","Quentin","Quigley","Ralph",
108           0   0          0         "Richard","Rob","Robert","Roger","Sam","Stavros",
109           0   0          0         "Stuart","Ted","Terry","Umberto","Vincent",
110           0   0          0         "Will","William","Wolfgang","Xavier","Zeke")
111      998702   0.459684   0.460028  }
112           0   0          0         Else {
113      996337   1.426439   1.427385  Set list = $lb("Agnes","Alexandra","Alice","Amanda","Angela","Ashley",
114           0   0          0         "Barb","Barbara","Belinda","Brenda",
115           0   0          0         "Charlotte","Chelsea","Christine","Christen",
116           0   0          0         "Danielle","Debra","Debby","Diane","Elvira","Emily","Emma",
117           0   0          0         "Filomena","Frances","Gertrude","Greta","Hannah","Heloisa","Imelda",
118           0   0          0         "Jane","Janice","Jocelyn","Josephine","Juanita","Julie",
119           0   0          0         "Kim","Kirsten","Kristen","Kyra","Laura",
120           0   0          0         "Linda","Lisa","Liza","Lola","Lydia",
121           0   0          0         "Maria","Mary","Maureen","Michelle","Molly",
122           0   0          0         "Nataliya","Natasha","Nellie",
123           0   0          0         "Olga","Pam","Pat","Patricia","Phyllis",
124           0   0          0         "Rhonda","Roberta","Sally","Samantha","Sophia","Susan",
125           0   0          0         "Tara","Terry","Thelma",
126           0   0          0         "Uma","Usha","Valery","Violet","Wilma","Yan","Zelda","Zoe")
127           0   0          0         }
128     1981723   7.896435   7.902909  Quit $LI(list,($zu(165,1,$LL(list)))+1)

Here is for $Sequence:

; Call to FirstName
11      1862630   4.837207  17.372511          set name = ##class(%Library.PopulateUtils).FirstName()

; Source code of FirstName
95      1834226   4.289204   4.389613  New list
96      1902287   1.877691   1.891898  s:$g(gender)="" gender=($zu(165,1,2))+1
97      1925621   0.863039   0.865561  If (gender = 1) {
98       980468   0.815338   0.817548  Set list = $LB("Al","Alfred","Alvin","Andrew","Angelo","Aviel",
99            0   0          0         "Bart","Bill","Bob","Brendan","Brian","Buzz","Chad",
100           0   0          0         "Charles","Chris","Clint","Dan","Dave",
101           0   0          0         "David","Dick","Dmitry","Ed","Edgar","Edward",
102           0   0          0         "Elmo","Elvis","Emilio","Fred","Geoffrey",
103           0   0          0         "George","Howard","James","Jeff","Joe","John",
104           0   0          0         "Jose","Joshua","Jules","Keith","Kenny","Kevin",
105           0   0          0         "Lawrence","Mark","Mario","Martin","Marvin","Michael","Milhouse",
106           0   0          0         "Mo","Neil","Norbert","Orson","Patrick","Paul",
107           0   0          0         "Peter","Phil","Quentin","Quigley","Ralph",
108           0   0          0         "Richard","Rob","Robert","Roger","Sam","Stavros",
109           0   0          0         "Stuart","Ted","Terry","Umberto","Vincent",
110           0   0          0         "Will","William","Wolfgang","Xavier","Zeke")
111      969898   0.302964   0.306063  }
112           0   0          0         Else {
113      971397   0.836977   0.843322  Set list = $lb("Agnes","Alexandra","Alice","Amanda","Angela","Ashley",
114           0   0          0         "Barb","Barbara","Belinda","Brenda",
115           0   0          0         "Charlotte","Chelsea","Christine","Christen",
116           0   0          0         "Danielle","Debra","Debby","Diane","Elvira","Emily","Emma",
117           0   0          0         "Filomena","Frances","Gertrude","Greta","Hannah","Heloisa","Imelda",
118           0   0          0         "Jane","Janice","Jocelyn","Josephine","Juanita","Julie",
119           0   0          0         "Kim","Kirsten","Kristen","Kyra","Laura",
120           0   0          0         "Linda","Lisa","Liza","Lola","Lydia",
121           0   0          0         "Maria","Mary","Maureen","Michelle","Molly",
122           0   0          0         "Nataliya","Natasha","Nellie",
123           0   0          0         "Olga","Pam","Pat","Patricia","Phyllis",
124           0   0          0         "Rhonda","Roberta","Sally","Samantha","Sophia","Susan",
125           0   0          0         "Tara","Terry","Thelma",
126           0   0          0         "Uma","Usha","Valery","Violet","Wilma","Yan","Zelda","Zoe")
127           0   0          0         }
128     1839043   3.943282   4.006857  Quit $LI(list,($zu(165,1,$LL(list)))+1)
Alexander Koblov · Aug 3, 2016

Lucy,

if you are using Studio for the first time, why did you start with copying system class?

Class Security.Users is in 'deployed mode'. You can see properties and method's headers, but you cannot see code behind the methods. The class that you copied Security.Users to -- UCDavis.Security.Users -- now is also in deployed mode.

More info about deployed mode:
http://docs.intersystems.com/cache20161/csp/docbook/DocBook.UI.Page.cls?...

Please tell us, what is the end goal, that you are trying to achieve? Why do you need to copy Security.Users?

Thank you,
Alexander.

Alexander Koblov · Jul 15, 2016

Hi Raghu.

I don't know about XPath, but maybe using XSLT might help you here:

Class Sample.XSLTransform [ Abstract ]
{

ClassMethod test()
{
    set tXML= ##class(%GlobalCharacterStream).%New()
    do tXML.Write("<HHSOS><DIAGNOSES><DIAGNOSIS_DATA><DIAGNOSIS_DATA_GUID>3762875</DIAGNOSIS_DATA_GUID><DIAGNOSIS_DATA_GUID>37628752</DIAGNOSIS_DATA_GUID></DIAGNOSIS_DATA><DIAGNOSIS_DATA></DIAGNOSIS_DATA><DIAGNOSIS_DATA></DIAGNOSIS_DATA><DIAGNOSIS_DATA_GUID>37628753</DIAGNOSIS_DATA_GUID></DIAGNOSES></HHSOS>")

    set tXSL=##class(%Dictionary.CompiledXData).%OpenId(..%ClassName(1)_"||ExampleXSL").Data

    set tSC=##class(%XML.XSLT.Transformer).TransformStream(tXML,tXSL,.tOutput)
    zwrite tSC
    set tSC=tOutput.OutputToDevice()
}

XData ExampleXSL
{
<?xml version="1.0"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:template match="/">
    <xsl:copy-of select="//DIAGNOSIS_DATA_GUID[1]"/>
</xsl:template>
</xsl:stylesheet>
}

}

SAMPLES>d ##class(Sample.XSLTransform).test()
tSC=1
<?xml version="1.0" encoding="UTF-8"?><DIAGNOSIS_DATA_GUID>3762875</DIAGNOSIS_DATA_GUID><DIAGNOSIS_DATA_GUID>37628753</DIAGNOSIS_DATA_GUID>
Alexander Koblov · Jul 12, 2016

Conrad,

your question have two parts

a) For queries like

WHERE a = (select ...)

if subquery returns more than one row, Caché will compare left part with first value of subquery.

b) If several people are tied for the minimum age, then following query prints all of them

SELECT Age,Name,home_state
FROM Sample.Person p1
WHERE age =  (
    SELECT min(age)
    FROM Sample.Person p2
    WHERE p1.home_state = p2.home_state)
Alexander Koblov · Jul 12, 2016

Documentation explains both functions well and with examples, so I encourage you to look into them. Especially first two examples for %ALL function

ALLMEMBERS -- function that returns a set of all members of the given level or hierarchy
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

%ALL -- function that enables you to use a member while ignoring any ROW and COLUMN context that uses the hierarchy to which this member belongs.
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

As to your question about calculating percentage of the top level, here is sample that for each product calculates revenue percentage of total from all products (2nd column) and from category for this product (3rd column).

Sample pivot

RevenuePctOfAllProducts and RevenuePctOfParent are calculated measures that defined as follows:

RevenuePctOfAllProducts:

Measures.[Amount Sold] / SUM(Product.[All Product].%ALL, Measures.[Amount Sold])

RevenuePctOfParent:

Measures.[Amount Sold] / SUM(Product.CurrentMember.Parent, Measures.[Amount Sold])

So the full query looks like:

WITH
MEMBER [MEASURES].[RevenuePctOfAllProducts] AS 
    'Measures.[Amount Sold] / SUM(Product.[All Product].%ALL, Measures.[Amount Sold])' 
MEMBER [MEASURES].[RevenuePctOfParent] AS 
    'Measures.[Amount Sold] / SUM(Product.CurrentMember.Parent, Measures.[Amount Sold])'
SELECT NON EMPTY {[Measures].[Amount Sold],
                  [MEASURES].[REVENUEPCTOFALLPRODUCTS],
                  [MEASURES].[REVENUEPCTOFPARENT]} ON 0,
    NON EMPTY HEAD(NONEMPTYCROSSJOIN([Product].[P1].[Product Category].Members,[Product].[P1].[Product Name].Members),2000) ON 1 
FROM [HOLEFOODS]
Alexander Koblov · Jul 11, 2016

As far as I understand such usage of GROUP BY and select columns is not standard in SQL world.

That is -- if we GROUP BY some fields, then in SELECT list we can have either fields we group by or other fields as arguments of aggregate functions.

We can write

SELECT home_state, max(age)
FROM sample.person
GROUP BY home_state

But we cannot write

SELECT home_state, max(age), name
FROM sample.person
GROUP BY home_state

It is not clear -- what name out of all rows that have the same home_state do we want.

Consider following data in Sample.Person:

Age Name Home_State
10  John MA
10  Jim  MA

What name will following query return John or Jim?

SELECT Age,Name
FROM Sample.Person
GROUP BY Home_State
HAVING Age = MIN(Age)

I prefer following variant of the query with join:

SELECT Age,Name,home_state
FROM Sample.Person p1
WHERE age =  (
    SELECT min(age)
    FROM Sample.Person p2
    WHERE p1.home_state = p2.home_state)

That returns both rows from the sample data above.