20
Views
10
Comments
Error executing query. Error in advanced query
Question

Hi,


I am getting below error when I am trying to access a screen. 


The error logged in "Service Center" is  Error executing query. Error in advanced query. Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. 


I tried to execute the same query in Azure SQL database and it ran within 2 sec and returned 8000+ rows, but when coming to run time it is throwing "Internal Error" screen.


Thanks,

Kushala

Rank: #203

Hello Kushala Mannem Can you provide the stack of the error from service center for more details?

Rank: #48022

Hi Raphael,


PFB the stack message :


[1] Error executing query.
at ssMetronome.Flows.FlowProgram.ScrnProgram_List.FuncssPreparation.QueryGetCPAPSAdvanced(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64& outParamCount, Int32 qpinATS_Filter, Int32 qpinESF_Filter, String qpstSearchKeyword_Filter, String qpstOrderBy, Int32 qpinOpportunityNumberFilter, String qpstAgreementFilter, Int32 qpinDRRESStatusIdFilter, Int32 qpinNullDRRESStatusId, String qpstUserSignum, Int32 qpinRoleId_BC, Int32 qpinRoleId_CFR, Int32 qpinRoleId_DC, Int32 qpinUserId, Int32 qpinRoleId_PFMManager, Int32 qpinRoleId_PFMLead, Int32 qpinRoleId_PFM, Int32 qpinRoleId_AD09Champion, Int32 qpinRoleId_CBE, Boolean qpboHasRoleBC, Boolean qpboHasRoleCFR, Boolean qpboHasRoleDC, Boolean qpboHasRolePFMManager, Boolean qpboHasRolePFMLead, Boolean qpboHasRolePFM, Boolean qpboHasRoleAD09Champion, Boolean qpboHasRoleProgramSponsor, Boolean qpboHasRoleEA, Boolean qpboHasRoleCBE, Int32 qpinTaskTypeId_AD09Approval, Int32 qpinTaskTypeId_StartDRRES, Int32 qpinTaskTypeId_ReviewRRE, Int32 qpinTaskTypeId_ReviewStructuring, Int32 qpinTaskStatusId_Created, Int32 qpinTaskStatusId_OnGoing, Int32 qpinTaskStatusId_OnHold, String qpstStartDRRESFilter, String qpstProgramStatus, String qpstCU, String qpstCFR, String qpstCRG, String qpstDC, String qpstAD09Champion, Int64 qploLegalEntity, Int64 qploNullLegalEntityId)
at ssMetronome.Flows.FlowProgram.ScrnProgram_List.Preparation(HeContext heContext)
at ssMetronome.Flows.FlowProgram.ScrnProgram_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 GetCPAPSAdvanced in Preparation in Program_List in Program in Metronome (SELECT {CPAP}.[CPAPID] , {CPAP}.[CPAPName] , {CPAP}.[CPAPURL] , {CPAP}.[AuditScopeFlag] , {CPAP}.[PlannedStart] , {CPAP}.[PlannedFinish] , {CPAP}.[CFR] , {CPAPStatus}.[Label] , {CPAP}.[CUName] , {DRRES}.[Id] , {DRRESStatus}.[Label] , {DRRES}.[Version] , {DRRES}.[ApprovedAt] , {DRRES}.[DRRESStatusId] , STUFF((SELECT ';' + {CPAPCRG}.[CRG] FROM {CPAPCRG} WHERE {CPAPCRG}.[CPAPId] = {CPAP}.[CPAPID] FOR XML PATH(''), type).value('.','NVARCHAR(MAX)') , 1, 1, '') AS CRG , DRRESDC.[External_Id] , (SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS 'ESFlag' FROM {CPAPES} WHERE {CPAPES}.[CPAPId] = {CPAP}.[CPAPID]) ESFlag , {RevenueAssessment}.[AD09ChampionSignum] FROM {CPAP} LEFT JOIN {CPAPCustomerUnit} ON {CPAPCustomerUnit}.[CPAPId] = {CPAP}.[CPAPID] LEFT JOIN {ECB4CustomerUnit} ON {CPAPCustomerUnit}.[CustomerUnitId] = {ECB4CustomerUnit}.[Id] LEFT JOIN {CPAPStatus} ...): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.


Thanks,

Kushala

Rank: #132

Hi Kushala,


Your query is giving a timeout, so I would suggest checking the best practices for optimization for SQL like indexes, max records, etc.

Moreover, hust looking at the part of the log your query involves subqueries doing casts and string concatenation (STUFF) which are not very performant operations. You're also doing these queries on the SELECT statement which does not bode well for query performance.

Perhaps, you want to rewrite this query and / or get less information to improve performance.


Cheers,

João

Rank: #203

Kushala, this error is happening every time you try to run this query?

Other advanced queries are also giving timeout?

There are any entities from an external database?


What can be happening is a problem with communication. Are you sure the environments have connectivity?


Also you can try to set the timeout for this specific query in the extended properties to see if can run:


Try to set a bigger value than your default server value

Rank: #203

Also, 


I suggested it to see if it runs, to discard any communication problem, since he said it runned in 2 secs in SSMS.

The timeout may be caused for other reasons that are not related with query performance :)

In addition to Raphael and Joao I want to add following:

Pls try with max number property to 10.

Have you tried query in test and what is the result.

There may be chances that you are transferring 8000 records. so if your scenario permits use paging and fetch less records.

Rank: #203

Well said Abdul!

This can also be the reason of the timeout.



Kushala, as Abdul said you can apply pagination in the SQL Adv query using TOP, to replicate the same behavior used in Aggregates.

If using a table try to bind it using ListNavigation action for example:

List_Navigation_GetStartIndex(<TableWidget>.Id,True) + <TableWidget>.LineCount + 1


In any case in the worst scenario going to the last page will take some time, you can try differents techniques for this like OFFSET but it is not very performatic also

Rank: #132

The max records, on TOP on Advanced query, is of course a good practice.

Just take into account that if the query has an ORDER BY, then it does not improve performance that match since the SQL engine needs to scan the whole table anyway to get the TOP 10.