New post
AllAccepted answers
Vitaliy Serdtsev · Aug 1, 2023

Thanks for the comment. I tested only on the provided data. The second option requires improvement.

 
size = 72
 
size = 69
Vitaliy Serdtsev · Jul 19, 2023 
Query FilterBy(
  Name As %String = "",
  Age As %Integer = "") As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_Filter_By, SqlProc ]
{
SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
(:Name IS NULL or Name %STARTSWITH :Name)
AND
(:Age IS NULL or Age >= :Age)
}
This kind of query ends up preventing Caché SQL compiler from optimizing using index based on each of the criteria made optional.

What is your conclusion based on?
If you check the plans of this query for different combinations of parameters, then the corresponding indexes are used (it is assumed that the table was previously configured via TuneTable).

select * from Sample.SP_Sample_Filter_By('s')


SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
('s' IS NULL or Name %STARTSWITH 's')
AND
(NULL IS NULL or Age >= NULL)
select * from Sample.SP_Sample_Filter_By(,47)


SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
(NULL IS NULL or Name %STARTSWITH NULL)
AND
(47 IS NULL or Age >= 47)
select * from Sample.SP_Sample_Filter_By('s',47)


SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
('s' IS NULL or Name %STARTSWITH 's')
AND
(47 IS NULL or Age >= 47)
Vitaliy Serdtsev · Jul 19, 2023

Did a little test:

Class dc.test [ Abstract ]
{

ClassMethod GetObjectFieldValue(
  pObject As %DynamicAbstractObject,
  pField As %String) As %String
{
  quit:pObject="" ""
  quit:pField="" "" set tResponse=""
  set tResponse = pObject.%Get(pField)
  return tResponse
}

ClassMethod Test()
{
  s p1={
    "priority": "2",
    "lastmodifieduser": "PORTAL",
    "assignedto": "jdoe01 STAFF",
    "status": "REVIEW",
    "documentclass": "PATIENTCASE",
    "versiontoken": "937486",
    "departmentid": "999",
    "patientcaseid": "105555424",
    "providerusername": "jdoe01",
    "internalnote": "firstMessage",
    "subject": "first123",
    "patientid": 9877654321,
    "createduser": "PORTAL",
    "description": "patient case - first123",
    "documentroute": "MDP",
    "documentsubclass": "PATIENTCASE_CLINICALQUESTION",
    "documentsource": "PORTAL",
    "createddate": "07/17/2023",
    "lastmodifieddate": "07/17/2023"}
    
  s p2="documentsubclass"
  w ..GetObjectFieldValue(p1,p2)
}

}

Result:

USER>d ##class(dc.test).Test()
PATIENTCASE_CLINICALQUESTION
Vitaliy Serdtsev · Mar 31, 2023

I wrote a small test on VBS, but you can easily redo this code for your language. For details on working with streams for ADO, see the documentation.

 
Code on Visual Basic Script
Vitaliy Serdtsev · Mar 9, 2023

In my opinion, the documentation clearly explains the characteristics of such a property and its purpose, for example:

  • To specify that this property has the characteristics of a multidimensional array
  • That is, the property is also automatically Transient.
  • Multidimensional properties are rare but provide a useful way to temporarily contain information about the state of an object.
Vitaliy Serdtsev · Mar 7, 2023 
CREATE PROCEDURE DeleteSpecimenTwo
  (IN Specno VARCHAR(50))
LANGUAGE OBJECTSCRIPT
{
NEW SQLCODE,%ROWID,%ROWCOUNT
#SQLCOMPILE SELECT=LOGICAL

&sql(DELETE FROM PCONT WHERE PSPECNO = :Specno)
If $Get(%sqlcontext)'=$$$NULLOREF Set %sqlcontext.%SQLCODE=SQLCODE,%sqlcontext.%ROWCOUNT=%ROWCOUNT
Quit:SQLCODE<0

&sql(DELETE FROM PSPEC WHERE PSPECNO = :Specno)
If $Get(%sqlcontext)'=$$$NULLOREF Set %sqlcontext.%SQLCODE=SQLCODE,%sqlcontext.%ROWCOUNT=%ROWCOUNT
}
Vitaliy Serdtsev · Feb 20, 2023 
#include %occInclude

n alg,algInfo

