Hi, Basavaraj: I suspect some of the xdmp:value() call got lost along the way, given the string concatenation operator || with no strings.
It might speed things up to execute fn:subsequence(cts:search(...), $start, $end - $start + 1), specifying the sort order with cts:index-order() arguments to cts:search() instead of evaluating a for iteration. Erik Hennum ________________________________ From: [email protected] [[email protected]] on behalf of Basavaraj Kalloli [[email protected]] Sent: Monday, May 29, 2017 11:25 AM To: MarkLogic Developer Discussion Subject: Re: [MarkLogic Dev General] Optimising XQuery Timeouts Hi Erik, Here is the full extract of the xdmp:value() call: xdmp:value((for $i in cts:search(/record[.//npg:Article], $final-query, ('unfiltered' ), 0.0) order by || $o1 || $o2 || return $i)[$start to $end]) Looks like that query is bringing in everything from the database and then ordering. As I am typing I think it will be good for it to be ordered as part of the search query and return only that many results as from $start to $end. I will also look into the empty not-queries and post more details. The problem we have been having is that this is legacy code which is making it difficult to investigate. Thanks for the pointers, hope the above code can possibly be the reason the queries are running slow with concurrent requests? Cheers, Basavaraj On Fri, May 26, 2017 at 3:58 PM, Erik Hennum <[email protected]<mailto:[email protected]>> wrote: Hi, Basavaraj: Can you show the full xdmp:value() call? It looks like the FLWOR expression is ordering based on an XPath into each retrieved document. It would be more efficient to order within the cts:search() call based on range indexes. The $final-query reported by query trace has some odd subqueries of the form: cts:not-query(cts:or-query((), ()), 1) An empty cts:or-query() is always false, making the negation is always true, so these subqueries contribute no selectivity. You might look at expressing the searchable expression criteria as a subquery with the $final-query. Also, you could try to call cts:search() directly instead of evalling it with xdmp:value(). Finally, if you're retrieving a large number of documents, the best practice is to page over the result set. Hoping that helps, Erik Hennum ________________________________ From: [email protected]<mailto:[email protected]> [[email protected]<mailto:[email protected]>] on behalf of Basavaraj Kalloli [[email protected]<mailto:[email protected]>] Sent: Friday, May 26, 2017 4:13 AM To: MarkLogic Developer Discussion Subject: [MarkLogic Dev General] Optimising XQuery Timeouts Hi All, I have been trying to resolve a couple of our queries which time out every couple of hours. I believe this is down to the number of concurrent requests. Things evaluated/investigated: * I turned on the debug logs to see if there are any deadlocks * I couldn't find any traces of locks * Next I profiled my query and looks like there are 234 expressions and normally it returns under 0.00738 secs * Next I did try query-trace the output looks like: xdmp:value("(for $i in cts:search(/record[.//npg:Article], $final-query, ('u...") Analyzing path for search: fn:collection()/record[descendant::npg:Article] Step 1 is searchable: fn:collection() Step 2 is searchable: record[descendant::npg:Article] Path is fully searchable. Gathering constraints. Step 2 predicate 1 contributed 1 constraint: descendant::npg:Article Step 2 predicate 1 contributed 2 constraints: descendant::npg:Article Step 2 contributed 3 constraints: record[descendant::npg:Article] Comparison contributed string scatter value constraint: xdmp:collection = ("http://ns.nature.com/graphs/articles-labanimal";, "lab_animal", "http://ns.nature.com/graphs/articles-nature";, ...) Search query contributed 1 constraint: cts:and-query((cts:not-query(cts:or-query((), ()), 1), cts:collection-query(("http://ns.nature.com/graphs/articles-labanimal";, "lab_animal", "http://ns.nature.com/graphs/articles-nature";, "journals_nature", "http://ns.nature.com/graphs/articles-palgrave";, "journals_palgrave")), cts:or-query(cts:field-value-query("doi", "10.1038/212441a0", ("lang=en"), 0), ()), cts:not-query(cts:or-query((), ()), 1), cts:not-query(cts:or-query((), ()), 1), cts:not-query(cts:or-query((), ()), 1)), ()) Order by clause contributed 1 range ordering constraint for $i: order by xs:date($i/descendant::prism:publicationDate) descending Ordering can be unfiltered. Executing search. Selected 1 fragment. I dont see anything unsual with the output - no traces of unsearchable expressions and lack of indexes. * I did try query-meters <qm:query-meters xsi:schemaLocation="http://marklogic.com/xdmp/query-meters query-meters.xsd" xmlns:qm="http://marklogic.com/xdmp/query-meters"; xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";> <qm:elapsed-time>PT0.003874S</qm:elapsed-time> <qm:requests>0</qm:requests> <qm:list-cache-hits>150</qm:list-cache-hits> <qm:list-cache-misses>0</qm:list-cache-misses> <qm:in-memory-list-hits>0</qm:in-memory-list-hits> <qm:triple-cache-hits>0</qm:triple-cache-hits> <qm:triple-cache-misses>0</qm:triple-cache-misses> <qm:triple-value-cache-hits>0</qm:triple-value-cache-hits> <qm:triple-value-cache-misses>0</qm:triple-value-cache-misses> <qm:expanded-tree-cache-hits>1</qm:expanded-tree-cache-hits> <qm:expanded-tree-cache-misses>0</qm:expanded-tree-cache-misses> <qm:compressed-tree-cache-hits>0</qm:compressed-tree-cache-hits> <qm:compressed-tree-cache-misses>0</qm:compressed-tree-cache-misses> <qm:in-memory-compressed-tree-hits>0</qm:in-memory-compressed-tree-hits> <qm:value-cache-hits>3</qm:value-cache-hits> <qm:value-cache-misses>35</qm:value-cache-misses> <qm:regexp-cache-hits>1</qm:regexp-cache-hits> <qm:regexp-cache-misses>1</qm:regexp-cache-misses> <qm:link-cache-hits>0</qm:link-cache-hits> <qm:link-cache-misses>0</qm:link-cache-misses> <qm:filter-hits>0</qm:filter-hits> <qm:filter-misses>0</qm:filter-misses> <qm:fragments-added>0</qm:fragments-added> <qm:fragments-deleted>0</qm:fragments-deleted> <qm:fs-program-cache-hits>0</qm:fs-program-cache-hits> <qm:fs-program-cache-misses>0</qm:fs-program-cache-misses> <qm:db-program-cache-hits>0</qm:db-program-cache-hits> <qm:db-program-cache-misses>0</qm:db-program-cache-misses> <qm:env-program-cache-hits>0</qm:env-program-cache-hits> <qm:env-program-cache-misses>0</qm:env-program-cache-misses> <qm:fs-main-module-sequence-cache-hits>0</qm:fs-main-module-sequence-cache-hits> <qm:fs-main-module-sequence-cache-misses>0</qm:fs-main-module-sequence-cache-misses> <qm:db-main-module-sequence-cache-hits>0</qm:db-main-module-sequence-cache-hits> <qm:db-main-module-sequence-cache-misses>0</qm:db-main-module-sequence-cache-misses> <qm:fs-library-module-cache-hits>0</qm:fs-library-module-cache-hits> <qm:fs-library-module-cache-misses>0</qm:fs-library-module-cache-misses> <qm:db-library-module-cache-hits>0</qm:db-library-module-cache-hits> <qm:db-library-module-cache-misses>0</qm:db-library-module-cache-misses> <qm:fragments> <qm:fragment> <qm:root xmlns="">record</qm:root> <qm:expanded-tree-cache-hits>1</qm:expanded-tree-cache-hits> <qm:expanded-tree-cache-misses>0</qm:expanded-tree-cache-misses> </qm:fragment> </qm:fragments> <qm:documents> <qm:document> <qm:uri>/n5061/xml/212441a0.xml</qm:uri> <qm:expanded-tree-cache-hits>1</qm:expanded-tree-cache-hits> <qm:expanded-tree-cache-misses>0</qm:expanded-tree-cache-misses> </qm:document> </qm:documents> <qm:hosts> <qm:host> <qm:host-name>#removed#</qm:host-name> <qm:round-trip-count>1</qm:round-trip-count> <qm:round-trip-time>PT0.000921S</qm:round-trip-time> </qm:host> <qm:host> <qm:host-name>#removed#</qm:host-name> <qm:round-trip-count>1</qm:round-trip-count> <qm:round-trip-time>PT0.000856S</qm:round-trip-time> </qm:host> </qm:hosts> </qm:query-meters> The only thing that strikes me is that there are value cache misses, I dont know if I can do anything for it or anything else I could try. I am running out of ideas so it would be great if anyone can share some thoughts/pointers. Thanks, Basavaraj Kalloli _______________________________________________ General mailing list [email protected]<mailto:[email protected]> Manage your subscription at: http://developer.marklogic.com/mailman/listinfo/general
_______________________________________________ General mailing list [email protected] Manage your subscription at: http://developer.marklogic.com/mailman/listinfo/general
