use of function executestoredprocedure()

The function itself is pretty lightweight and using standard the Java/JDBC drivers to connect. In my experience, any issues with performance tend to be with the stored procedure itself. There aren't other SP functions available in the field to my knowledge, and this is the one I've personally used on many different engagements. As for alternatives, we'd have to understand more about your data structure and what you are trying to now what else might be feasible.

Regardless, can you clarify what you mean by performance issues? What have you investigated that points you in the direction of the executestoredprocedure function as the issue? If you haven't already, I would look into expression detail logs and performance view for your interfaces, and get timing on that function versus overall interface load time.

Hi sahilb346  is there any specific reason behind opting for stored procedure instead of views or materialized view?

I believe, in your case, materialized view could be the best option, this will give you better performance as well.

However as @Justin mentioned, what do you mean by slow performance?

Did you try executing this procedure directly In DB, how much time it's taking to return the response?

There might be chances where your interface might be causing the performance issues.

I've built a few stored procedures (in MS SQL) for use in our Appian apps (all for select type queries not for insert/update). I am calling them via the executestoredprocedure() plugin as both a function in a SAIL form/interface as well as in a script node in a process model. They execute quickly and generally without problems. One in particular comes to mind and it runs in SAIL in a report type interface, you enter values into 2 text fields and it calls the procedure which runs a few queries and unions the result sets together. The interface is very responsive with sub 1 second response (just ran in prod at 0.03s), returning 17 columns of data and 9 rows of data. The procedure unions together 4 select queries that each left join 3 tables together. The tables are 12000, 200 & 80 rows each.

Here are a few thoughts
1) write your queries outside of Appian in the native database query tool that you normally use or your DB vendor provides
2) make sure your queries themselves are fast/responsive before you bother to combine them together into more complex logic for your stored procedure
3) if your tables being joined are large, make sure you have proper indexes and table design. Running a select query on a 'US State to Abbreviation table' where there are only 50 rows is fine without an index on say, the state name or abbreviation, but running a query on a large table with say, 200k+ rows where your where clause doesn't include indexed columns means a full table scan...the bigger the table the slower the query
4) most databases support an execution plan functionality (for Microsoft SQL Server it is 'showplan') which will help you write more efficient queries by showing the table scans and heavy parts of your query.
5) Test your procedures natively and then via an Appian Expression (calling the executestoredprocedure()). While the Expression/Rule will add some overhead, it shouldn't be much. Safe bet, your stored procedure should probably execute for <1 second. I believe mine tend to run for 0.1 or 0.2 seconds. Also, if your underlying tables include a lot of columns and your result set only needs say, 5 columns, have the stored procedure only return the columns you need, that way when Appian returns the data from the call it only has to interpret and convert a small amount of data into a CDT, etc.

Either way, it is important to remember in any of your queries that you need good table design. If you have large tables with 10s of thousands+ rows of data and you don't utilize indexes well your system will have to page through ALL of the data to find what you need. This will affect performance of your normal query entities, rules, etc. You wouldn't try to find someone (by name) in a phone book if the phone book wasn't ordered by last, first name. Likewise, you wouldn't want to try and find a specific phone number in a phone book to get the matching person's name...unless your phone book was sorted numerically by phone # (there are old 800 number phone books that do this).

Hope this helps, Ryan

I would suggest few options to optimize the code:

1. Create a view and do type cast in Appian to map into CDT.
2. Break the stored procedure into small part and execute on different section on user interaction.
3. If you Appian is upgraded to 17.4 you can use multiple query to get data because it will work in parallel processing which is efficient.