A Checklist for Optimizing Query Performance

by Paxton Hare

I recently had a conversation with some developers looking for tips and tricks to help them optimize their XQuery code. During the conversation it occurred to me that the tips I was giving them were really just my mental checklist for how I optimize code. I asked around within MarkLogic and discovered that this mental checklist was nearly identical to those of my coworkers. Wouldn't it be nice if someone wrote this stuff down in easy to follow steps?

The Checklist

#1 - Is your query running in "Accidental" Update Mode?

MarkLogic runs transactions in one of two modes: query and update. The main difference being that query transactions are read-only and cannot perform update operations while update transactions can perform update operations.

That's nice, but what does this mean for performance?

Before we get into this, please be aware that update statements are necessary to update data in the database. You absolutely want and need to use them.

However, there are times when you are running code that doesn't update anything. Such query-only code should run in query mode. This checklist item is addressing the case where you accidentally run your query in update mode.

Now back to our fun explanation:

When you run in update mode MarkLogic will use reader and writer locks as needed. It can be easy to accidentally run a transaction in update mode. Doing so could have dire consequences to performance. Take for example this query:

fn:doc(), when given no parameters, returns everything in the database. If this code gets run in update mode then MarkLogic will assume that you plan to update documents so it locks every single document returned by fn:doc(). And because write locks are exclusive this will force any multithreaded requests to fight for locks. Lock contention is normal and expected, but it can degrade performance. This is a very naive example, but I hope it illustrates the point. If you accidentally run code in update mode, when you don't really want to, then you can cause performance problems.

Ok. But how do I know if I'm running in update mode? I don't think I am.

Lucky for us there is a nifty trick we can employ to throw an error if we are running in update mode when we want to be in query mode.

They key here being the let statement:

xdmp:request-timest returns an xs:unsignedLong when running in query mode. But it returns empty-sequence() when running in update mode. This let statement, if run from an update transaction, will fail to cast empty-sequence() to xs:unsignedLong an thus throw an exception.

If you run your query and the exception is thrown then you know that you are running in update mode. Now you have to track down the reason and try to fix it so your code can run in query mode. 

This is my #1 on the list because it's easy to check and can often result in significant performance gains. All too often I accidentally run code in update mode.

But how do you end up in accidental update mode?

A couple of scenarios come to mind.

  • xdmp:eval, xdmp:invoke, xdmp:invoke-function, xdmp:spawn, xdmp:spawn-function. These functions allow you to execute code and specify configuration options for transaction mode. If one of these functions is running your code then look there to see if you have it set to update mode by accident.
  • REST API service extensions. The MarkLogic REST API allows you to define your own service extensions. You create an xquery library module with one or more entry point functions (get, post, put, delete, etc). Some of these functions run as query mode by default and some run as update mode. See the docs for more specifics. Then check to see if you are running your code from one of the methods that runs in update mode by default.
  • static analysis. When your transaction is set to auto you allow MarkLogic to decide whether to run in update or query mode. To make this determination MarkLogic will run static analysis on your code looking for calls to any of the functions that perform updates. If any of these functions are found in the code path then MarkLogic will run in update mode.
    An example of some code that will cause update mode:

    As you can see, the document-insert is never called, but MarkLogic still sees it right there in the code path and thus runs in update mode.

There is an entire chapter devoted to transactions in the MarkLogic User's guide, I recommend that everyone read it at least once a year.

#2 - Is your cts:search running unfiltered? 

Perhaps one of MarkLogic's most famous APIs is cts:search. At some point every MarkLogic developer has used it. If you are that developer and you find your search seems to be running slower than you'd like, perhaps you are accidentally running a filtered search.

What's a filtered search?

Scott Parnell said it well in his blog post A goal without a plan is just a wish from November 2016:

By default, cts:search resolves queries in two phases. The first phase performs index resolution on the D-nodes. This initial result may contain false positives depending on the index configuration and the query. The second phase performs filtering of the results on the E-nodes, which examines the matched documents and removes false positives. If a query can be resolved completely from the indexes, then filtering is not required.

