ReportServer Forum

zitot

Member

Registered: 2024-10-12

Will using parameter help performance?

while connecting to Oracle SQL through the jdbc thin client
I am trying to figure out how to optimize some of my queries, as they can take minutes to run.

At the moment I have usually hardcoded as string literals instead of parameters for testing purposes, I'll change it later, but I was wondering if a report server parameter is equivalent to a bind variable, as far as improving the performance goes, or if it is more like parsing a string literal each time it sees it; as i understand it, oracle sql bind variables allow it to not need to parse a string literal

for example
where col = 'value', value can be replaced with a bind variable e.g. :B1

so if i use param $P{my_str} (or maybe it was $P!{my_str}) that would replace the actual source code with the string literal and be the worst for performance, as my understanding goes, but does ${my_str} do something?

Or maybe I'm misunderstanding how bind variables, hard parsing and soft parsing works.

It's not a big problem now but as I write more reports it's something I want to know now.

eduardo

Administrator

Registered: 2016-11-01

Website

Re: Will using parameter help performance?

Hi zitot,

parameters are converted into prepared statement parameters if used as described here ($P{key}) : https://reportserver.net/de/dokumentati … -guide-4-7 (7.3 Working with Parameters). If you use the $!{} syntax, the parameter will directly be entered in the query by omitting the JDBC parameter mechanism (direct replacement). More details of his here: https://reportserver.net/de/dokumentati … -guide-4-7 (7.3 Working with Parameters). Also, you can check the source code available here: https://github.com/infofabrik/reportser … erybuilder

Regards,
Eduardo

abraun

Member

Registered: 2020-02-21

Re: Will using parameter help performance?

Your query performance will be directly dictated by the database. the parameter will just replace your hard-coded value. You will have to work on native query performance enhancements on the database itself by finding indexes, limiting columns and rows, or other methods to speed up the query.