Woo hoo. Glad I could help. Sorry it was only about 100x improvement, I didn't spend a lot of time on it. :-)
-- Ron Hitchens, [email protected]/[email protected] On May 28, 2012, at 5:14 PM, "David Norris (FM&T Journalism)" <[email protected]> wrote: > 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
_______________________________________________ General mailing list [email protected] http://community.marklogic.com/mailman/listinfo/general
