Tip: Dealing with timeout errors in the Espace Edit screen of Service Center

Tip: Dealing with timeout errors in the Espace Edit screen of Service Center

  
Hi everyone,
 
I am writing this post to talk about an error some OutSystems Platform users face when accessing the details of a specific eSpace in Service Center (the "Espace Edit" screen).
 
Symptom
When the user click on an eSpace in Service Center to see its details (the eSpace_Edit screen, where you can find its versions, references, site properties, etc.) the web request takes some time to process and eventually we are presented with an error message such as the following:
 
Error in advanced query GetConsumers in Preparation in eSpace_Edit in Factory in ServiceCenter (Select    {Espace}.[SS_Key],    {Espace}.[Name],    'eSpace',    '',    0,    null,    @sumIsBroken,    sum(Case When Consumer.[Published_Prod_Version_Id] <> @versionId Then 1 Else 0 End),    0,    '',    @sumIsMissing  From {Espace_Reference} Consumer  Join {Espace_Version} On {Espace_Version}.[Id] = Consumer.[Consumer_Version_Id]   Join {Espace} on {Espace}.[Id] = {Espace_Version}.[eSpace_Id]   Where    Consumer.[Producer_SS_Key] = @key And    {Espace}.[Is_Active] = 1 And    Consumer.[Consumer_Version_Id] = {Espace}.[Version_Id]  Group By {Espace}.[SS_Key], {Espace}.[Name]  Order By {Espace}.[Name]): Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Similar errors may occur in operations such as publishing eSpaces, where the stack may look something like:

Error in advanced query GetConsumers in Espace_UpdateCache in Espace in ServiceCenter (SELECT DISTINCT {Espace}.* from {Espace_Reference} inner join {Espace_Version} on {Espace_Version}.[Id]={Espace_Reference}.[Consumer_Version_Id] inner join {Espace} on {Espace}.[Id]={Espace_Version}.[eSpace_Id] where producer_ss_key=@key and {Espace}.[Is_Active] = 1): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

In an Oracle installation, these errors may look a bit different:

Error in advanced query GetConsumers in Espace_UpdateCache in Espace in ServiceCenter (SELECT DISTINCT {Espace}.* from {Espace_Reference} inner join {Espace_Version} on {Espace_Version}.[Id]={Espace_Reference}.[Consumer_Version_Id] inner join {Espace} on {Espace}.[Id]={Espace_Version}.[eSpace_Id] where producer_ss_key=@key and {Espace}.[Is_Active] = 1): ORA-01013: user requested cancel of current operation.
 
Cause

This sort of errors happen because there is a timeout when running a query to obtain the eSpace consumers. Usually the correct approach in this situation is to deal with the inherent database slowness for these operations. The most frequent causes are the existence of too many eSpace versions or not having any maintenance plans running in your database.
 
Resolution

With these causes in mind, we typically suggest the following:
 
  1. Check if there are maintenance plans defined in the database and that they are running frequently and with success. If not defined, we recommend that you create at least one maintenance plan to reorganize or rebuild indexes as well as updating statistics. You should involve your team of DBAs to assist you in defining a database optimization strategy according to the recommendations provided in the following technote: www.outsystems.com/goto/sql-server-best-practices
     
  2. If the recommendation in item 1 is being met and the problem persists, consider deleting old versions of eSpaces in your factory (Service Center > Factory > eSpaces > Check Old eSpace Versions to Delete). When you do this, do not forget to backup versions that may be important to archive. In fact, this is one of the suggestions you can find in the following post about tuning your development environment:
    http://www.outsystems.com/forums/discussion/9691/how-to-have-a-great-development-environment/
    After performing this operation, please run your database maintenance plans once more and check if the errors stops occurring.
     
  3. If none of the above solve your problem, ultimately you can resort to simply increasing the time the query is allowed to run in your system. For that, increase the Default Query Timeout value in Configuration Tool (tip: you will need to restart all OutSystems Services and the application service for the change to become effective). You can read more about it at http://www.outsystems.com/help/ConfigurationTool/9.0/default.htm#Database_Tab_in_SQL_Server.htm (the example for .NET).

I would also like to recommend everyone arriving here to read the post on the several time out types in the OutSystems Platform at http://www.outsystems.com/goto/timeouts-guide.
 
Cheers!
 
João Proença