use of function executestoredprocedure()

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'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