Assuming you have your indexes configured properly and that you want to run unfiltered let's move forward with checking your cts:search.

Looking at the docs for cts:search you will see this function signature:

In order for cts:search to run unfiltered, you must specify "unfiltered" as an option in parameter 3.

What gets most of us is that the default for cts:search is "filtered". By not supplying the "unfiltered" parameter we are relying on the default, filtered behavior.

This makes #2 of my list because it's so easy to overlook yet so easy to fix.

#3 - Profile Your Code

When code is not performing to your expectations you should ALWAYS profile it. Don't even bother optimizing until you do. You will most likely waste your time fixing the wrong thing.

Lucky for us MarkLogic makes it somewhat easy to do this.

Easiest Way: Use Query Console (QConsole)

From the MarkLogic Docs:

Query Console is an interactive web-based query development tool for writing and executing ad-hoc queries in XQuery, Server-Side JavaScript, SQL and SPARQL. Query Console enables you to quickly test code snippets, debug problems, profile queries, and run administrative XQuery scripts.

QConsole lives at http://yourserver:8000/qconsole/

It includes a handy profile button to tell you where your query is spending the most time. This is great news if you can easily run your query.

Simply type in the code you wish to run, press the profile tab, then press the run button.

The results in the table are sorted from longest running to shortest (Shallow %). Look for unusually long running statements and see how you can optimize them.

Tip: putting the bulk of your code into library modules makes it easier to isolate for testing and easier to profile.

Harder Way: Using prof:enable() and prof:report()

Let's say that, for whatever reason, you can't just run your code in QConsole. Fear not, there are functions to help you.

Simply wrap your code like so:

The output of prof:report() is an xml representation of the profile report. If your code is buried deep within other code you can either log the report with xdmp:log or insert it into a document with xdmp:document-insert() to look at later.

If you like to get fancy you can create a reusable module with a function to log it for you.

Whoa! That is fancy! How do I use it?

Now when you run your code, the profiler report will be logged to the ErrorLog and inserted at /profile-output/${requestid}.xml. It's not as easy to read as the QConsole output, but it contains all the same information.

#4 - Use Indexes when Appropriate

MarkLogic runs queries in two phases. The first and fastest phase uses indexes to narrow the number of results. These initial results may contain false positives depending on the index configuration and the query. The second phase performs filtering of the results. This second phase involves retrieving the documents from disk and verifying that they match the query. Documents that do not match (false positives) are removed from the result set. It's the disk I/O used when retrieving the documents that usually causes queries to run slow.

But how can I fix this?

In many cases you can rely solely on indexes to avoid the second phase. MarkLogic exposes many functions in the cts and xdmp namespaces for doing exactly this.

A Simple Example

A common example is getting the number of documents that match a query.

This can perform poorly because it has to retrieve every customer matching the paid="false" attribute from disk. To speed it up you would use indexes instead.

Note that we use xdmp:estimate. The key to this being fast is that it runs unfiltered. It doesn't perform the second phase of query resolution. That's why its name is estimate. It's relying on your indexes to give you a count. To ensure the resulting value is correct, you need to properly configure your indexes.

#5 - Optimize cts:search using Indexes

If after steps 1 to 4 you find that your performance bottlenecks are in your cts:search code you will want to start looking at index optimization. I will refer you to Scott Parnell's blog post A goal without a plan is just a wish from November 2016 which covers this in more detail.

Further Reading

This checklist is by no means exhaustive. It merely demonstrates some of the ways that MarkLogic developers go about finding and fixing performance issues. For more information on the topics discussed in this article read some of these.

Comments

  • Would love to see a JavaScript-focused version these tips. For example, at least in ML 8, Profiler does not work for JavaScript
    • Hi Salim. I don't have a full post for that yet, but: #1 doesn't apply (since JS requires "declareUpdate"); #2 applies pretty much as-is for JS; #3 -- profiling is supported for JS in ML9 (http://docs.marklogic.com/guide/performance/profile#id_21727); #4 and #5 apply pretty much as they are.