$EXTRACT^doc

Keep in mind that

Characters are counted from 1.

, so instead

$EXTRACT({LabName},0,3)

should be

$EXTRACT({LabName},1,3)

In this case, it is uncritical, because

If the from value is 0 or a negative number, $EXTRACT returns a null string; however, if from is used with to, a from value of 0 or a negative number is treated as a value of 1.

But in other cases it can lead to the wrong result.

That would work storagetime but not at runtime.

It was just a matter of ease of accessing data using SQL. No more than.

Who knows, maybe the author just decided to use SQL to identify duplicates?

I think runtime hook disallowing duplicate inserts would be better.

I agree. But this is a completely different task and is solved in a different way.
Like this:

Property Actor As list Of packet.Actor(POPSPEC = ".ActorFilter():10") [ Required ];

Method ActorFilter() As %Integer
{
  set actorID=$random(10)+1

  for i=1:1:$order(i%Actor(""),-1) return:actorID=$list(i%Actor(i),1) ""

  quit actorID
}

The author after all asked about SQL therefore I above and asked to specify that it is necessary actually.

There are a couple of comments:

1. Instead of

   Class packet.Actor Extends (%Persistent, %Populate)
   {
     Property Name As %String [ Required ];
   
     Index NameIndex On Name [ Unique ];
   
     Property Age As %Integer (MAXVAL=100, MINVAL=10) [ Required ];
   
     Index AgeIndex On Age [ Unique ];
   
     Property Sex As %String (DISPLAYLIST=",Woman,Man") [ Required ];
   }

   better

   Class packet.Actor Extends (%Persistent, %Populate)
   {
   
   Index NameIndex On (Name, Age) [ Unique ];
   
   Property Name As %Name [ Required ];
   
   Property Age As %Integer(MAXVAL = 100, MINVAL = 10) [ Required ];
   
   Property Sex As %String(VALUELIST = ",Woman,Man") [ Required ];
   
   }

   Otherwise it will not be possible to insert two or more persons with the same age. Or do you specifically want it?

2. Property Actor As list of packet.Actor (POPSPEC=".ActorFilter()") [Required];

   This code contradicts the documentation (Specifying the POPSPEC Parameter for List Properties):

   Leave basicspec empty if the property is a list of objects.

   However, in this case, ActorFilter() should return strictly one ID, not a collection.

3. The data in the Actor property is stored as $lb($lb(ID1),$lb(ID2),..,$lb(IDN)), therefore, to select all records that have ID=6 in this field, you need to perform

   select * from packet.Movie where $listbuild('6') %inlist Actor

It seems to me that in your case it would be easier to do so:

Property Actor As list Of packet.Actor(STORAGEDEFAULT = "array") [ Required ];

Then the query will be simplified:

select * from packet.Movie_Actor where Actor=6

Or even so

select * from packet.Movie where Movie_Actor->Actor=6

If we have a lot of votes will consider to add it.

On-my enough one moreover, that moderators (you and Dmitry) and engineers InterSystems (Eduard, Alexander, Anton, Sergey, Anastasia, Timur, etc.) - Russian-speaking.

I have one problem with it - if we introduce Russian Community, will you stop answering questions in English?)

1. I here like writing code more than words.
2. It will depend on who is asking.

1. Instead of

   Property Product As %String [ Required ];
   Property Item As %String [ Calculated, SqlComputeCode = { s {*}=$e({Product},1,5)}, SqlComputed ];
   

   it is better to use

   Property Product As %String [ Required ];
   Property Item As %String(MAXLEN = 5) [ Required, SqlComputeCode = { s {*}=$e({Product},1,5)}, SqlComputed, SqlComputeOnChange = Product ];
   

