Hi All,
Whats the difference between query entity and execute stored procedure function from the execution stand point. When we get the data from tables or views.
Will there be any heap issues when we use executestoredprocedure function? what are the pros and cons of using executestoredprocedure() functions.
Discussion posts and replies are publicly visible
Hi
In my opinion, query entity is more controlled and protective of the Appian System. It will limit the quantity of data being accessed from the tables, whereas executing a stored procedure can be uncontrolled - without knowing you could easily bring in a couple of hundred thousand records into memory - attempt some transformation and then consume all the heap leading to a system outage
Don't get me wrong, Stored Procs can be very handy if used correctly and suitable limits placed on the stored proc akin to what you'd get from OOTB functionality in Query Entity.
Just my opinion though.
Cheers
Paul
Lets say query entity with startIndex as 1 and batch size as 20 will get 20 rows... iff we perform the same operation using stored procedure LIMIT 0,20 in my SQL query. What would be the impact?
in our use case when we query a view using query entity its taking lot of time to get 20 rows... but when i execute the same in SP its very fast.
QE does 2 queries if you are passing a specific batch size, one to do a total result count, the other to get the data. So if your view is slow, QE will be twice as slow but the total count is necessary if you want to display data in a grid.
Per Paul, I would not use stored procs to just read data in process. Now you have yet another object to maintain.
Also, how slow is slow? What is the stored proc performance? Are you passing a sort parameter?
When i get 20 rows using query entity its taking approx 10 seconds, when i get 20 rows using stored procedure with my query having LIMIT0,20 its taking 1 or 1.5 seconds.