Hi

When opening /OutDoc/eSpace_List.aspx, the page runs for a while then displays an error:

The following error is logged in Service Center:

MessageError executing query. Error in advanced query GetEspaces in Preparation in eSpace_List in MainFlow in OutDoc (SELECT {Espace}.*, {Espace_Version}.[Description], {Espace_Version}.[Uploaded_Date], {UserMT}.*  FROM  {Espace}  INNER JOIN {Espace_Version} ON    (    COALESCE({Espace}.[Version_Id], 0) = 0    AND     {Espace_Version}.[Id] = (SELECT max(version2.Id) FROM {Espace_Version} version2 WHERE version2.eSpace_Id = {Espace}.[Id])    OR     {Espace}.[Version_Id] = {Espace_Version}.[Id]   )    LEFT JOIN {UserMT} ON COALESCE({Espace_Version}.[Uploaded_By], 0) = COALESCE({UserMT}.[Id], 0)  WHERE( Lower({Espace}.[Name]) LIKE (@eSpaceNamePattern )) AND {Espace}.[Is_Active] = 1 AND {Espace}.[Is_System] = 0  ORDER BY @OrderBy ): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Environment InformationeSpaceVer: Id=77146, PubId=0, CompiledWith=11.26.0.41987RequestUrl: https://[redacted]/OutDoc/eSpace_List.aspx?(Not.Licensed.For.Production)= (Method: GET)AppDomain: /LM/W3SVC/1/ROOT/OutDoc-476-133584253536739038FilePath: D:\OutSystems\Platform Server\running\OutDoc.01641990959\eSpace_List.aspxClientIp: 10.11.95.91 X-Forwarded-For: 62.232.22.31Locale: en-USDateFormat: yyyy-MM-ddPID: 9448 ('w3wp', Started='4/22/2024 3:02:16 PM', Priv=3052Mb, Virt=2122211Mb)TID: 503Thread Name:.NET: 4.0.30319.42000

Stack[1] Error executing query.   at ssOutDoc.Flows.FlowMainFlow.ScrneSpace_List.FuncssPreparation.QueryGetEspaces(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64& outParamCount, String qpsteSpaceNamePattern, String qpstOrderBy)   at ssOutDoc.Flows.FlowMainFlow.ScrneSpace_List.Preparation(HeContext heContext)   at ssOutDoc.Flows.FlowMainFlow.ScrneSpace_List.Page_Load(Object sender, EventArgs e)   at System.Web.UI.Control.OnLoad(EventArgs e)   at System.Web.UI.Control.LoadRecursive()   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)[2] Error in advanced query GetEspaces in Preparation in eSpace_List in MainFlow in OutDoc (SELECT {Espace}.*, {Espace_Version}.[Description], {Espace_Version}.[Uploaded_Date], {UserMT}.*  FROM  {Espace}  INNER JOIN {Espace_Version} ON    (    COALESCE({Espace}.[Version_Id], 0) = 0    AND     {Espace_Version}.[Id] = (SELECT max(version2.Id) FROM {Espace_Version} version2 WHERE version2.eSpace_Id = {Espace}.[Id])    OR     {Espace}.[Version_Id] = {Espace_Version}.[Id]   )    LEFT JOIN {UserMT} ON COALESCE({Espace_Version}.[Uploaded_By], 0) = COALESCE({UserMT}.[Id], 0)  WHERE( Lower({Espace}.[Name]) LIKE (@eSpaceNamePattern )) AND {Espace}.[Is_Active] = 1 AND {Espace}.[Is_System] = 0  ORDER BY @OrderBy ): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

This error is caused by the advanced SQL in MainFlow > eSpace_List > Preperation > GetEspaces:

It seems that OutSystems developers aren't following best practices? See https://success.outsystems.com/documentation/best_practices/performance_and_monitoring/performance_best_practices_queries/#keep-max-records-consistent-with-your-needs:

"Solution

When there are limitations to the amount of records that are fetched from a query, you should fill in the Max Records property of an Aggregate accordingly to optimize the query execution time. This is especially useful in table records or when an Aggregate is used to get a single record.

Importance

Usually, there's no need to display thousands of records in a single screen, so there's no need to get them all from the database. Only get the amount of rows that will be displayed. This improves screen loading."

Why does the query retrieve all records from {Espace_Version} when the table on the Espace_List page only displays 50 records. The query returns 1291 records in our factory, so that means 1241 query rows are being retrieved and then ignored.

OutDoc version 8.0.10 updated the timeout property for the GetEspaces SQL query to 110 seconds, but this is a sticky-tape solution and the page still times out. The SQL need to be paginated (OFFSET / FETCH NEXT in MS SQL) and Max. Records set to 50.