Hi,
Thanks Ron that¹s made a huge difference.
3.9 seconds for that query with 700,000 documents now takes just 0.030
seconds.
Excellent!
Thanks for you help.
Regards,
Dave
(The extra space in the $value variable was a typo in our email)
I notice that your $value variable has a leading space
in the value (' OBS-AT001-0569'), is that intentional?
But I think the reason you're not getting results is that
you've got the values reversed in the >= and <= tests. The
value assigned to $windowStart is greater than $windowEnd,
which will never yield any results.
Not having a URI lexicon does not prevent you from using
the cts element value functions. Only the cts:uris and
cts:uri-match functions require that the URI lexicon be present.
Since you say you have a range index on timeCode, I couldn't
help myself and rewrote your query using cts:search and range
queries. This should be a lot quicker than your original query
(note, tested only on trivial amounts of data, not at scale, I
also removed the space from $value and flipped start/end values):
xquery version '1.0-ml';
declare variable $documentUri := '/stats/';
declare variable $value := 'OBS-AT001-0569';
declare variable $windowStart :=
xs:dateTime('2012-05-08T16:14:00.000+01:00');
declare variable $windowEnd := xs:dateTime('2012-05-08T16:15:00.000+01:00');
declare variable $limit := 20;
declare variable $session-qname := xs:QName("session");
declare variable $id-qname := xs:QName("id");
declare variable $timeCode-qname := xs:QName("timeCode");
if ( $windowStart > fn:current-dateTime() )
then ()
else (
<document>{
(for $d in cts:search (xdmp:directory($documentUri, "infinity"),
cts:and-query ((
cts:element-attribute-value-query ($session-qname, $id-qname,
$value, "exact"),
cts:element-range-query ($timeCode-qname, ">=", $windowStart),
cts:element-range-query ($timeCode-qname, "<=", $windowEnd)
)))
order by xs:dateTime ($d/document/header/video/timeCode) descending
return <stat>{$d/document/element()}</stat>
) [1 to $limit]
}</document>
), xdmp:query-meters()
On May 27, 2012, at 11:55 AM, Jonathan Cook - FM&T wrote:
> Hi,
>
> Would someone have some suggestions for improving the following query? Doesn't
seem to be utilising the range index we have setup on timeCode.
>
> We do not have uri-lexicon switched on so don't think we can make use of
cts:element* functions.
>
> Thanks
> Jon
>
> declare variable $documentUri := '/stats/';
> declare variable $value := ' OBS-AT001-0569';
> declare variable $windowStart := xs:dateTime('2012-05-08T16:15:00.000+01:00');
> declare variable $windowEnd := xs:dateTime('2012-05-08T16:14:00.000+01:00');
> declare variable $limit := 20;
>
> if ( $windowStart > fn:current-dateTime() )
> then ()
> else (
> <document>{
> let $seq := for $d in xdmp:directory($documentUri, "infinity")
> where $d/document/header/video/session/@id=$value
> and xs:dateTime($d/document/header/video/timeCode) >= $windowStart
> and xs:dateTime($d/document/header/video/timeCode) <= $windowEnd
> order by xs:dateTime($d/document/header/video/timeCode) descending
> return xdmp:node-uri($d)
>
> for $top in $seq[position() <= $limit]
> return <stat>{fn:doc($top)/document/element()}</stat>
> }</document>
> ), xdmp:query-meters()
_______________________________________________
General mailing list
[email protected]
http://community.marklogic.com/mailman/listinfo/general
