User bio
404 bio not found
Member since May 31, 2017
Posts:
Replies:
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 Sample.Person
WHERE
(nvl(:Name,'')='' or Name %STARTSWITH :Name)
AND
(nvl(:Age,'')='' 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.
That´s why I followed Paul´s idea and came up with %SQLQuery´s subclass SmartSQLQuery found above which dynamically comments out each criteria which is not applicable.
What about this dynamic discarding of SQL criteria based on empty parameters extending %SQLQuery?
Class gen.SmartSQLQuery Extends %Library.SQLQuery
{
ClassMethod Func() As %SQL.StatementResult [ CodeMode = generator, ProcedureBlock = 1, ServerOnly = 1 ]
{
set %code=0
// don't generate any code if it not for a query
if %mode="method" quit $$$OK
// %mode is "propertymethod" for a valid query. We don't have any way currently to detect a misuse of a query class
if '$$$comMemberDefined(%class,$$$cCLASSquery,%property) quit $$$OK
// Reset the formal list to the query declaration:
$$$comSubMemberKeyGetLvar(formal,%class,$$$cCLASSquery,%property,$$$cQUERYmethod,%method,$$$cMETHformalspecparsed)
$$$comMemberKeyGetLvar(qformal,%class,$$$cCLASSquery,%property,$$$cQUERYformalspecparsed)
$$$comSubMemberKeySet(%class,$$$cCLASSquery,%property,$$$cQUERYmethod,%method,$$$cMETHformalspecparsed,formal_qformal)
Set glbArgList = formal_qformal
Set publicList = ""
For i=1:1:$ListLength(glbArgList) {
Set $Piece(publicList,",",i) = $List($List(glbArgList,i),1)
}
Set publicList = publicList _ "," _ "tStatement" _ "," _ "tResult"
$$$comSubMemberKeySet(%class,$$$cCLASSquery,%property,$$$cQUERYmethod,%method,$$$cMETHpubliclist,publicList)
set sc=$$SetOneQueryMeth^%occQuery(%class,%property,%method) quit:$$$ISERR(sc) sc
$$$comMemberKeyGetLvar(origin,%class,$$$cCLASSquery,%property,$$$cXXXXorigin)
$$$comMemberKeyGetLvar(query,%class,$$$cCLASSquery,%property,$$$cQUERYsqlquery)
// preparse the query to construct the actual argument list. If more than the supported number of arguments then revert to
// the non-dynamic option
set query = $zstrip(query,"<W")
set tLines = 0 for tPtr = 1:1:$Length(query,$$$NL) { set tLine = $Piece(query,$$$NL,tPtr) if tLine '= "" { set tLines = tLines + 1, tLines(tLines) = tLine } }
set sc=$$ExpandMacros^%SYS.DynamicQuery(%class,.tLines) QUIT:$$$ISERR(sc) sc
set SQLCODE = $$dynamic^%qaqpreparser(.tLines,.tStatementPreparsed,.tStatementArgs)
$$$GENERATE($Char(9)_"try {")
$$$GENERATE($Char(9,9)_"Set query = """_$replace(query,$$$NL,"""_$C(13,10)_""")_"""")
$$$GENERATE($Char(9,9)_"For i=1:1:$Length(query,$$$NL) {")
$$$GENERATE($Char(9,9,9)_"Set line=$Piece(query,$$$NL,i)")
$$$GENERATE($Char(9,9,9)_"If line?.E1"":""1.AN {")
$$$GENERATE($Char(9,9,9,9)_"Set var=$Piece($Piece(line,"":"",2),"" "",1)")
$$$GENERATE($Char(9,9,9,9)_"if @var="""" {")
$$$GENERATE($Char(9,9,9,9,9)_"Set $Piece(query,$$$NL,i) = ""-- ""_line")
$$$GENERATE($Char(9,9,9,9)_"}")
$$$GENERATE($Char(9,9,9)_"}")
$$$GENERATE($Char(9,9)_"}")
$$$GENERATE($Char(9,9)_"set tLines = 0 for tPtr = 1:1:$Length(query,$$$NL) { set tLine = $Piece(query,$$$NL,tPtr) if tLine '= """" { set tLines = tLines + 1, tLines(tLines) = tLine } }")
// $$$GENERATE($Char(9,9)_"set sc=$$ExpandMacros^%SYS.DynamicQuery(%class,.tLines) Throw:$$$ISERR(sc) ##class(%Exception.StatusException).ThrowIfInterrupt(sc)")
$$$GENERATE($Char(9,9)_"set SQLCODE = $$dynamic^%qaqpreparser(.tLines,.tStatementPreparsed,.tStatementArgs)")
$$$GENERATE($Char(9,9)_"//")
$$$GENERATE($Char(9,9)_"set tSelectMode = """_$Case($$$ucase(%parameter("SELECTMODE")), "RUNTIME": "", "ODBC": 1, "DISPLAY": 2, "LOGICAL": 0, : "")_"""")
$$$GENERATE($Char(9,9)_"if SQLCODE=0 && ($Listlength(tStatementArgs) < 361) && ($Length(tStatementPreparsed) < 40000) {")
$$$GENERATE($Char(9,9,9)_"set tExecuteArgs = """" for tPtr=1:2:$ListLength(tStatementArgs) { set tArg = $Case($List(tStatementArgs,tPtr),""?"":""$g(%parm(""_$Increment(qcount)_""))"",""c"":$$quoter^%qaqpreparser($List(tStatementArgs,tPtr+1)),""v"":""$g(""_$List(tStatementArgs,tPtr+1)_"")"",:"""") Set tExecuteArgs = tExecuteArgs _ "","" _ tArg }")
$$$GENERATE($Char(9,9,9)_"set tSchemaPath = ##class(%SQL.Statement).%ClassPath($classname())")
$$$GENERATE($Char(9,9,9)_"set tStatement = ##class(%SQL.Statement).%New(tSelectMode,tSchemaPath)")
$$$GENERATE($Char(9,9,9)_"do tStatement.prepare(tStatementPreparsed)")
$$$GENERATE($Char(9,9,9)_"Xecute ""set tResult = tStatement.%Execute(""_$Extract(tExecuteArgs,2,*)_"")""")
$$$GENERATE($Char(9,9)_"}")
$$$GENERATE($Char(9)_"}")
$$$GENERATE($Char(9)_"catch tException { if '$Isobject($Get(tResult)) { set tResult = ##class(%SQL.StatementResult).%New() } set tResult.%SQLCODE=tException.AsSQLCODE(),tResult.%Message=tException.AsSQLMessage() }")
$$$GENERATE($Char(9)_"Quit tResult")
QUIT $$$OK
}
}
Followers:
Rodrigo has no followers yet.
Following:
Rodrigo has not followed anybody yet.
Global Masters badges:
Rodrigo has no Global Masters badges yet.
You´re right, @Vitaliy Serdtsev
In my test I had used a particular huge table of people with non-default mapping (legacy globals) and with a very specific and custom name index for which nvl(:Name,'') = '' inhibited the index.
But with this plain :Name IS NULL it worked fine.
Thanks a lot!