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(addraddr(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 
    
  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).

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

ClassMethod Query(
  As %TinyInt 1,
  prop As %String,
  rownum1 As %Integer,
  rownum2 As %Integer)
{
  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))
    
  'rs.%SQLCODE {
    while rs.%Next() {
      /*
      s id=rs.%Get("ID")
        ,prop=rs.%Get("prop")
      w id," ",prop,!
      */
      rs.%Print()
    }
  }
  "(",$lts($lb(q,prop,rownum1,rownum2)),") time = ",$zh-time," s.",!!
}

/// d ##class(dc.test).Test()
ClassMethod Test()
{
  ..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>##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.

Use %MVR

For example:

Class dc.test Extends %Persistent
{

Index ibar On bar;

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

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

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

}

Result:

USER>##class(dc.test).Test()
ID      bar
1       00123AB
 
1 Rows(s) Affected
 
ID      bar
1       00123AB
 
1 Rows(s) Affected

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" s,": ",$$$getServState(s),!

Result:

USER>^test
wudfsvc: Stopped
WSearch: Running

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

  ; CSV -> JSON

  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()

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

  ; Read JSON

  fileImp=##class(%Stream.FileBinary).%New()
  fileImp.LinkToFile(fileOut)
  obj ##class(%DynamicAbstractObject).%FromJSON(fileImp)
  obj.%ToJSON()
  !,"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>^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

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;
      borderdashed 2px #ccc;
      background-color: #fefefe;
      color: #ccc;
      text-aligncenter;
      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 = 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>

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()
{
  ..%KillExtent()
  
  &sql(insert into dc.test default values)
  zw ^dc.testD
  
  !
  
  ; %SelectMode = DISPLAY
  ##class(%SQL.Statement).%ExecDirect(##class(%SQL.Statement).%New(2),"select * from dc.test").%Display($c(9,9)) !

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

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

Result:

USER>##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().

Useful links:

  1. SQL and NLS Collations
  2. Collations in Caché

This issue dares relatively simply:

  • Create the required collation tables. Fortunately, for some languages, they are already included and can be taken as a basis. In complex cases, you can contact to WRC.
    Check their list and status:
    USER>zn "%SYS"
     
    %SYS>^COLLATE
     
    Status       Number   Abbrev   Name
    ----------   ------   ------   ----------------------
    Built-in        0     OANS     ISM Pre-6.2
    Built-in        1     ANSI     ISM 6.2->6.4
    Built-in        2     COBR     Ipsum/Cobra
    Built-in        3     DTMC     DTM-compatible
    Built-in        4     CBR2     Ipsum/Cobra-2
    Built-in        5     UNIC     Cache standard
    Not loaded     10     GER1     German1
    Not loaded     11     POR1     Portuguese1
    Not loaded     12     POL1     Polish1
    Not loaded     13     GER2     German2
    Not loaded     14     SPA1     Spanish1
    Not loaded     15     DAN1     Danish1
    Available      16     CYR1     Cyrillic1
    Not loaded     17     GRE1     Greek1
    Not loaded     18     CZE1     Czech1
    Not loaded     19     CZE2     Czech2
    Not loaded     20     POR2     Portuguese2
    Not loaded     21     FIN1     Finnish1
    Not loaded     22     JAP1     Japanese1
    Not loaded     24     POL2     Polish2
    Not loaded     27     FRE1     French1
    Not loaded     28     FIN2     Finnish2
    Available      29     HUN1     Hungarian1
    Available      30     GER3     German3
    Not loaded     31     POL3     Polish3
    Not loaded     32     SPA2     Spanish2
    Not loaded     33     DAN2     Danish2
    Not loaded     34     GRE2     Greek2
    Not loaded     35     FIN3     Finnish3
    Not loaded     36     LIT1     Lithuanian1
    Available      37     CYR3     Cyrillic3
    Not loaded     38     SLO1     Slovenian1
    Not loaded     39     SLO2     Slovenian2
    Not loaded     40     TUR1     Turkish1
    Not loaded     41     DAN3     Danish3
    Available      42     UKR1     Ukrainian1
    Available      43     CYR4     Cyrillic4
    Not loaded     44     CZE3     Czech3
    Not loaded     46     MAL1     Maltese1
    Not loaded     48     MAL2     Maltese2
    Not loaded     49     SPA4     Spanish4
    Not loaded     50     SLO1     Slovak1
    Not loaded     51     SPA5     Spanish5
    Not loaded     52     FIN4     Finnish4
    Not loaded     53     CZE4     Czech4
    Built-in      128     OSTR     ISM Pre-6.2 string
    Built-in      129     NSTR     ISM 6.2->6.4 string
    Built-in      133     USTR     Cache standard string
    Available     144     CYR1S    Cyrillic1 string
    Available     157     HUN1S    Hungarian1 string
    Available     158     GER3S    German3 string
    Available     165     CYR3S    Cyrillic3 string
    Available     170     UKR1S    Ukrainian1 string
    Available     171     CYR4S    Cyrillic4 string
    
  • load the new collation tables to your current locale
  • for Storage make mapping your indexes in separate globals with own collation
  • Profit!

Here are two small examples:

  1. Class dc.test1 Extends %Persistent
    {
    
    Index iDE On DescDE As EXACT;
    
    Index iHU On DescHU As EXACT;
    
    Index iRU On DescRU As EXACT;
    
    Property DescDE As %String;
    
    Property DescHU As %String;
    
    Property DescRU As %String;
    
    ClassMethod LoadColTables() [ Private ]
    {
      r=$$$NO
      
      try {
        $$$AddAllRoleTemporaryInTry
        n $namespace
      
        s $namespace="%SYS"
        
        $$$ThrowOnError(##class(Config.NLS.Tables).Load("deuw","COL","German3"))
        $$$ThrowOnError(##class(Config.NLS.Tables).Load("hunw","COL","Hungarian1"))
        $$$ThrowOnError(##class(Config.NLS.Tables).Load("rusw","COL","Cyrillic4"))
        
        ;d ^COLLATE ; for check only
    
        r=$$$YES
      }catch(ex{
        ex.DisplayString(),!
      }
    
      r
    }
    
    /// d ##class(dc.test1).Test()
    ClassMethod Test()
    {
      q:'..LoadColTables()
      
      ..%KillExtent()
      
      &sql(insert into dc.test1(DescDE,DescHU,DescRU)
      select 'Straßer','csak','Прохор' union all
      select 'Straster','comb','Павел' union all
      select 'Strasser','cukor','Пётр')
      
      zw ^dc.test1D
      !,"---- Before (for all indexes by default = 'Cache standard') --------",!!
      print
      
      !,"---- After (each index has its own collation) --------",!!
      ..%PurgeIndices()
      
      d $system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test1.iDE",##class(%Collate).DisplayToLogical("German3")))
       ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test1.iHU",##class(%Collate).DisplayToLogical("Hungarian1")))
       ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test1.iRU",##class(%Collate).DisplayToLogical("Cyrillic4")))
       ,..%BuildIndices(,$$$NO,$$$YES,$$$NO)
       ,$system.SQL.TuneTable($classname(),$$$YES)
       ,$system.OBJ.Compile($classname(),"cu-d")
       ,print
    
      q
    
    print  
      zw ^dc.test1.iDE,^dc.test1.iHU,^dc.test1.iRU
      !
      lng="DE","HU","RU" ##class(%SQL.Statement).%ExecDirect(,$$$FormatText("select %ID,Desc%1 from dc.test1 order by %exact(Desc%1)",lng)).%Display() !!
      q
    }
    
    Storage Default
    {
    <Data name="test1DefaultData">
    <Value name="1">
    <Value>%%CLASSNAME</Value>
    </Value>
    <Value name="2">
    <Value>DescDE</Value>
    </Value>
    <Value name="3">
    <Value>DescHU</Value>
    </Value>
    <Value name="4">
    <Value>DescRU</Value>
    </Value>
    </Data>
    <DataLocation>^dc.test1D</DataLocation>
    <DefaultData>test1DefaultData</DefaultData>
    <IdLocation>^dc.test1D</IdLocation>
    <Index name="iDE">
    <Location>^dc.test1.iDE</Location>
    </Index>
    <Index name="iHU">
    <Location>^dc.test1.iHU</Location>
    </Index>
    <Index name="iRU">
    <Location>^dc.test1.iRU</Location>
    </Index>
    <IndexLocation>^dc.test1I</IndexLocation>
    <StreamLocation>^dc.test1S</StreamLocation>
    <Type>%Library.CacheStorage</Type>
    }
    
    }

    Result:

    USER>##class(dc.test1).Test()
    ^dc.test1D=3
    ^dc.test1D(1)=$lb("","Straßer","csak","Прохор")
    ^dc.test1D(2)=$lb("","Straster","comb","Павел")
    ^dc.test1D(3)=$lb("","Strasser","cukor","Пётр")
     
    ---- Before (for all indexes by default = 'Cache standard') --------
     
    ^dc.test1.iDE("Strasser",3)=""
    ^dc.test1.iDE("Straster",2)=""
    ^dc.test1.iDE("Straßer",1)=""
    ^dc.test1.iHU("comb",2)=""
    ^dc.test1.iHU("csak",1)=""
    ^dc.test1.iHU("cukor",3)=""
    ^dc.test1.iRU("Павел",2)=""
    ^dc.test1.iRU("Прохор",1)=""
    ^dc.test1.iRU("Пётр",3)=""
     
    ID      DescDE
    3       Strasser
    2       Straster
    1       Straßer
     
    3 Rows(s) Affected
     
    ID      DescHU
    2       comb
    1       csak
    3       cukor
     
    3 Rows(s) Affected
     
    ID      DescRU
    2       Павел
    1       Прохор
    3       Пётр
     
    3 Rows(s) Affected
     
     
    ---- After (each index has its own collation) --------
     
    ^dc.test1.iDE("Strasser",3)=""
    ^dc.test1.iDE("Straßer",1)=""
    ^dc.test1.iDE("Straster",2)=""
    ^dc.test1.iHU("comb",2)=""
    ^dc.test1.iHU("cukor",3)=""
    ^dc.test1.iHU("csak",1)=""
    ^dc.test1.iRU("Павел",2)=""
    ^dc.test1.iRU("Пётр",3)=""
    ^dc.test1.iRU("Прохор",1)=""
     
    ID      DescDE
    3       Strasser
    1       Straßer
    2       Straster
     
    3 Rows(s) Affected
     
    ID      DescHU
    2       comb
    3       cukor
    1       csak
     
    3 Rows(s) Affected
     
    ID      DescRU
    2       Павел
    3       Пётр
    1       Прохор
     
    3 Rows(s) Affected
  2. Class dc.test2 Extends %Persistent
    {
    
    Index iDE On (Lng, Descr As EXACT);
    
    Index iHU On (Lng, Descr As EXACT);
    
    Index iRU On (Lng, Descr As EXACT);
    
    Property Lng As %String;
    
    Property Descr As %String;
    
    /// d ##class(dc.test2).Test()
    ClassMethod Test()
    {
      ..%KillExtent()
       ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test2.iDE",##class(%Collate).DisplayToLogical("German3")))
       ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test2.iHU",##class(%Collate).DisplayToLogical("Hungarian1")))
       ,$system.OBJ.DisplayError(##class(%GlobalEdit).Create(,"dc.test2.iRU",##class(%Collate).DisplayToLogical("Cyrillic4")))
    
      &sql(insert into dc.test2(Lng,Descr)
      select 'DE''Straßer' union
      select 'DE''Straster' union
      select 'DE''Strasser' union
      select 'HU''csak' union
      select 'HU''comb' union
      select 'HU''cukor' union
      select 'RU''Прохор' union
      select 'RU''Павел' union
      select 'RU''Пётр')
      
      zw ^dc.test2D,^dc.test2.iDE,^dc.test2.iHU,^dc.test2.iRU
      
      d $system.SQL.TuneTable($classname(),$$$YES)
       ,$system.OBJ.Compile($classname(),"cu-d")
       ,print
    
      q
    
    print  
      !
      lng="DE","HU","RU" {
        idx=$replace("iDE,iHU,iRU","i"_lng,"_i"_lng)
        ##class(%SQL.Statement).%ExecDirect(,$$$FormatText("select * from %IGNOREINDEX %1 dc.test2 where Lng=? order by %exact(Descr)",idx),lng).%Display()
        !!
      }
      q
    }
    
    Storage Default
    {
    <Data name="test2DefaultData">
    <Value name="1">
    <Value>%%CLASSNAME</Value>
    </Value>
    <Value name="2">
    <Value>Lng</Value>
    </Value>
    <Value name="3">
    <Value>Descr</Value>
    </Value>
    </Data>
    <DataLocation>^dc.test2D</DataLocation>
    <DefaultData>test2DefaultData</DefaultData>
    <IdLocation>^dc.test2D</IdLocation>
    <Index name="iDE">
    <Location>^dc.test2.iDE</Location>
    </Index>
    <Index name="iHU">
    <Location>^dc.test2.iHU</Location>
    </Index>
    <Index name="iRU">
    <Location>^dc.test2.iRU</Location>
    </Index>
    <IndexLocation>^dc.test2I</IndexLocation>
    <StreamLocation>^dc.test2S</StreamLocation>
    <Type>%Library.CacheStorage</Type>
    }
    
    }

    Result:

    USER>##class(dc.test2).Test()
    ^dc.test2D=9
    ^dc.test2D(1)=$lb("","DE","Straßer")
    ^dc.test2D(2)=$lb("","DE","Straster")
    ^dc.test2D(3)=$lb("","DE","Strasser")
    ^dc.test2D(4)=$lb("","HU","csak")
    ^dc.test2D(5)=$lb("","HU","comb")
    ^dc.test2D(6)=$lb("","HU","cukor")
    ^dc.test2D(7)=$lb("","RU","Прохор")
    ^dc.test2D(8)=$lb("","RU","Павел")
    ^dc.test2D(9)=$lb("","RU","Пётр")
    ^dc.test2.iDE(" DE","Strasser",3)=""
    ^dc.test2.iDE(" DE","Straßer",1)=""
    ^dc.test2.iDE(" DE","Straster",2)=""
    ^dc.test2.iDE(" HU","comb",5)=""
    ^dc.test2.iDE(" HU","csak",4)=""
    ^dc.test2.iDE(" HU","cukor",6)=""
    ^dc.test2.iDE(" RU","Павел",8)=""
    ^dc.test2.iDE(" RU","Прохор",7)=""
    ^dc.test2.iDE(" RU","Пётр",9)=""
    ^dc.test2.iHU(" DE","Strasser",3)=""
    ^dc.test2.iHU(" DE","Straster",2)=""
    ^dc.test2.iHU(" DE","Straßer",1)=""
    ^dc.test2.iHU(" HU","comb",5)=""
    ^dc.test2.iHU(" HU","cukor",6)=""
    ^dc.test2.iHU(" HU","csak",4)=""
    ^dc.test2.iHU(" RU","Павел",8)=""
    ^dc.test2.iHU(" RU","Прохор",7)=""
    ^dc.test2.iHU(" RU","Пётр",9)=""
    ^dc.test2.iRU(" DE","Strasser",3)=""
    ^dc.test2.iRU(" DE","Straster",2)=""
    ^dc.test2.iRU(" DE","Straßer",1)=""
    ^dc.test2.iRU(" HU","comb",5)=""
    ^dc.test2.iRU(" HU","csak",4)=""
    ^dc.test2.iRU(" HU","cukor",6)=""
    ^dc.test2.iRU(" RU","Павел",8)=""
    ^dc.test2.iRU(" RU","Пётр",9)=""
    ^dc.test2.iRU(" RU","Прохор",7)=""
     
    ID      Descr   Lng
    3       Strasser        DE
    1       Straßer DE
    2       Straster        DE
     
    3 Rows(s) Affected
     
    ID      Descr   Lng
    5       comb    HU
    6       cukor   HU
    4       csak    HU
     
    3 Rows(s) Affected
     
    ID      Descr   Lng
    8       Павел   RU
    9       Пётр    RU
    7       Прохор  RU
     
    3 Rows(s) Affected
    

Important: if you remove "As EXACT" in the index, the examples will not work correctly for the German language, because: Capital ẞ

Try so:

Class dc.test Extends %ZEN.Component.page
{

XData Contents [ XMLNamespace "http://www.intersystems.com/zen" ]
{
  <page xmlns="http://www.intersystems.com/zen">
    <button id="btn1" caption="Test Server"/>
    <button id="btn2" caption="Test Client"/>
  </page>
}

ClassMethod SrvTest() [ ZenMethod ]
{
  &js<zenAlert('from Server');>
}

ClientMethod test() [ Language = javascript ]
{
  zenAlert('from Client');
}

ClientMethod onloadHandler() [ Language = javascript ]
{
  document.getElementById('btn1').addEventListener('click',new Function('evt','return zenPage.SrvTest();'),false);
  document.getElementById('btn2').addEventListener('click',new Function('evt','return zenPage.test();'),false);
}
}