Reporting and Analytics for ERP

Get Involved. Join the Conversation.

Topic

    absalvi
    Reports with hardcoded parameters performing better then...
    Topic posted October 29, 2019 by absalvi Red Ribbon: 250+ Points, tagged BI Cloud Connector, BI Publisher, Fusion Financial reporting, OTBI, Reports, Sample Reports 
    43 Views, 5 Comments
    Title:
    Reports with hardcoded parameters performing better then those with bind variables
    Content:

    Hello,

    We have a peculiar problem, we have a query for gl balances report which when run by hardcoding the Legal entity and period runs fine fetching all our data within 30-40 seconds. However, the same query takes 4 minutes to run when we define a parameter in the Data Model and refer it as a bind variable in the report. Sample below:

    Runs Quickly:

    select * from table where column = 'ABC';

    Performance Issue:

    select * from table where column = :argument1

    Has anyone faced such an issue earlier? Is there any configuration for this? or how can this be resolved.

    Thanks,

    Abhishek

    Comment

     

    • Sudhakara Rao Kovuru

      This is expected scenario only since hardcoded value directly pass to SQL engine to get the required data...whereas the bind variable has to pick the reference and send to fetch...

      There is no configuration to handle it.. if you query is taking too much time then need to work on it by using some performance tuning tips.

      • absalvi

        So our query with hardcoded values takes 40 seconds and with bind variables take 4 minutes. Does this mean that passing of the bind variable takes approx. 3 minutes and such a huge difference is to be expected? The bind variable value is coming from a List of Values which have been defined in the report itself.

        • Sudhakara Rao Kovuru

          Yes its depends on the SQL query which you built...I can't comment on the time which bind variable taking but eventually more time than hard coded values.. if you  need some help here then please share the query so that we can try to help to optimize it.

          • absalvi

            Thanks, however, we have already tuned our query from 30 mins to 40 secs. Would be more interested on leads towards why the bind variables are taking time. Thanks for your time! :) 

    • David Taylor

      Do you get the slow performance no matter how many times you run the report?  ie run the report twice, each time with the same value for the parameter? 

      Are you able to post the exact query you are using? 

      I am new to Cloud but is it possible to get an explain plan for the query (with and without the bind variable)?