Why do I get a timeout expired error in Timetracker's Reporting API and how do I avoid it?

Question 

Why do I get timeout expired error in Timetracker's Reporting API and how do I avoid it?

Answer

Some fields that Reporting API endpoints return are not just stored in our database as values, but calculated every time that users make requests. These are referred to as calculated fields. 

Calculating these fields - "TrackedItself", "TrackedTotal", "TrackedItselfBillable", "TrackedTotalBillable" - is a heavy operation, so retrieving them can take a considerable amount of time. This might lead to a "timeout expired" exception if a user requests a large amount of data. In this case, we suggest decreasing the amount of data by using more filters or removing calculated fields from the select statement if you don't need them.

How to sort and filter by calculated fields

Usually, it's relatively easy to sort or filter data by some value, but with calculated fields, their values should be calculated first and then filtered or sorted. For example, let's say a user runs a query that returns the first 100 rows ordered by TrackedItself field. Only 100 rows are requested, but the application has to calculate TrackedItself for the whole organization's work items based on all worklogs that were ever tracked to just sort them.

The same approach is true for filtering by calculated fields - both operations are extremely heavy and there's a good chance that it could lead to a timeout exception. That's why we've made additional filtering required.

Right now, it's impossible to sort or filter by a calculated field based on all data for all times. If such a filter or order is added to query, then using worklogsFilter becomes required to limit the number of worklogs for calculation. Learn more about worklogsFilter here.

Here are some examples:

// this query is allowed because it doesn't use sorting or filtering by a calculated field
https://your_org_name.timehub.7pace.com/api/odata/v3.0/workitems?$select=System_Id,System_WorkItemType,TrackedItself&$filter=System_TeamProject eq 'My project'

// following queries will throw an exception 
// because worklogsFilter is not used and values are calculated basing on all worklogs
https://your_org_name.timehub.7pace.com/api/odata/v3.0/workitems?$select=System_Id,System_WorkItemType,System_TeamProject,TrackedItself&$filter=System_TeamProject eq 'My project'&$orderby=TrackedItself desc
https://your_org_name.timehub.7pace.com/api/odata/v3.0/workitems?$select=System_Id,System_WorkItemType,System_TeamProject,TrackedItself&$filter=System_TeamProject eq 'My project' and TrackedItself ge 3600

// these queries are allowed - 
// although they use filtering or sorting by a calculated field 
// those calculations are based on worklogs filered by dates, 
// so TrackedItself would contain sums of worklogs from a specified month only
https://your_org_name.timehub.7pace.com/api/odata/v3.0/workitems?$select=System_Id,System_WorkItemType,System_TeamProject,TrackedItself&$filter=System_TeamProject eq 'My project'&$orderby=TrackedItself desc&worklogsFilter=Timestamp ge 2020-10-01 and Timestamp lt 2020-11-01
https://your_org_name.timehub.7pace.com/api/odata/v3.0/workitems?$select=System_Id,System_WorkItemType,System_TeamProject,TrackedItself&$filter=System_TeamProject eq 'My project' and TrackedItself ge 3600&worklogsFilter=Timestamp ge 2020-10-01 and Timestamp lt 2020-11-01

Sort and filter by other fields

Most work item fields can be used in any statement without any issues, but some of them are not really expected to be used for filtering or sorting, so queries are slow in such cases.

Here's a list of these work item fields. If you need to sort or filter by any of them, please contact us at support@7pace.com and describe your use case. We might be able to improve the speed of the field you need.

System_AreaId
System_CreatedBy
System_NodeName
System_Reason
System_RevisedDate
Microsoft_VSTS_CMMI_Blocked
Microsoft_VSTS_CMMI_FoundInEnvironment
Microsoft_VSTS_CMMI_HowFound
Microsoft_VSTS_CMMI_Probability
Microsoft_VSTS_CMMI_RequirementType
Microsoft_VSTS_CMMI_RequiresReview
Microsoft_VSTS_CMMI_RequiresTest
Microsoft_VSTS_CMMI_RootCause
Microsoft_VSTS_CMMI_TargetResolveDate
Microsoft_VSTS_CMMI_TaskType
Microsoft_VSTS_CMMI_UserAcceptanceTest
Microsoft_VSTS_Common_ActivatedBy
Microsoft_VSTS_Common_ActivatedDate
Microsoft_VSTS_Common_BacklogPriority
Microsoft_VSTS_Common_BusinessValue
Microsoft_VSTS_Common_ClosedBy
Microsoft_VSTS_Common_ClosedDate
Microsoft_VSTS_Common_Discipline
Microsoft_VSTS_Common_Issue
Microsoft_VSTS_Common_Priority
Microsoft_VSTS_Common_Rating
Microsoft_VSTS_Common_ResolvedBy
Microsoft_VSTS_Common_ResolvedDate
Microsoft_VSTS_Common_ResolvedReason
Microsoft_VSTS_Common_ReviewedBy
Microsoft_VSTS_Common_Risk
Microsoft_VSTS_Common_Severity
Microsoft_VSTS_Common_StackRank
Microsoft_VSTS_Common_StateChangeDate
Microsoft_VSTS_Common_TimeCriticality
Microsoft_VSTS_Common_Triage
Microsoft_VSTS_Common_ValueArea
Microsoft_VSTS_Scheduling_CompletedWork
Microsoft_VSTS_Scheduling_DueDate
Microsoft_VSTS_Scheduling_Effort
Microsoft_VSTS_Scheduling_FinishDate
Microsoft_VSTS_Scheduling_OriginalEstimate
Microsoft_VSTS_Scheduling_RemainingWork
Microsoft_VSTS_Scheduling_Size
Microsoft_VSTS_Scheduling_StartDate
Microsoft_VSTS_Scheduling_StoryPoints
Microsoft_VSTS_Scheduling_TargetDate