New post
AllAccepted answers
Vitaliy Serdtsev · Jul 20, 2018

Can also speed up, abandoning the indirect:

  s:'$d(addr3) addr3(1)=1, (addr3(2),addr3(8),addr3(9))=289, addr3(3)=3
  
  d $case(addr3(value),1:t3case1,289:t3case289,3:t3case3,:t3case)
  ##; w !,r
  q  

t3case1
  s r="Case 1"
  q
t3case289
  s r="Case 2,8-9" q
t3case3
  s r="Case 3"
  q
t3case
  s r= "Case default"
  q

But the quickest option is if/elseif/else, since here is used inline-call, and not external-call.

Vitaliy Serdtsev · Jul 19, 2018

So, in order.

  1. There is no limit to perfection.

    Better then  d:(v=2)!(v=8)!(v=9) t3case289 q

  2. There are one error in the Test2 method:
      s:'$d(addr) addr(1)=1, (addr(2),addr(8),addr(9))=289, add(3)=3
  3. There are error in the Test3 method:
      d:v=1 t3case1 Q ;will work in any way
  d:v=2 t3case289 Q  d:v=8 t3case289 Q  d:v=9 t3case289 Q 
  d:v=3 t3case3 Q 
  d t3case Q 
  4. Speed will depend on many factors: RAM size, number of conditions (the more of them, the slower the lasts of them will be executed and more RAM is required).
Vitaliy Serdtsev · Jul 19, 2018

So, in order.

- The third article mentions a few options of which some are not available in Cache, such as LIMIT and OFFSET.

Caché has analogues - TOP N and %vid, which with more than replace the LIMIT/OFFSET. In the second link this is discussed in detail.

- And the 4th talks about using LIMIT and OFFSET which are, again, not available in Cache.

The essence of the article is to replace query

SELECT user_id, external_id, name, metadata, date_created
FROM users
ORDER BY user_id ASC
LIMIT 50 000 000, 10 000; --- 5 000th page * 10 000 page size

10 000 rows in set (40.81 sec)

to

SELECT user_id, external_id, name, metadata, date_created
FROM users
WHERE user_id > 51 234 123 --- value of user_id for 50 000 000th record
ORDER BY user_id ASC
LIMIT 10 000

10 000 rows in set (0.03 sec)
Eventhough I am only asking for the first 100, there is still a major performance hit when executing the first rset.%Next() due to, what I assume is, the code trying to find the 100 records I am requesting out of the 1 mil records.

In this case, the following query is sufficient:

SELECT TOP 100 prop FROM table WHERE prop=?
-The second suggestion is what I am doing above. Using %VID with a subquery is just too slow when dealing with large datasets.

Is there an index for "prop"?

You tuned the table?

I have all works quickly:

Class dc.test Extends %Persistent
{

Index iprop On prop;

Property prop As %String;

ClassMethod Fill(
  Nrow = {1e6},
  Npage = {1e3})
{
  d DISABLE^%NOJRN
    ,..%KillExtent()
  
  s time=$zh
    ,^dc.testD=Nrow
  
  f id=1:1:Nrow {
    s p=id-1\Npage+1
      ,v=id-1#Npage+1
      ,val=p_":"_v
      ,^dc.testD(id)=$lb("",val)
      ,^dc.testI("iprop",$$$SQLUPPER(val),id)=""
  }
  
  w "(Fill) time = ",$zh-time," s.",!!
  
  zw:Nrow<=50 ^dc.testD,^dc.testI
  
  d ENABLE^%NOJRN
    ,$system.SQL.TuneTable($classname(),$$$YES)
    ,$system.OBJ.Compile($classname(),"cu-d")
}

ClassMethod Query(
  q As %TinyInt = 1,
  prop As %String,
  rownum1 As %Integer,
  rownum2 As %Integer)
{
  s sql(1)="select *,%vid from (select %ID,prop from dc.test where prop %startswith ?) where %vid between ? and ?"
    ,sql(2)="select *,%vid from (select %ID,prop from %ignoreindex iprop dc.test where prop like ?) where %vid between ? and ?"
    ,sql(3)="select *,%vid from (select top ? %ID,prop from %ignoreindex iprop dc.test where prop like ? order by %ID desc) order by %vid desc"

    ,time=$zh
    ,rs=$s(q=3:##class(%SQL.Statement).%ExecDirect(,sql(q),rownum1,prop),
             1:##class(%SQL.Statement).%ExecDirect(,sql(q),prop,rownum1,rownum2))
    
  i 'rs.%SQLCODE {
    while rs.%Next() {
      /*
      s id=rs.%Get("ID")
        ,prop=rs.%Get("prop")
      w id," ",prop,!
      */
      d rs.%Print()
    }
  }
  w "(",$lts($lb(q,prop,rownum1,rownum2)),") time = ",$zh-time," s.",!!
}

/// d ##class(dc.test).Test()
ClassMethod Test()
{
  d ..Fill()
    ,..Query(1,"1000:",111,111+16)
    ,..Query(2,"%12%",111,111+16)
    ,..Query(1,"1",111984,111984+16)

    ,..Query(2,"%12%",39584,39584+16) ;# slow (last 17)
    ,..Query(3,"%12%",17,"")          ;# fast (last 17)
}
}

Result:

USER>d ##class(dc.test).Test()
(Fill) time = 1.277645 s.
 
