Executing stored procedure takes longer when called via executeStoredProcedure function

Question

Dear Reader, 
 I have a stored procedure (functions are also a part of this procedure) which when executed in the SQL Server takes around 2 - 3 seconds to execute. This same procedure when executed via Appian using executeStoredProcedure function is taking more than 30 seconds, sometimes more than a minute. 
 Is there any way I can optimize this? 
 Are there any changes required to be done on Appian/Database side to fix this issue? 
 Thank you in advance! 
 Shreya

Chris · Accepted Answer

Hi Shreyadm, if you execute the procedure in the Smart Service (vs SAIL function), do you see better performance? Similar issues have been discussed in the Execute Stored Procedure plugin thread with App Market. Such as, this may be due to runtime validation which we cannot set to false() in the function, and takes extra time to perform validations prior to execution. Within the Smart Service, you can set this parameter to false. 
 If you do have to call this via SAIL, you may be able to work with your DBAs on validation performance. 
 In our situation, we ended up creating a web service external to Appian (separate web server), which called the procedure, and calling this web service as a wrapper from within Appian drastically increased our performance.

Executing stored procedure takes longer when called via executeStoredProcedure function

Top Replies