Hi everyone.
This is a full example how to use a %ScrollableResultSet for results pagination using %DynamicQuery:SQL and build a JSON response including page details.
Feel free to use, share, comment or improve it.
Kind Regards
ClassMethod getPersonsPag(iAge As %Integer, sortField As %String = 1, sortOrder As %String = 2, pageSize As %String = 20, pageIndex As %String = 1) As %DynamicObject
{
set out = []
set vFrom = ((pageIndex -1 ) * pageSize)+1
set vTo = vFrom + (pageSize-1)
set sql = "SELECT ID,SSN,LastName,GivenName,SecondaryName, Gender, Age "_
"FROM Sample.Person WHERE Age > ? "_
"Order By "_sortField _" "_ $CASE(sortOrder,1:"ASC",2:"DESC",:"ASC")
Set rs=##class(%ScrollableResultSet).%New("%DynamicQuery:SQL")
set sc = rs.Prepare(sql)
set sc = rs.Execute(iAge) If $$$ISERR(sc) Do DisplayError^%apiOBJ(sc) Quit
if (rs.Count()=0) Quit {"msg" : "No Records Found"}
Do rs.CurrRowSet(vFrom)
if pageSize >= rs.Count() set pageSize = rs.Count()
try{
FOR i=1:1:pageSize{
Do out.%Push({
"index": (i),
"pid": (rs.%Get("ID")),
"ssn" : (rs.%Get("SSN")),
"lastname" : (rs.%Get("LastName")) ,
"givenname": (rs.%Get("GivenName")),
"secondaryname": (rs.%Get("SecondaryName")) ,
"gender": (rs.%Get("Gender")),
"age": (rs.%Get("Age") )
})
Do rs.%Next()
}
}
catch(e){ }
set outJson = []
Do outJson.%Push({
"pageSize":(pageSize),
"pageIndex":(pageIndex),
"fromIndex":(vFrom),
"toIndex":(vFrom+i - 1),
"resultSetTotal":(rs.Count()),
"pageRecords":(i),
"pages":($NORMALIZE((rs.Count()/pageSize),0)),
"resultSet":(out)
})
return outJson
}
This is Usefull?
Hi Rubén,
Another proposition on IRIS 2021.1+ can be this one with the use of the new window (OVER) function :
ClassMethod getPersonsPagWindow(iAge As %Integer, sortField As %String = 1, sortOrder As %String = 2, pageSize As %String = 20, pageIndex As %String = 1) As %DynamicObject { set out = [] set vFrom = ((pageIndex -1 ) * pageSize)+1 set vTo = vFrom + (pageSize-1) set sql = "SELECT * "_ "FROM ( SELECT persons.* "_ " , ROW_NUMBER() OVER (ORDER By "_sortField_" "_ $CASE(sortOrder,1:"ASC",2:"DESC",:"ASC")_ " ) rn "_ " FROM Sample.Person persons where Age > ? "_ " ) tmp "_ "WHERE rn between "_vFrom_" and "_vTo_" "_ "ORDER By "_sortField_" "_ $CASE(sortOrder,1:"ASC",2:"DESC",:"ASC") Set rs=##class(%ResultSet).%New("%DynamicQuery:SQL") set sc = rs.Prepare(sql) set sc = rs.Execute(iAge) If $$$ISERR(sc) Do DisplayError^%apiOBJ(sc) Quit while rs.%Next() { Do out.%Push({ "pid": (rs.%Get("ID")), "ssn" : (rs.%Get("SSN")), "lastname" : (rs.%Get("LastName")) , "givenname": (rs.%Get("GivenName")), "secondaryname": (rs.%Get("SecondaryName")) , "gender": (rs.%Get("Gender")), "age": (rs.%Get("Age") ) }) } set outJson = [] Do outJson.%Push({ "pageSize":(pageSize), "pageIndex":(pageIndex), "fromIndex":(vFrom), "toIndex":(vTo), "resultSet":(out) }) return outJson }
I bench the two solutions on a dataset of 100 000 rows without index with a result of 20 elements on page 1 and here are the results :
"getPersonsPag timed : 1,647 secondes" "getPersonsPagWindow timed : 0,247 secondes"
I guess that the window function is faster because you don't have to fetch all the data in a global before paging.
That's Really Awesome! Thanks for the information!
The idea of a scrollable result set is to call
Save
/OpenId
- and the result set would continue on a next row automatically. So you don't need to manage to/from indices:Class User.Pagination { /// do ##class(User.Pagination).Time("NoSave") /// do ##class(User.Pagination).Time("Save") ClassMethod Time(method = "Save") { set start = $zh do $classmethod(,method) set end = $zh write $$$FormatText("%1 took %2 sec", method, $fnumber(end-start,"",4)) } /// do ##class(User.Pagination).NoSave() ClassMethod NoSave() { do { do $i(i) set obj = ..getPersonsPage(20,i) //w obj.%ToJSON(),!,! } while (obj.toIndex < obj.resultSetTotal) } /// do ##class(User.Pagination).Save() ClassMethod Save() { do { set obj = ..getPersonsPageSave(20,.id) //w obj.%ToJSON(),!,! } while (id'=-1) } ClassMethod getPersonsPage(pageSize As %String = 20, pageIndex As %String = 1) As %DynamicObject { #dim sc As %Status = $$$OK #dim rs As %ScrollableResultSet set sc = ..getRS(,.rs) quit:$$$ISERR(sc) {"msg": ($System.Status.GetErrorText(sc))} set vFrom = ((pageIndex -1 ) * pageSize) set vTo = vFrom + (pageSize-1) do rs.CurrRowSet(vFrom) set results = [] set:(pageSize >= rs.Count()) pageSize = rs.Count() set i = 0 while rs.%Next() && $i(i) { quit:(i>pageSize) do results.%Push({ "index": (i), "pid": (rs.%Get("ID")), "ssn" : (rs.%Get("SSN")), "age": (rs.%Get("Age"))}) //do results.%Push(+rs.%Get("ID")) } set out = { "pageSize":(pageSize), "pageIndex":(pageIndex), "fromIndex":(vFrom + 1), "toIndex":(vFrom+i), "resultSetTotal":(rs.Count()), "pageRecords":(i), "pages":($NORMALIZE((rs.Count()/pageSize),0)), "resultSet":(results) } return out } ClassMethod getRS(id As %Integer, Output rs As %ScrollableResultSet) As %Status { #dim sc As %Status = $$$OK if '$d(id) { set sql = "SELECT ID, SSN, Age FROM Sample.Person" set rs=##class(%ScrollableResultSet).%New("%DynamicQuery:SQL") set sc = rs.Prepare(sql) quit:$$$ISERR(sc) sc set sc = rs.Execute() quit:$$$ISERR(sc) sc quit:(rs.Count()=0) $$$ERROR($$$GeneralError, "No results") } else { set rs=##class(%ScrollableResultSet).%OpenId(id) } quit sc } ClassMethod getPersonsPageSave(pageSize As %String = 20, ByRef id As %Integer) As %DynamicObject { #dim sc As %Status = $$$OK #dim rs As %ScrollableResultSet set sc = ..getRS(.id,.rs) quit:$$$ISERR(sc) {"msg": ($System.Status.GetErrorText(sc))} set results = [] set:(pageSize >= rs.Count()) pageSize = rs.Count() set i = 0 set notAtEnd = rs.%Next() while notAtEnd && $i(i) { do results.%Push({ "index": (i), "pid": (rs.%Get("ID")), "ssn" : (rs.%Get("SSN")), "age": (rs.%Get("Age"))}) //do results.%Push(+rs.%Get("ID")) quit:(i>=pageSize) set notAtEnd = rs.%Next() } if notAtEnd { do rs.%Save() Set id=rs.%Id() } else { do rs.%DeleteId(id) set id = -1 } set out = { "pageSize":(pageSize), "resultSetTotal":(rs.Count()), "pageRecords":(i), "pages":($NORMALIZE((rs.Count()/pageSize),0)), "resultSet":(results) } kill rs return out } }
It's also about 3 times faster since the query is only executed once:
do ##class(User.Pagination).Time("Save") Save took 0,0048 sec do ##class(User.Pagination).Time("NoSave") NoSave took 0,0143 sec
That's Really Awesome! Thanks for the information!
Addition to the above: Speedup SQL pagination
Forgive if this is a dumb question, but I'm a little new to IRIS SQL. What does the "iAge" variable represent and what does it do in rs.Execute(iAge) ?
it passes the parameter iAge to the sql statement.
the sql statement have one parameter
?
, so it will take place here.Ah, I see, I didn't realize you could pass parameters into a SQL Statement that way. Thank you!