cancel
Showing results for 
Search instead for 
Did you mean: 

Reg - Performance of Stored Procedure

former_member187794
Participant
0 Kudos

Hello Experts

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

Regards

Giri

Accepted Solutions (1)

Accepted Solutions (1)

SergioG_TX
Active Contributor
0 Kudos

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

former_member187794
Participant
0 Kudos

thanks Sergio

We are implementing transpose logic at the View level. Per your suggestion, we moved the transpose logic to Stored Procedure and also, divided the query into sub queries

Regards

Giri

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

No, calc view performance is not impacted (at least to to this extent) by an invocation via a stored procedure.

Given the information you provided, there is no way to find what causes this effect - in fact we don't even know how you obtained the runtime numbers, so that could be wrong in itself.

Anyhow, an easy way to understand where time is spend by your query is to use PlanViz and to check the time single execution steps take and how much data these work on.

Most often this will provide the required information to move on.

SergioG_TX
Active Contributor
0 Kudos

thanks Lars, we are seeing this when running the calc view from the SQL console as well as when running the stored procedure from the sql console. the same invocation varies drastically as he mentioned those times. do you know if there is any DB configuration that may be causing this? we have run the plan analyzer as well and are unable to see the cause other than it is a stored proc executing it instead of running the view directly. the view as Giri mentioned has 2 required input parameters which are in fact passed to the stored procedure as well.

lbreddemann
Active Contributor
0 Kudos

And here we have DB developer again asking the DBA for help with tuning the application... is that just a deja-vu or a hickup in the Matrix?

Jokes aside, before asking for "configuration parameters' it's absolutely necessary to understand what is consuming time in your code.

If you cannot see it in Plan Viz chances are that you are not using it correctly - which wouldn't be your fault given that there is no training or tutorial documentation available for just that.

So why don't you go ahead and share the PlanViz files and your stored proc. code so that we can have a look at what's going on?

former_member187794
Participant
0 Kudos

Hi Lars

Thanks for your reply. Please find attached the screen shots of duration and quick snapshot of Stored procedure logic

Regards

Giri