How can I improve/optimize this HQL query?

Help / Discuss
, , ,
1

Hello everyone, hope you good :slight_smile:

I am using version 14.x.x of xWiki.
I have created a custom class within my Wiki that has multiple objects (about 3k-5k records) and I need to get them all in JSON format so that I can then build an Excel file with the objects from my class.

To achieve this, I created a script that was working well initially, but it is not really optimal, since the records will surely continue to increase and with my query, it takes a long time to obtain the data from xWiki.

#set ($hql = "select
            doc.fullName,
            property.value, 
            property1.value,
            property2.value
        from 
            XWikiDocument as doc, 
            BaseObject as obj,
            StringProperty as property,
            StringProperty as property1,
            StringProperty as property2
        where 
            obj.name = doc.fullName and 
            obj.className = 'MyWiki.DataTypes.MyClass' and
            doc.name <> 'myWikiObjectTemplate' and 
            property.id.id = obj.id and
            property.name = 'gameType' and
            property1.id.id = obj.id
        order by
            cast(property2.value as integer)")

#set ($query = $services.query.hql($hql)


#set ($documentsMap = {})
    #foreach ($item in $query)
        #set ($docName = $item[0])
        #set ($doc = $xwiki.getDocument($docName))
        #set ($obj = $doc.getObject('MyWiki.DataTypes.MyClass'))
        #set ($choices = $obj.getProperty('propertyX').value)
        #set ($choiceObjects = [])

        #foreach ($choice in $choices)
            #set ($choiceDoc = $xwiki.getDocument($choice))
            #set ($choiceObj = {})
            #if ($choiceDoc)
                #set ($choiceObj = $choiceDoc.getObject('MyWiki.DataTypes.ChoiceClass'))
            #end
            #set ($possibleAnswer = $choiceObj.getProperty('$propertyx').value)
            #set ($truthValue = $choiceObj.getProperty('propertyx').value)

            #set ($choiceData = {
                "value1": $propertyx,
                "value2": $propertyy,
                "value3": $propertyz
            })
            $choiceObjects.add($choiceData)
        #end

        #set ($docData = {
            "fullName": $obj.getProperty('property1').value,

So my question is: How can I improve/optimize xWiki’s HQL queries so that the search doesn’t take so long?

I query the Wiki database directly.

Any advice?

Thank you very much for your help or relevant information.
Best regards,
GalRen.