Skip to Content

Reg - Performance of Stored Procedure

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Nov 04, 2015 at 09:57 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Nov 03, 2015 at 10:49 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded