Stored Procedure over Query Entity

Hi,

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.

Thanks 

  Discussion posts and replies are publicly visible

Parents
  • 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!.

    Thanks,

    Abhishek

Reply
  • 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!.

    Thanks,

    Abhishek

Children
No Data