Database throughput problems due to queries to the AssetEntry table executed by the assetEntryLocalServiceUtil.getEntries method

Issue

We have a custom development that calls the assetEntryLocalServiceUtil.getEntries method:

AssetEntryQuery assetEntryQuery = new AssetEntryQuery();
assetEntryQuery.setAnyCategoryIds(new long[] { categoriaMesVistos });
assetEntryQuery.setOrderByCol1("priority");
List<AssetEntry> assetEntryList = AssetEntryLocalServiceUtil.getEntries(assetEntryQuery)

This method executes queries to the AssetEntry table, leading to database throughput problems.

How can I avoid them?

Environment

DXP 7.3

Resolution

You have to replace the call to AssetEntryLocalServiceUtil.getEntries with the equivalent _assetHelper.searchAssetEntries method that uses the Elasticsearch index.

In the Asset Publishers, this code: com.liferay.asset.publisher.web.internal.util.AssetPublisherHelperImpl class, getAssetEntries method uses both AssetEntryLocalServiceUtil.getEntries and _assetHelper.searchAssetEntries depending on _isSearchWithIndex result.

Since AssetEntryLocalServiceUtil.getEntries can cause performance issues at the database level, it is generally preferable to use the _assetHelper.searchAssetEntries method, which queries against Elasticsearch and typically performs better for these specific use cases.

As you can see in the linked example, both methods receive an assetEntryQuery object, so adapting your code to use _assetHelper.searchAssetEntries is straightforward:

Call assetHelper.searchAssetEntries, which returns an object of type BaseModelSearchResult<AssetEntry>. There are several examples of how to invoke this method:
With the BaseModelSearchResult<AssetEntry> object, call the baseModelSearchResult.getBaseModels() method, which will return the list of AssetEntries.

With these changes, you will avoid executing this query against your database and instead perform it against Elasticsearch.