REQUIREMENT : Get Employee IDs (/Employee/@id) matching the query in ascending order of joining date (/Employee/JoiningDate) as quickly as possible
ENVIRONMENT : MarkLogic DB has 1 million Employee XMLs
QUERY-1 [Runs very slow, but returns data in the correct order]
let $fullQuery :=
cts:and-query((
cts:directory-query("/employee/", "infinity"),
cts:element-value-query(fn:QName("","DeptName"), "Sales", ("case-insensitive")),
cts:element-value-query(fn:QName("","SubDeptName"), "Micro", ("case-insensitive"))
))
let $queryOptions := ("unfiltered", cts:index-order(cts:element-reference(fn:QName("","JoiningDate"),("type=dateTime")), "ascending"))
return cts:search(/Employee, $fullQuery, $queryOptions)!fn:string(./@id) (: Using ! to get id's in sorted order. This seems to affect performance quite heavily. :)
QUERY-2 [Runs very fast, but returns data in incorrect order]
let $fullQuery :=
cts:and-query((
cts:directory-query("/employee/", "infinity"),
cts:element-value-query(fn:QName("","DeptName"), "Sales", ("case-insensitive")),
cts:element-value-query(fn:QName("","SubDeptName"), "Micro", ("case-insensitive"))
))
let $queryOptions := ("concurrent")
return cts:element-attribute-values(xs:QName("Employee"), xs:QName("id"), (), $queryOptions, $fullQuery)
How to fulfil the REQUIREMENT using either of the above two approaches?
cts:value-co-occurrences
will let you order by one index while returning the values from another index at the same time.
e.g.
for $v in cts:value-co-occurrences(
cts:element-reference(xs:QName("JoiningDate"),
cts:element-attribute-reference(xs:QName("Employee"), xs:QName("id")),
(),
$fullQuery
)
return $v/cts:value[2]/string(.)