999111 1000:111 111
999112 1000:112 112
999113 1000:113 113
999114 1000:114 114
999115 1000:115 115
999116 1000:116 116
999117 1000:117 117
999118 1000:118 118
999119 1000:119 119
999120 1000:120 120
999121 1000:121 121
999122 1000:122 122
999123 1000:123 123
999124 1000:124 124
999125 1000:125 125
999126 1000:126 126
999127 1000:127 127
(1,1000:,111,127) time = .084489 s.
 
5128 6:128 111
5129 6:129 112
5212 6:212 113
5312 6:312 114
5412 6:412 115
5512 6:512 116
5612 6:612 117
5712 6:712 118
5812 6:812 119
5912 6:912 120
6012 7:12 121
6112 7:112 122
6120 7:120 123
6121 7:121 124
6122 7:122 125
6123 7:123 126
6124 7:124 127
(2,%12%,111,127) time = .091251 s.
 
999984 1000:984 111984
999985 1000:985 111985
999986 1000:986 111986
999987 1000:987 111987
999988 1000:988 111988
999989 1000:989 111989
999990 1000:990 111990
999991 1000:991 111991
999992 1000:992 111992
999993 1000:993 111993
999994 1000:994 111994
999995 1000:995 111995
999996 1000:996 111996
999997 1000:997 111997
999998 1000:998 111998
999999 1000:999 111999
1000000 1000:1000 112000
(1,1,111984,112000) time = .66504 s.
 
999121 1000:121 39584
999122 1000:122 39585
999123 1000:123 39586
999124 1000:124 39587
999125 1000:125 39588
999126 1000:126 39589
999127 1000:127 39590
999128 1000:128 39591
999129 1000:129 39592
999212 1000:212 39593
999312 1000:312 39594
999412 1000:412 39595
999512 1000:512 39596
999612 1000:612 39597
999712 1000:712 39598
999812 1000:812 39599
999912 1000:912 39600
(2,%12%,39584,39600) time = 1.946264 s.
 
999121 1000:121 17
999122 1000:122 16
999123 1000:123 15
999124 1000:124 14
999125 1000:125 13
999126 1000:126 12
999127 1000:127 11
999128 1000:128 10
999129 1000:129 9
999212 1000:212 8
999312 1000:312 7
999412 1000:412 6
999512 1000:512 5
999612 1000:612 4
999712 1000:712 3
999812 1000:812 2
999912 1000:912 1
(3,%12%,17,) time = .089032 s.
Vitaliy Serdtsev · Jul 17, 2018

Use %MVR

For example:

Class dc.test Extends %Persistent
{

Index ibar On bar;

Property bar As %String(COLLATION = "MVR");

ClassMethod Test()
{
  d ..%KillExtent()
  &sql(insert into dc.test(bar)
  select '00123AB' union
  select '12345AB')

  d $system.SQL.TuneTable($classname(),$$$YES), $system.OBJ.Compile($classname(),"cu-d")
  f args="00123AB","123AB" d ##class(%SQL.Statement).%ExecDirect(,"select * from dc.test where bar=?",args).%Display() w !!
}

}

Result:

USER>d ##class(dc.test).Test()
ID      bar
1       00123AB
 
1 Rows(s) Affected
 
ID      bar
1       00123AB
 
1 Rows(s) Affected
Vitaliy Serdtsev · Jul 17, 2018

test.mac:

#include %systemInclude

  #define getServState(%serviceName) ##class(%Activate.GenericObject).GetObject("winmgmts:\\.\root\cimv2").InvokeMethod1("Get",$$$FormatText("Win32_Service.Name=%1",$$$quote(%serviceName))).GetProperty("State")

  f s="wudfsvc","WSearch" w s,": ",$$$getServState(s),!

Result:

USER>d ^test
wudfsvc: Stopped
WSearch: Running
Vitaliy Serdtsev · Jul 6, 2018

Example of converting a CSV to JSON file and then reading from a JSON file without creating intermediate classes/tables:

  ; CSV -> JSON

  s rowtype = "name VARCHAR(50),year INTEGER,amount NUMERIC(9,2),date DATE",
    fileIn = "C:\Temp\import.csv",
    fileOut = "C:\Temp\export.json",
    fileExp=##class(%Stream.FileBinary).%New(),
    obj=##class(%Document.Object).%New()

  d obj.loadResultSet(##class(%SQL.Statement).%ExecDirect(,"call %SQL_Util.CSV(,?,?)",.rowtype,.fileIn),"res"),
    fileExp.LinkToFile(fileOut),
    obj.%ToJSONStream(fileExp),
    fileExp.%Save()

  ; Read JSON

  s fileImp=##class(%Stream.FileBinary).%New()
  d fileImp.LinkToFile(fileOut)
  s obj = ##class(%DynamicAbstractObject).%FromJSON(fileImp)
  d obj.%ToJSON()
  w !,"obj.res.%Size() = ",obj.res.%Size()
    ,!,obj.res."0"."""date"""

Result:

C:\Temp\import.csv:
car,2000,100.51,27.10.2016,
phone,2003,65.8,15.01.2017,

USER>d ^test
{"res":[{"\"date\"":"27.10.2016","amount":100.51,"name":"car","year":2000},{"\"date\"":"15.01.2017","amount":65.8,"name":"phone","year":2003}]}
obj.res.%Size() = 2
27.10.2016