Slow Servicedesk and Workflow Performance - UpdateProcessLastModifiedDate taking too long.

Products

• Workflow Solution
• ServiceDesk

Issue/Introduction

 Customers complained of slow performance

 [Workflow] Error in UpdateProcessLastModifiedDate

Client: IP=127.0.0.1 HostName=127.0.0.1 Browser=Mozilla/4.0 (compatible; MSIE 6.0; MS Web Services Client Protocol 2.0.50727.5466)

System.Data.SqlClient.SqlException: A severe error occurred on the current command.  The results, if any, should be discarded.

A severe error occurred on the current command.  The results, if any, should be discarded.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.HasMoreRows()

   at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)

   at LogicBase.Ensemble.Workflow.Reporting.Gateways.SQLStorageGateway.GetReportsFromReader(IDataReader rdr, Int32 pageNumber, Int32 pageLength, List`1 list, ReportLoadParams loadParams, ReportFilterContext filter)

   at LogicBase.Ensemble.Workflow.Reporting.Gateways.SQLStorageGateway.ExecuteReader(IDbCommand cmd, ReportFilterContext filter, Int32 pageNumber, Int32 pageLength, List`1 list, ReportLoadParams loadParams)

   at LogicBase.Ensemble.Workflow.Reporting.Gateways.SQLStorageGateway.ProcessGetReportsList(ReportFilterContext filter, Int32 pageLength, Int32 pageNumber, String sortEquation, ReportLoadParams loadParams)

   at LogicBase.Ensemble.Workflow.Reporting.Gateways.ReportStorageGateway.GetReportsList(ReportFilterContext filter, Int32 pageLength, Int32 pageNumber, String sortEquation, ReportLoadParams loadParams)

   at LogicBase.Ensemble.Workflow.Reporting.Gateways.ReportStorageGateway.GetReportsList(ReportFilterContext filter, ReportLoadParams reportLoadParams)

   at LogicBase.Ensemble.Workflow.Reporting.Gateways.ProcessReportStorageGateway.GetProcessFromFilter(String sessionID, LogProcessReport repFilter, Boolean checkPerm, Boolean loadAllData)

   at LogicBase.Ensemble.Workflow.ReportingStorageGatewayService.UpdateProcessLastModifiedDate(String sessionID, String executionContextID, String reason)

 

and

 

Client: IP=127.0.0.1 HostName=127.0.0.1 Browser=Mozilla/4.0 (compatible; MSIE 6.0; MS Web Services Client Protocol 2.0.50727.5466) 

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

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) 

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) 

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) 

at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) 

at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) 

at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) 

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) 

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() 

at LogicBase.Ensemble.Workflow.Reporting.Gateways.SQLStorageGateway.ExecuteCommand(IDbConnection con, IDbCommand cmd)

Cause

 The SQl query surrounding the UpdateProcessLastModifiedDate, a query that is used frequently, was returning an unnecesarily excessive amount of data.  Much more than was required. Mostly due to the use of SELECT * and an improper WHERE clause configuration. There was also an UPDATE which was updating all the columns even though only a single column needed to be updated. 

In all cases, for this query to perform its required function, the only column that needed to be updated was ModifiedOn in the ReportProcess table. This is done every time a change is made to a process.

Resolution

 A revised build of Workflow - 7.5.3001.64 or later, contains this fix.  See KB Article HOWTO95421, for the correct build.

Applies To

 Servicedesk 7.5 Mp1