Reg - Performance of Stored Procedure
I am seeing performance issues when calling a stored Procedure.
Let me explain my requirement.
1. Defined a 2 Calculation Views, with 2 input parameters (Parameterized) (not complex logic)
2. Created another calculation view, making a union of two other calculation views
I could execute this 2nd View from SQL console in 0.1 seconds (when fetched 56 rows)
But, when I call the same view from a stored procedure (with same input parameters) it is taking 1.1 seconds approx.
same response time from xsjs or sql console (slightly higher when called from xsjs, around 0.1 seconds difference)
Just trying to understand, if the performance degrades when calling a calculation view from a stored procedure?
We can call the view, directly from a XSJS file. But as part of requirement, we need to use a stored procedure.
Please let me know, if someone has any idea about this situation
Sergio Guerrero replied
Giri, per my tests today, having a calculation view doing the transpose is more costly than doing the transpose in SQL Script in the stored procedure. my solution for this would be to run smaller subqueries running in parallel and then doing a union of the subqueries to yield a result. q1 = select ... from view ; q2 = select ... from view ; . . qN = select ... from view; out = select * from :q1 union select * from :q2 union select * from :qN; this would give you a better response time.. .per my tests, it is now running in 500-800 ms hope this helps