Lets say I have a view that contains 20cols and 3000 rows, and I want to return all the data to my process.
In terms of performance, would it be better to
a) Define a StoredProc in the MySQL DB that does a select * from view and call it via the StoredProc Smart Service
or
b) Define an expression rule that contains an entityquery which gets all the data.
In my own mind, I think that the first option would be the slowest as we need to invoke the smart service, call the SP then parse the data, whereas the second option has no smart service to worry about.or will there be no real difference between the methods as invoking the entity query will take just as long as the smart service ?
Discussion posts and replies are publicly visible
pauls0010 Hi Pauls, As per the data volume you mentioned here i dont think you will face much performance aspect issue. But suppose if the data volume increases its risky to use stored Proc where it might cause heap memory issue which can screw up the server. My suggestion would be going with Query Entity which has better handling where we can fetch data on pagination and also easy to cast the datas to Appian values as well.
as a rule of thumb, better to keep everything 'in' Appian rather than use SP's in a database I think.
Yes i agree, but unless and until we have some complex operations to be done with the data.
true, but in my case things are relatively simple.
I think it depends on what you need to d with the data. For example,
If you need to pull the data back for display, filtering, selection... then use a query entity within SAIL since you can control batching and you are showing the data as it is pulled back (formatted) from the view.If you need to pull back the data, then run complex business rules, and then update other data in a process model... then use a stored procedure to perform the manipulations.These are just a couple of example, but the guiding principle is to keep the footprint light to support scale.Hope that helps gives you some food for thought.