Object properties query efficiency

Hi all,

I’m hoping to get some advice on a more efficient way to retrieve all the properties of custom objects attached to a page.

My problem is that each call to getProperty(‘xx’).getValue() appears to generate a separate database query. That’s fine for a small number of objects, but on a page with, say, 100 custom objects, each with 10 properties, that generates 1000 queries. Under heavy load, I’m finding that causes major bottlenecks with e.g. database connections and often crashes the site.

Currently, I do something like this:

    #set($objs = $doc.getObjects('XWiki.CustomClass'))
    #foreach($obj in $objs)
        #set($prop = $obj.getProperty('CustomProp1').getValue())
        #set($prop = $obj.getProperty('CustomProp2').getValue())
    #end

I thought I could use HQL to get all the data in a single query. The issue I’m running into is that while the class definition may have 10 custom properties, each individual object may only have stored values for some.

So, if I write the query to return one property per row, there’s no easy way to iterate by object, because the number of returned rows corresponding to each object isn’t fixed.

In standard SQL, this is easy with a left join, but HQL doesn’t appear to support left joins. So a query like this results in excluding all objects that don’t have a value for, say, CustomProp2:

    select
        prop1.value,
        prop2.value
    from
        XWikiDocument doc,
        BaseObject obj,
        StringProperty prop1,
        StringProperty prop2
    where
        doc.fullName=obj.name
        and obj.className='XWiki.CustomClass'
        and (prop1.id.id=obj.id and prop1.name = 'CustomProp1')
        and (prop2.id.id=obj.id and prop2.name = 'CustomProp2')

I’ve read through all the HQL query examples in the docs, but don’t see anything matching my use case. I also don’t think I can use the REST API for this, because it has the same problem - each call to /objects/x/properties would need a separate HTTP request and generate a separate database query.

Does anyone know an easy way to get all the properties for all objects on a page in an easily iterable form in a single query?

Any help is greatly appreciated.

I think I’ve come up with a workaround using sub-selects, e.g.:

select
    (select prop1.value from StringProperty prop1 where prop1.id.id=obj.id and prop1.name = 'CustomProp1',
    (select prop2.value from StringProperty prop1 where prop2.id.id=obj.id and prop2.name = 'CustomProp2'
from
    XWikiDocument doc,
    BaseObject obj
where
    and doc.fullName=obj.name
    and obj.className='XWiki.CustomClass'

Not much more efficient execution-wise, but can at least be sent to the DB in one chunk instead of as 1000 separate queries.

If someone has a better way to do it, would love to learn.

I don’t think that’s correct. XWiki has a document cache. To get a property you need to get hold of the document first and you’re handed one from the cache in most cases. All the xobjects and properties are also in the cache (and thus in memory).

That’s a different topic. Indeed right now when we load a document we also load all its xobjects (and thus xproperties) and then cache them. Thus right now we don’t scale when documents have a high number of xobjects. 100s are fine but if you get into the 1000s or more it may be a bit slow.

FTR this is http://jira.xwiki.org/browse/XWIKI-2874

So the rule right now when using XWiki is to use a lot of documents (that scales very well) but for each document to not have too many xobjects.

Thanks for the reply.

My HQL workaround looks like it’s not going to work - I need users without programming rights to be able to execute the code.

I get the objects from the current document like this:
#set($objs = $doc.getObjects('XWiki.CustomClass'))

And then I iterate over $objs to extract each $obj.getProperty().getValue()

On a single page load, I see hundreds/thousands of queries like this in my MySQL log:

select stringprop0_.XWS_ID as XWP1_10_0_, stringprop0_.XWS_NAME as XWP2_10_0_, stringprop0_1_.XWP_CLASSTYPE as XWP3_10_0_, stringprop0_.XWS_VALUE as XWS3_15_0_ from xwikistrings stringprop0_ inner join xwikiproperties stringprop0_1_ on stringprop0_.XWS_ID=stringprop0_1_.XWP_ID and stringprop0_.XWS_NAME=stringprop0_1_.XWP_NAME where stringprop0_.XWS_ID=3328819443768384352 and stringprop0_.XWS_NAME='CustomProp1'

select stringprop0_.XWS_ID as XWP1_10_0_, stringprop0_.XWS_NAME as XWP2_10_0_, stringprop0_1_.XWP_CLASSTYPE as XWP3_10_0_, stringprop0_.XWS_VALUE as XWS3_15_0_ from xwikistrings stringprop0_ inner join xwikiproperties stringprop0_1_ on stringprop0_.XWS_ID=stringprop0_1_.XWP_ID and stringprop0_.XWS_NAME=stringprop0_1_.XWP_NAME where stringprop0_.XWS_ID=3328819443768384352 and stringprop0_.XWS_NAME='CustomProp2'

If I’m understanding you right, that’s to be expected, and those results are then supposed to be cached, but there’s currently no way around the first page load performing a large number of separate SELECT queries?

The JIRA issue is helpful - maybe I need to think about refactoring this data model to use less custom objects.

Correct, that’s http://jira.xwiki.org/browse/XWIKI-2874

Using custom objects is very good and this is the strength of XWiki. But you need to use several documents instead: instead of 1000 xobjects in a page, use 1000 pages with 1 xobject in each. That’s how it’s done everywhere and that scales very well.

Thanks

1 Like