XPath retrieve sometimes slow when sorted

0
Situation: a table with 2.5M records. We do a sorted retrieve on all objects (in this case: around 130 of them) that are associated to a certain helper object. We do that in a java action like this: Core.retrieveXpathQuery(getContext(),//Module.Person[Module.HelperObject_Person='349113150827176554'],-1,0,{PersonDateTimeAttribute=ASC}) Core.retrieveXpathQuery(getContext(),//Module.Person[Module.HelperObject_Person='349113150827176554'],-1,0,{PersonAutoNumberAttribute=ASC}) These queries take around 35 seconds, while there is a table index on both PersonDateTimeAttribute and PersonAutoNumberAttribute. Now consider the following, doing a similar retrieve but sorted on several attributes of an associated object: Core.retrieveXpathQuery(getContext(),//Module.Person[Module.HelperObject_Person='349113150827176554'],-1,0,{Module.Person_Address/Module.Address/Zipcode=ASC, Module.Person_Address/Module.Address/Street=ASC, Module.Person_Address/Module.Address/HouseNr=ASC}) This (more complex, it seems) query takes 54 milliseconds. Why are the first queries so slow? How can we improve these?
asked
3 answers
1

Maybe because the order is different. In the first case, the statement is executed by first doing the sort over 2.5M objects and then the selection of the 130 persons related to HelperObject. In the second case, the selection is done first and the sorting by the associated entity is done over only the 130 found records, which is why it can be faster.

If this is really so, then this is the result of a bad choice made by retrieveXpathQuery.

A dirty workaround might be to add a self-reference from Person to Person (duh, what else?) for each of the 2.5M Persons and then sort on Person_Person/PersonDateTimeAttribute. See if that tricks retrieveXpathQuery into first selecting and then sorting.

answered
1

If you really want to know what is going on here do the following:

 – Log database things on trace level (I am never sure which one you need, probably connectionbus_retrieve or something)
 – Find the actual SQL-queries. Note that these contain ? instead of the parameters. The values for these parameters will have been logged on seperate lines just before the statement itself.
 – Paste the SQL into a pgadmin SQL-console. Use the explain analyze funcitonality (google for this on how to turn it on)
 – Execute the query in pgadmin.

You will now see what is taking the database this long. Only issue is that this explain plan will not tell you why the database did not use a specific index where you would have expected it to.

I see 2 workarounds for your issue:

 – Add an extra sort, resulting in something like

PersonDateTimeAttribute=ASC, Module.Person_Address/Module.Address/Zipcode=ASC

- Write the desired query directly in SQL, which is easy in Mendix 7.

And another question I have: is there a reason why you use Core.retrieveXpathQuery instead of the superior XPath helper class from Community Commons?

answered
0

Have you tried to perform the first query without the order by / sorting ? If you have placed an index on PersonDateTimeAtrribute, isn't the sorting already been done by the index? 

answered