Scrollable ResultSet Pagination Sample

Article

Rubén Larenas · Sep 7, 2021 4m read

#JSON #ObjectScript #SQL #InterSystems IRIS

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?

Guillaume Rongier · Sep 8, 2021

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.

3 0

Rubén Larenas · Sep 8, 2021

That's Really Awesome! Thanks for the information!

0 0

Eduard Lebedyuk · Sep 8, 2021

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:

Here's an example

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

2 0

yongsheng chen · Sep 8, 2021

That's Really Awesome! Thanks for the information!

0 0

Vitaliy Serdtsev · Sep 9, 2021

Addition to the above: Speedup SQL pagination

2 0

Jason Jones · Mar 26

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

0 0

Guillaume Rongier · Mar 26

it passes the parameter iAge to the sql statement.

the sql statement have one parameter ?, so it will take place here.

0 0

Jason Jones · Mar 26

Ah, I see, I didn't realize you could pass parameters into a SQL Statement that way. Thank you!

0 0