f alg="RSA-OAEP","A256KW","A256CBC-HS512","A256GCM" {
  s algInfo=##class(%Net.JSON.JWA).GetAlgInfo(alg)
  w alg," = ",$s(algInfo'="":algInfo.%ToJSON(),1:$system.Status.GetErrorText($$$ERROR($$$UnsupportedJOSEAlg,alg))),!
}

Output:

RSA-OAEP = {"alg":"RSA-OAEP","kty":"RSA","use":"enc","key_ops":["wrapKey","unwrapKey"],"keysize":2048,"hashsize":0}
A256KW = {"alg":"A256KW","kty":"oct","use":"enc","key_ops":["wrapKey","unwrapKey"],"keysize":256,"hashsize":0}
A256CBC-HS512 = {"alg":"A256CBC-HS512","kty":"oct","use":"enc","key_ops":["encrypt","decrypt"],"keysize":256,"hashsize":512}
A256GCM = Error #9723: Unsupported JOSE algorithm: A256GCM

Since this algorithm is not supported, it remains either to try to modify the GetAlgInfo method or to work directly with the OpenSSL library or similar.

Vitaliy Serdtsev · Feb 17, 2023

Try working directly with the private key file, for example:

#include %msql

s f=##class(%Stream.FileBinary).%New()
s f.Filename="С:\your_private_key.pem"
s privateKey=f.Read($$$MaxLocalLength)
 
s myString  = "text to sign"
s signedTxt = ##class(%SYSTEM.Encryption).RSASHASign(256, $zcvt(myString,"O","UTF8"), privateKey)
zw signedTxt

This code works for me.

Vitaliy Serdtsev · Feb 13, 2023

There are a couple of points:

  • could you please publish the source code of the class, and not the SQL script? The fact is that the command 
    CREATE INDEX BITMAP_INDEX_ST_SEARCH_VISIT_BLOOD_TYPE_CODE ON SQLUser.ST_SEARCH_VISIT (BLOOD_TYPE_CODE)
    creates a regular index, not a bitmap index as expected. I'm afraid my code may not match yours.
  • I created a test class with test data on Caché 2018.x and IRIS 2023.1FT and I have your query executed very quickly. Details below.
 
User.STSEARCHVISIT.cls

Generating 6000000 records

USER>d ##class(User.STSEARCHVISIT).Fill(6e6)
OK
explain select count(distinct by(VISIT_SERIAL_NO,HQ_ORG_CODE) 1) from ST_SEARCH_VISIT

<plans>
  <plan>
    <sql>
      select count(distinct by(VISIT_SERIAL_NO,HQ_ORG_CODE) 1) from ST_SEARCH_VISIT /*#OPTIONS {"DynamicSQL":1} */
    </sql>
    <cost value="29794316"/>
    Call module B. Output the row.
    <module name="B" top="1">
      Read index map ST_SEARCH_VISIT.IdxVisitorg, looping on %SQLUPPER(VISIT_SERIAL_NO), %SQLUPPER(HQ_ORG_CODE), and ID. For each row: Check distinct values for %SQLUPPER(HQ_ORG_CODE) and %SQLUPPER(VISIT_SERIAL_NO) using a temp-file. For each distinct row: Accumulate the count([value]).
    </module>
  </plan>
</plans>

select count(distinct by(VISIT_SERIAL_NO,HQ_ORG_CODE) 1) from ST_SEARCH_VISIT

Row count: 1 Performance: 2.8704 seconds 6003078 global references 36016391 lines executed

explain select count(1) from (select distinct by (VISIT_SERIAL_NO,HQ_ORG_CODE) VISIT_SERIAL_NO,HQ_ORG_CODE  from ST_SEARCH_VISIT)

<plans>
  <plan>
    <sql>
      select count(1) from (select distinct by (VISIT_SERIAL_NO,HQ_ORG_CODE) VISIT_SERIAL_NO,HQ_ORG_CODE from ST_SEARCH_VISIT) /*#OPTIONS {"DynamicSQL":1} */
    </sql>
    <cost value="21000"/>
    Call module B. Output the row.
    <module name="B" top="1">
      Read index map ST_SEARCH_VISIT.IdxVisitorg, looping on %SQLUPPER(VISIT_SERIAL_NO) and %SQLUPPER(HQ_ORG_CODE). For each row: Call module I. Accumulate the count([value]).
    </module>
    <module name="I" top="1">
      Read index map ST_SEARCH_VISIT.IdxVisitorg, using the given %SQLUPPER(VISIT_SERIAL_NO) and %SQLUPPER(HQ_ORG_CODE), and looping on ID. For each row: Accumulate the [value].
    </module>
  </plan>
</plans>

select count(1) from (select distinct by (VISIT_SERIAL_NO,HQ_ORG_CODE) VISIT_SERIAL_NO,HQ_ORG_CODE from ST_SEARCH_VISIT)

Row count: 1 Performance: 0.0269 seconds 3079 global references 17711 lines executed