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?
Also, just to add an another point, if you are on Appian 18.4 or later version of Appian, then you do have the opportunity to reduce the number of calls (from 2 to 1) while using queryEntity, by the help of fetchTotalCount parameter of this function.
Means, if you set this as false then Appian will not perform an additional query to get the totalCount, (but you should do this when you are expecting this data to represent on a grid).
Also, if you are using a View which returns huge amount of data, then you should consider switching to a Table or Materialized View to improve the performance.
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.
if the number of rows returned from the stored procedure is 20 rows.. will it cause the heap issue?