Stored Procedure over Query Entity


Is there any advantage of using Stored Procedure function over Query Entity or Query Rule? Like Performance wise etc 

Will Stored procedure function(execute stored procedure) cause any memory issues. Can anyone please let me know.


  • Hey Ajay,

    Stored Procedures would always suit the best for large sets of data.

    But however depending on your requirement you can choose the appropriate ones.

    1. The MAX size of the return dataSubset is 1MB and AKAIK there's no limit for SP's as we're only passing the query from appian and the data execution would happen on the DB level.

    2.Usually SP execution would be more faster.

    3. You can do almost everything a queryEntity can do to an SP.(Filters aggregation,selection,etc.).

    4. As a good design pattern its better to generate the query at Appian level dynamically and keep the DB simple to handle only the ddl/dml statements.(This makes us easier to debug).

    In my previous scenarios i have replaced queryEnities with SP where it was timing out for large datasets. Hence SP is a good way to go!.



  • Benchmark it and see.

    Keep in mind, though, that a performance savings at a very low number of rows does not mean you'll have a performance savings at increasingly high numbers of rows.  To perform a legit test, you'll want to try with very few rows, then mock up an extensive number of rows to see if the one you choose STAYS better. 

    Keep in mind also, that if the performance improvement is negligible, then you'll need to consider other factors like maintainability.  Execute stored procedure is a 3rd party plugin, and not even vanilla Appian, after all.  It could be deprecated, or they could replace it with one that IS baked into OOTB Appian, like they did with the Health Check.  I would venture it has a bit more risk than Query Entity, and requires more SQL knowledge of your developers / tech support.  And they'll have to go searching through 2 systems to find all the code to update.

    Keep in mind also, that it only matters whether you're using the best performing choice here if it actually is your bottleneck.  Should you shave 12 ms off this query, or spend that time shaving 20 whole seconds off the next?  What have you gained making the faster of two parallel processes faster still, when you still have to wait the same time for the slower one you always did?  It profits you nothing.  You should focus your time on where you could shave off the most time, and it's honestly probably not here.

 Discussion posts and replies are publicly visible