Read timed out at executing executeEntityQuery()

Question

I am working on a query that excludes items based on the value of one of its metadata.

The problem is that sometimes I get the result, but sometimes not. Error stack trace shows the following:

com.sdl.odata.client.api.exception.ODataClientRuntimeException: Unable to perform action
at com.sdl.odata.client.DefaultODataClient.performAction(DefaultODataClient.java:93)
at com.sdl.web.content.client.impl.ContentClient.lambda$performAction$7(ContentClient.java:330)
at com.sdl.web.content.client.impl.ClientRequestRetrySupporter$RetryBlock.execute(ClientRequestRetrySupporter.java:64)
at com.sdl.web.content.client.impl.ClientRequestRetrySupporter.runWithRetry(ClientRequestRetrySupporter.java:26)
at com.sdl.web.content.client.impl.ContentClient.performAction(ContentClient.java:329)
at com.sdl.web.content.client.ContentClientApi.performAction(ContentClientApi.java:43)
at com.sdl.web.api.broker.querying.QueryImpl.executeEntityQuery(QueryImpl.java:145)
....
Caused by: java.net.SocketTimeoutException: Read timed out
at java.net.SocketInputStream.socketRead0(Native Method)

The error is triggered when I call the following code:

Query builtQuery = buildQuery(brokerQuery);
Item[] items = builtQuery.executeEntityQuery();

The error is also triggered mostly when I execute the query on the 1st, 2nd, or 3rd try. But after that, the query returns just fine (I am assuming it's because of the caching).

I tried to debug with Fiddler, and I see the following warning from Fiddler when the error occurs:

The session was aborted by the client, Fiddler, or the server.

(Edit: added information) Increasing connection timeout on cd_client_conf.xml helped for the simpler query. But query with longer criteria (orCriteria in this case) keeps failing.

This is how I build the criteria for the query.

final List<Criteria> andCriteria = new ArrayList<>();
final List<Criteria> combinedCriteria = new ArrayList<>();
final List<Criteria> notCriteria = new ArrayList<>();
andCriteria.add(new ItemSchemaCriteria(61647));
andCriteria.add(new ItemTemplateCriteria(61648));
andCriteria.add(new PublicationCriteria(33));
andCriteria.add(new CustomMetaValueCriteria(new CustomMetaKeyCriteria("type"), "brief"));

notCriteria.add(new CustomMetaKeyCriteria("endDate"));
notCriteria.add(new CustomMetaKeyCriteria("category"));

NotInCriteria notInCriteria = new NotInCriteria(new OrCriteria(notCriteria));

combinedCriteria.add(new AndCriteria(andCriteria));
combinedCriteria.add(notInCriteria);

return new AndCriteria(combinedCriteria);

Explanation: I need to query based on specific filters with andCriteria. I use notCriteria to filter out content that has metadata attribute endDate or category filled in. Filtering out based on endDate only works sometimes fine, but every time I do more filtering based on the attribute (e.g. with category) it seems that the model service is struggling to execute the query.

What could be the problem here?

Answer 1

Let me clarify, For the broker query nothing to do with model service, broker query will always go to content service only, one thing I will mention for performance always recommend to pass the SetResultFilter Limit the number of results, it will add in your query something like select top 50 based on your SearchCap value.

Example:

// Limit the amount of results
 int SearchCap = 50;
 LimitFilter filter = new LimitFilter(SearchCap);
 query.SetResultFilter(filter);

I can already see your query bit complex because of the multiple join query based on your filter, andCriteria, and notInCriteria.

As @Anand mentioned worth you can capture your SQL query in the database to see is there any specific index if anything needed sometimes SQL profile will highlight those queries for optimizing for missing index or will ask specif index.

Start looking at those queries to see directly what is the execution time for that query. based on that you can adjust your Criteria in your code.

Example different possible filter criteria:

criteria.Add(new CategorizationCriteria(8, "Category", new String[] { "Keyword1", "Keyword2" }));
criteria.Add(new CategoryCriteria("'CategoryName'", Criteria.Equal));
criteria.Add(new ContentItemCriteria("tcm:1-2-3"));
criteria.Add(new CustomMetaKeyStringCriteria("'MetaKeyStringValue'"));
criteria.Add(new InCriteria(new ContentItemCriteria("tcm:4-5-6")));
criteria.Add(new ItemLastPublishedDateCriteria("2010-01-01", Criteria.GreaterThanOrEqual));
criteria.Add(new ItemReferenceCriteria(999));
criteria.Add(new ItemTitleCriteria("Some Title"));

criteria.Add(new KeywordCriteria("ACategory", new String[] { "AKeyword1", "AKeyword2" }, Criteria.Equal));
criteria.Add(new NotInCriteria(new ContentItemCriteria("tcm:4-5-6")));
criteria.Add(new NumericalRangeCriteria("Meta Field", 100, 200));
criteria.Add(new TaxonomyCriteria("tcm:7-8-9"));
criteria.Add(new TaxonomyKeywordCriteria("tcm:1-2-3", "tcm:3-4-5", true));
criteria.Add(new TaxonomyKeywordDescriptionCriteria("tcm:1-2-3", "Keyword Description", true));
criteria.Add(new CustomMetaValueCriteria(new CustomMetaKeyCriteria("type"), "brief", Criteria.Equal));

I hope it helps.