Marklogic optic query is running very slowly in the Java client, but it is running fast in the query console

Question

I have java spring boot application hosted on azure app service. I am running a optic query to select columns from a table with almost 10 million records. I am doing pagination using offset limit and only selecting 25 records per page.

The query runs very fast(<1 sec) in query console but it take on average 15 seconds from the application to run.

I am using Modifyplan and rowmanager in Marklogic Java client.

I am running this on production server with 3 primary and 3 secondary node. The cache settings on the group level is set to automatic.

I have tried running query with xdmp:query-meters in query console and I can see it is using triple index cache and triple index vale cache. I am expecting a response of few seconds from this application as it is further used in web applications.

The code is in client machine but it is something like this:

BasicAuthContext basicAuthContext = getBasicAuthContext(false, user,password);
DatabaseClient databaseClient= DatabaseClientFactory.newClient(host,port, "dh-FINAL",basicAuthContext);

Rowmanager rowManager= databaseClient.newRowManager();
PlanBuilder op = rowManager.newPlanBuilder();
//getTradePlan method create a queryplan
ModifyPlan plan = getTradePlan(op);

try(RowSet rows: rowManager.resultRows(plan))

This above line is where the query is executed and which takes more time in the code than in query console.

Answer 1

Make sure you are executing the query as the same user when comparing performance. If you are running as a user with the admin role in Query Console, then it may run faster than when executed by the Java client as a non-admin user. Queries executed as a user with the admin role will bypass some security checks and avoid overhead that can make a big difference in execution times.

https://help.marklogic.com/Knowledgebase/Article/View/query-performance-difference-between-admin-vs-non-admin-user

MarkLogic treats the Admin user as a super user. When an Admin user executes a query, the query is not evaluated against any Security database settings and it bypasses all document permission checks (i.e. read, write, update) and Query privileges.

When comparing performance of a query run by Admin user versus a non-Admin user, all other non-Admin user queries may show longer execution run times, depending upon how many roles the user inherits, the size of the security database, and on the nature of the query. You may not notice difference for an isolated single query executions, but when run under a large load, difference may be noticeable.

Depending upon the details, you might be able to tune the query to be more efficient. You might also consider using AMPS in order to boost the performance of some components of the query, or wrap it all in a function and AMP the entire thing, depending upon whether that makes sense for your data and security model.

https://help.marklogic.com/knowledgebase/article/View/109/0/lexicon-performance-at-scale

Query time grows proportionately with the number of matches from a given search across a set of documents (not the actual number of documents in your database). The presence of security constraints will contribute a significantly larger number of matches than if the same lexicon search was performed with admin credentials. In order to minimize the number of matches (and therefore query time) for a given lexicon search, you'll want to amp your lexicon searches to an admin user.