Is it possible to Call Stored Procedure to get some data in SAIL Interface or Expression Rule?

Hi - Yes, you can invoke a Stored Procedure directly from a SAIL interface, as well as from in Process. You'll need to install the following plug-in if you haven't already:

https://community.appian.com/b/appmarket/posts/execute-stored-procedure

You will then have access to fn!executestoredprocedure() which you can call within a SAIL interface.

Pros:

• you do not have to leave the SAIL interface (e.g. call a Process Model) to conduct transactions with a Database

Cons:

• depending on the complexity and risks associated with your database integration you may end up writing some complex logic (which ends up looking very process-like) in your SAIL interface to handle "non-happy-path" results (i.e. exceptions/errors) - for example: how are you going to notify your Support team that there's been an error? How is that error surfaced to the End User?

Other schools of thought exist, but I would err towards using Stored procs for read-only purposes AND very simple updates (e.g. toggling the value of a Boolean field), where there are no down-stream implications of the transaction failing.

Hi 

We use the function offered up by the Stored Procedure plug in to invoke non updating Stored Procedures to bring back information into SAIL. Care is needed here as the OOTB query entity will limit the amount of data that is brought back, where as Stored Procs can bring EVERYTHING into memory unless you specify a batchsize in the Stored Proc itself. We've had issues with heap space consumption where Stored Procs have been used to bring everything into memory and then transformation done to do something with the data.

Don't get me wrong - Stored Procedures are a very good mechanism of linking data tables together - but I would urge care in the design and think through the data limitations accordingly. 

Cheers

Paul

Thanks for the detailed info. will this Replacement of SP instead of View  improve the performance point of view. one thing i am assuming  transformation time(while using Query Entity Rule) is not required in SP comparatively with View(Query Entity).

If you're looking to improve the performance then I'd suggest there are other places to look first. In both cases you are communicating over the network to a Database. Using a Query Entity then you are sending the raw SQL over the network and then getting the DB to parse that, run it and returns the result. An SP just sends over any parameters, but unless the SQL statement is huge your performance is going to be subject to how the DB is retrieving that data (are there indexes that could be applied to help make the data retrieval optimal? Are the Database Statistics tables up to date so that the DB knows the best way to retrieve the data? I would check these out BEFORE you even consider looking at a Stored Procedure.

We are using many places to get the data from multiple tables with the help of Views. These views having large no of joins and conditions. So our DB team is suggested to replacing those views with stored procedures hence with this change they expecting better performance from Appian end as well.

So Please give us some best practices to use SP(to only get the Data no transactions and updates) instead of Views.

Thanks a lot.

I'm not a DBA so they would have a better view (no pun intended!) of what implementation will deliver a better performance. My limited understanding is that by executing a SQL request (which is what a Query Entity would send across to the DB) the database: parses the SQL, looks at the request, looks at its statistics, generates an access plan and then executes that plan to get you your data. Swapping to a Stored procedure removes some of those steps but does not necessarily result in much of a material performance improvement. Yes, the SQL is pre-parsed, an execution plan is pre-generated...but with a SQL request the execution plan is cached after the first call anyway, and it's the plan - HOW the Database engine retrieves the data - that is THE most critical aspect that affects the performance (at the database end anyway).

Understood. here another aspect is we need to take care (Appian) fo implementing the pagination. but batch size should be taken care by SP side.

I concur with Stewart.  Bare in mind why a VIEW may be slow / not performing, have the correct Indexes been applied to tables.

I find using VIEWS a lot simpler, but performance can take a drop if it is doing 'too much' i.e. too many joins - better to have specific VIEWS for this.

With SPs you will need to handle any errors raised,

Thanks a ton for the details. Let our DBA team decides which one need to implement. Can you some one give the details about what are the things we need to implement from in SAIL what are coming as OOB from Appian when we are using SP to get the data and binding to Paging Grid.

Things like how do we get the default features with grid like Pagination, Sorting etc...