Exemple de pagination avec ScrollableResultSet

Article

Sylvain Guilbaud · Jan 2 4m de lecture

#JSON #ObjectScript #SQL #InterSystems IRIS

Bonjour à toutes et à tous,

Ceci est un exemple complet d'utilisation d'un %ScrollableResultSet pour la pagination des résultats à l'aide de %DynamicQuery:SQL et créer une réponse JSON incluant les détails de la page.

N'hésitez pas à l'utiliser, le partager, le commenter ou l'améliorer.

Cordialement

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

}

Aller à la publication initiale écrit par @Rubén Larenas

Sylvain Guilbaud · Jan 2

@Guillaume Rongier ajoute :

Bonjour Rubén,

Une autre proposition sur IRIS 2021.1+ peut être celle-ci avec l'utilisation de la fonction nouvelle fenêtre (OVER) :

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
}

Je compare les deux solutions sur un dataset de 100 000 lignes sans index avec un résultat de 20 éléments en page 1 et voici les résultats :

"getPersonsPag timed : 1,647 secondes"
"getPersonsPagWindow timed : 0,247 secondes"

Je suppose que la fonction window est plus rapide car vous n'avez pas besoin de récupérer toutes les données de manière globale avant la pagination.

1 0

Sylvain Guilbaud · Jan 2

@Eduard Lebedyuk précise :

L'idée d'un ensemble de résultats défilants est d'appeler Save/OpenId - et l'ensemble de résultats continuera automatiquement sur une ligne suivante. Vous n'avez donc pas besoin de gérer les indices avant/arrière :

Un exemple ici

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
}

}

C'est également environ 3 fois plus rapide puisque la requête n'est exécutée qu'une seule fois :

do ##class(User.Pagination).Time("Save")
Save took 0,0048 sec
do ##class(User.Pagination).Time("NoSave")
NoSave took 0,0143 sec

1 0

Sylvain Guilbaud · Jan 2

Et @Vitaliy Serdtsev conclue :

Ajout à ce qui précède : pagination SQL accélérée

1 0

Pierre LaFay · Jan 3

Les différences de performance sont impressionnantes, ce qui montre bien à mon sens qu'il est préférable de se poser les questions d'implémentation en tout début de phase de développement et/ou créer des classes façades

0 0