Optimizing Marklogic Search Query for Profiling Results

I see two main problems: there are too many trips in the database and those trips are returning too much data that you don't really need. The goal is to keep the number of database searches to a minimum and to make each search as accurate as possible.

In this case, the main way to perform database lookups is cts:search

. There are several: perhaps too many, and sometimes the results are never used. I think some of them are leftover experiments. When you profile it is important to clean up clean code.

Next, most of the profiler's time is in the @name=$segment

XPath predicate . This was repeated without a good reason. Get rid of the repetition and it will go faster.

However, another reason @name=$segment

is that MarkLogic indexes documents, not nodes. It indexes the names and values ​​of the nodes, but each index entry points to a document, or rather to a fragment, but not there. So when you have one document with tens or hundreds of index entries for values segment/@name

, all of those index entries point to the document root. When you only query for segments that match a specific name, the index lookup matches the entire document. Therefore, each document tree must be evaluated. This can be costly in CPU cycle, and that's what the profiler shows.

There is no cure for this without restructuring the document, or perhaps doing something smart with coincidences. However, we can clean up your query and convert it to a single XPath expression using full paths. See if this is enough for your use case.

declare namespace p4ns="http://www.nvsp.nl/p4" ;

(: These might be external parameters. :)
let $segment := "Bruto"
let $ids := ("2311","2312","2313")
return collection()/p4ns:postcode[
  @id = $ids]/p4ns:category/p4ns:variable/p4ns:segment[
  @name = $segment]/string()

      

        
        
        
      

    

If I insert your XML sample and change my id to 2313

, this returns a single value 1234

. Profiling shows 33 expressions in less than 1ms, with 66% of the time searching the database through XPath. However, it still has to look at all values segment/@name

: in this case, 14 of them take 10% of the time.

Note that I have not used cts:search

any of your range indices either. MarkLogic automatically indexes the node value for queries to match XPath values. You only need range indices for special operations: for example, graphs, sorting, and finding inequality.

You could improve this a bit:

(collection()/p4ns:postcode[
  @id = $ids]/p4ns:category/p4ns:variable/p4ns:segment[
  @name = $segment])[1]/string()

      

        
        
        
      

    

We now tell the evaluator that only one match was expected. This way it will stop after it finds Bruto

and what is at the beginning of the document. In this case, it is the first, but on average it (...)[1]

should cut the number of expressions in half. Other tree trimming practices should also help: for example, you can add names category

and variable

their input and express them as predicates XPath.

This may be a good time to back up and see the big picture. What are you trying to accomplish with this query? There may be a much more efficient way to achieve your goal.

If this is your most common use case, then ideally you would restructure your documents so that every lookup in the id segment becomes a computable call doc($uri)

. I'm not sure if it's a good idea in this particular case, but I don't have complete knowledge of your application.

Another approach is to use in-memory value indexes and https://docs.marklogic.com/cts:value-co-occurrences to not look at XML at all. However, this is a complex approach, and I am not going to explore it here.