0

Jet perfomance

Hi.

Some reports suddenly gives #VALUE for all columns/rows in the Excel sheet.When debugging it complains: 

2022-06-24 15.44.43.736 Thread1 ProgressManager.EndProgress: Keep alive: True
2022-06-24 15.44.43.750 Thread1 ReplicationBatchExecutionManager.TryExecuteNFLookupQuery: A Jet Reports error has occurred:
 Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
  Jet.Shared.DataProviderException
   at Jet.Data.Ado.AdoDataSource.OpenReader(String query)
   at Jet.Data.Ado.AdoDataSource.QueryLookup(ILimitableQuery query)
   at Jet.Data.JetDataSource.ExecuteQuery(IQuery query)
   at Jet.Data.Ado.AdoDataSource.ExecuteQuery(IQuery query)
   at Jet.Data.Nav.NavSqlDataSource.ExecuteAdoQuery(QueryInfo adoQuery, Boolean isVirtualTable)
   at Jet.Data.Nav.NavSqlDataSource.QueryLookup(ILimitableQuery query)
   at Jet.Data.JetDataSource.ExecuteQuery(IQuery query)
   at Jet.JetFunctionEvaluator.ExecuteDataFunctionQuery(JetFunction function, IJetDataSource dataSource)
   at Jet.JetFunctionEvaluator.EvaluateDataFunction(JetFunction function, Boolean applyDefaultScanLimit)
   at Jet.JetFunctionEvaluator.GetTableResults(NLFunction function)
   at Jet.JetFunctionEvaluator.EvaluateTableFunction(JetFunction function)
   at Jet.BatchExecution.ReplicationBatchExecutionManager.TryExecuteNFLookupQuery(IJetDataSource dataSource, TableInfo table, IEnumerable`1 tokens, JetFunction& function, Object[,]& results)

   Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
  System.Data.SqlClient.SqlException

We're running the newst Jet version with service tier on the Dynamics Business central on-prem server, and a sql 2016 in the same network. It is a busy SQL/Dynamics enviornment, but we cannot find why it suddenly "hicks".

The report itself gets items via a NL-function, and does 2 more NL-calls to other tables with the SKU from NL#1. Besides that we try to use NF for everything.

Any ideas to get Jet to perform better? NOLOCKS is activated and the timeout is changed to 300s.

Regards

Martin

2 comments

Please sign in to leave a comment.