Advanced search option in CME is timing out if the result expected is more than 250 items. On closed observation, found that if query takes more than 50 seconds then it times out and retrieving just 250 items taking more than 50 seconds is very strange.
Please note, this scenario is only happening in production where we have better infrastructure than development. In development same query is completed for approx 900 items in 20 seconds.
The query timeout error is as follows:
Error: Query timeout expired
Component: Tridion.ContentManager.CoreService
Errorcode: 3633
User: NT AUTHORITY\NETWORK SERVICE
StackTrace Information Details:
SQLUtilities.OpenRecordsetByStoredProcedure
SystemDAL.GetBulkStatusInfo
UtilitiesBL.GetBulkStatusInfo
SearchBLST.PostProcessListSearchResults
SearchBLST.GetListData
SearchBLST.GetSearchResults
at Tridion.ContentManager.ContentManagement.SearchQuery.GetListResults(Int32 startRowIndex, Int32 maxRows)
at Tridion.ContentManager.Query.GetListResults()
at Tridion.ContentManager.CoreService.CoreServiceBase.GetSearchResultsXml(SearchQueryData filter)
at SyncInvokeGetSearchResultsXml(Object , Object[] , Object[] )
at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
at Tridion.ContentManager.CoreService.TransactionSupportInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)
We have same search infrastructure setup in development and production i.e. One master search server and other slave search servers. In development we have total 2 servers and in production we have 6 redundant servers.
Also, we have already tried following options:
1. Reindexing the CM database
2. Running the solr link for index optimization
3. Ran re-index tool.
Please suggest what can be done to improve the performance of the query in production?