2. Check on the version 2017.2 can not, but checked on 2018.1

   SELECT * FROM Portal.ProductStats ps left JOIN  Portal.ProductCacheUpdates pcu ON (pcu.Item=ps.Item) WHERE ps.Item=?

   • ExtentSize=1 (Portal.ProductStats)
     ExtentSize=1000 (Portal.ProductCacheUpdates)

     
     Relative cost  = 1338
     ◾Read master map Portal.ProductStats.IDKEY, looping on ID.
     
     ◾For each row:
     
      Read index map Portal.ProductCacheUpdates.pcacheUpdsProd, using the given %SQLUPPER(Item), and looping on ID.
      For each row:
      Read master map Portal.ProductCacheUpdates.IDKEY, using the given idkey value.
      Generate a row padded with NULL for table Portal.ProductCacheUpdates if no row qualified.
      Output the row.

   • ExtentSize=1000 (Portal.ProductStats)
     ExtentSize=1 (Portal.ProductCacheUpdates)

     Relative cost  = 1219.2
     ◾Read index map Portal.ProductStats.pcacheUpds, using the given %SQLUPPER(Item), and looping on ID.
     
     ◾For each row:
     
      Read master map Portal.ProductStats.IDKEY, using the given idkey value.
      Read index map Portal.ProductCacheUpdates.pcacheUpdsProd, using the given %SQLUPPER(Item), and looping on ID.
      For each row:
      Read master map Portal.ProductCacheUpdates.IDKEY, using the given idkey value.
      Generate a row padded with NULL for table Portal.ProductCacheUpdates if no row qualified.
      Output the row.

     As you can see in both cases the index pcacheUpdsProd is used.

Have you really set up the tables and cleared the cached queries so that the optimizer can start using the new statistics?

Try to do it manually in the terminal:

blablabla>d $system.SQL.TuneSchema("Portal",1), $SYSTEM.SQL.Purge(), $system.Status.DisplayError($system.OBJ.CompilePackage("Portal","cu-d"))

The documentation describes in detail how and why.

For example, sometimes the optimizer decides not to use the index if a full crawl would be more efficient. It depends on many parameters: ExtentSize, Selectivity, etc.

Give here what Robert asks, and it will be possible to tell more precisely why.

&SQL(SELECT ID FROM Cinema.Film ORDER BY ID DESC)

similar to the query

&SQL(SELECT TOP ALL ID FROM Cinema.Film ORDER BY ID DESC)

&SQL(SELECT TOP ALL ID FROM Cinema.Film ORDER BY ID DESC)
and
&SQL(SELECT TOP 1 ID FROM Cinema.Film ORDER BY ID DESC)

are so significantly different.

The field TimeCreated is of type Ens.DataType.UTC.

Then so:

datepart('hh', %external(TimeCreated))

See Data Display Options

By default, the mac address of the computer on which the DBMS instance is running is returned. But you can get the mac address of any other computer, see getmac /?.

I checked for versions 2009.1/2010.1: unfortunately, the $zu(114,0) returns nothing, therefore, remains variant with the command line.

Example for Windows, provided that the system has a single network card:

#include %syConfig
 n result
 w $zu(144,1,$$$DEFETHADDR),!,"------",!
 
 d $system.OBJ.DisplayError(##class(%Net.Remote.Utility).RunCommandViaCPIPE("getmac /NH /fo table",,.result))
 w $p(result," ",1)

PS: for other OS command line may be different.

I know. After all the author requested the solution on pure M and not COS.

Thank you for your comment. This code is taken from the source code %SYS, which in theory should be an example for application developers.

I hope that InterSystems developers will see your comment and make appropriate changes.

Intel i5-2400

10000 digits ~ 58 sec.

calcPI(n) public { 
  s $lb(len,nines,predigit,r)=$lb(10*n\3,0,0,"")
  
  f i=1:1:len s a(i)=2
  
  f j=1:1:n {
    s q=0
    f i=len:-1:1 s x=10*a(i)+(qi), a(i)=x#(2i-1), q=x(2*i-1)
    s a(1)=q#10, q=q\10
    i q=9 {
      s nines=nines+1
    }elseif q=10 {
      s r=r(predigit+1)$$repeat^%qarfunc(0,nines), predigit=0, nines=0
    }else{
      s r=rpredigit, predigit=q
      s:nines r=r$$repeat^%qarfunc(9,nines), nines=0
    }
  }
  q r_predigit
}

Certainly.