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
Vitaliy Serdtsev · Jun 25, 2018

Try the following example of uploading multiple files at once, showing the progress for each file you upload (without form, submit, iframe, jQuery, flash, java, reloading/redrawing the page):

<!DOCTYPE html>
  <html>
  <head>
    <title>Upload multiple files using XMLHttpRequest</title>

    <style type="text/css">
    .ok {
      color:green;
    }

    #dropZone {
      width: 360px;
      height: 125px;
      border: dashed 2px #ccc;
      background-color: #fefefe;
      color: #ccc;
      text-align: center;
      padding: 125px 0 0 0;
    }
  </style>
    
  </head>
<body onload="onloadHandler()">
<div id="dropZone">Drag and drop files here or use the button below</div>
<div>
  Select files
  <input type="file" id="fileToUpload" onchange="fileSelected(document.getElementById('fileToUpload').files)" multiple="multiple" />
</div>
<button type="button" title="Clearing the queue" onclick="clearList();">Clear</button>
<button type="button" title="Upload files to the server" onclick="upFile();">Upload</button>
<div id="holder"></div>
<script language="javascript">
function clearList()
{
  fileQueue=[];
  document.getElementById('holder').innerHTML='';
  document.getElementById('fileToUpload').value='';
}

function fileSelected(files){
  var holder = document.getElementById('holder');
  for (var i = 0; i < files.length; i++) {
  
    var file = files[i];

    var fileSize = 0;
    if (file.size > 1024 * 1024)
      fileSize = (Math.round(file.size * 100 / (1024 * 1024)) / 100).toString() + 'Mbyte';
    else
      fileSize = (Math.round(file.size * 100 / 1024) / 100).toString() + 'Kbyte';
  
    var divInfo = document.createElement('div');
    divInfo.innerHTML=file.name+' ('+file.type+') - '+fileSize;
    holder.appendChild(divInfo);

    var divProgN = document.createElement('div');
    divProgN.id='progressNumber'+i;
    divProgN.innerHTML='%';
    holder.appendChild(divProgN);

    var prog = document.createElement('progress');
    prog.id='progressValue'+i;
    prog.max='100.0';
    prog.value='0';
    holder.appendChild(prog);

    fileQueue.push({i:i,file:file});
  }
}

function upFile()
{
  while (fileQueue.length > 0) {
    var item=fileQueue.pop();
    uploadFile(item.file,item.i);
  }
}

function onloadHandler() {
  if (typeof FileReader == "undefined") alert('Sorry, your browser does not support File API, so this demo will not work correctly');
  
  fileQueue = new Array();

  uploadFile = function (file, i) {
    var xhr = new XMLHttpRequest(), upload = xhr.upload, fd = new FormData();
    
    fd.append('fUpload', file);

    upload.addEventListener('progress',
    function (evt) {
      if (evt.lengthComputable) {
        var percentComplete = Math.round(evt.loaded * 100 / evt.total);

        document.getElementById('progressNumber'+i).innerHTML = percentComplete.toString() + '%';
        document.getElementById('progressValue'+i).value = percentComplete;
        
      }
      else {
        document.getElementById('progressNumber'+i).innerHTML = 'Cannot calculate';
      }
      
    }, false);
    upload.addEventListener('load', function (ev) {
      var c=document.getElementById('progressNumber'+i);
      c.className='ok';
      c.innerHTML='OK';
    }, false);
    upload.addEventListener('error', function (ev) {alert('An error occurred while trying to upload the file.');}, false);
    upload.addEventListener('abort', function (ev) {alert('The upload was cancelled by the user or the browser reset the connection.');}, false);
    xhr.open('POST',window.location.href);
    xhr.setRequestHeader('Cache-Control', 'no-cache');
    xhr.setRequestHeader('X-Requested-With', 'XMLHttpRequest');
    xhr.send(fd);
  }
  
  dropZone=document.getElementById('dropZone');
  dropZone.addEventListener('dragenter',  function(ev){
    ev.stopPropagation();
    ev.preventDefault();
  }, false);
  dropZone.addEventListener('dragleave',  function(ev){
    ev.stopPropagation();
    ev.preventDefault();
    this.style['backgroundColor'] = '#FEFEFE';
    this.style['borderColor'] = '#CCC';
    this.style['color'] = '#CCC';
  }, false);
  dropZone.addEventListener('dragover',  function(ev){
    ev.stopPropagation();
    ev.preventDefault();
    this.style['backgroundColor'] = '#F0FCF0';
    this.style['borderColor'] = '#3DD13F';
    this.style['color'] = '#3DD13F';
  }, false);
  dropZone.addEventListener('drop',  function(ev){
  ev.stopPropagation();
  ev.preventDefault();
  this.style['backgroundColor'] = '#FEFEFE';
  this.style['borderColor'] = '#CCC';
  this.style['color'] = '#CCC';
  fileSelected(ev.dataTransfer.files);
  }, false);
}
</script>
<script language="Cache" method="OnPreHTTP" arguments="" returntype="%Boolean">
  #dim stream As %CSP.BinaryStream=%request.GetMimeData("fUpload")
  i $IsObject(stream) {
    
    // make with the resulting file useful work
    
    ;s ^tmp($i(^tmp),"filename")=stream.FileName
    ;s ^tmp($i(^tmp),"filesize")=stream.Size
    
    q $$$NO
  }
  q $$$YES
</script>
</body>
</html>
Vitaliy Serdtsev · Jun 22, 2018

Use %UTC instead of %TimeStamp

In this case, all conversions will be performed automatically.

Simple example:

Class dc.test Extends %Persistent [ Final ]
{

Property CreationTime As %TimeStamp [ InitialExpression = {$ZDateTime($ZTimeStamp, 3, 1, 2)} ];

Property utc As %UTC [ InitialExpression = {##class(%UTC).NowUTC()} ];

/// d ##class(dc.test).Test()
ClassMethod Test()
{
  d ..%KillExtent()
  
  &sql(insert into dc.test default values)
  zw ^dc.testD
  
  w !
  
  ; %SelectMode = DISPLAY
  d ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(2),"select * from dc.test").%Display($c(9,9)) w !

  ; %SelectMode = ODBC
  d ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(1),"select * from dc.test").%Display($c(9,9)) w !

  ; %SelectMode = LOGICAL
  d ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(0),"select * from dc.test").%Display($c(9,9))
}
}

Result:

USER>d ##class(dc.test).Test()
^dc.testD=1
^dc.testD(1)=$lb("2018-06-22 14:58:11.61","2018-06-22 14:58:11.618") note that the database stores the same values
 
ID              CreationTime            utc
1               2018-06-22 14:58:11.61  2018-06-22 17:58:11.618
 
1 Rows(s) Affected
ID              CreationTime            utc
1               2018-06-22 14:58:11.61  2018-06-22 17:58:11.618
 
1 Rows(s) Affected
ID              CreationTime            utc
1               2018-06-22 14:58:11.61  2018-06-22 14:58:11.618
 
1 Rows(s) Affected

Similarly, you can play with the query in the Portal or any ODBC/JDBC client.

PS: don't forget about SQL Functions: %internal()/%external(), %odbcin()/%odbcout().