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] [[email protected]] on behalf of Basavaraj Kalloli [[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] Manage your subscription at: http://developer.marklogic.com/mailman/listinfo/general
