Oracle sql query executed slowly
Posted: Fri Mar 15, 2013 1:37 pm
Hello!
I have a quite strange problem, I execute SQL queries from the report template, I connect to Orace database using the Oracle ODP.NET driver (the oracle odp.net OS driver is at version 11.2.0). In few cases the query execution tooks too long - compared with the execution from sql editors, like sqldeveloper. The difference is about 20 second when 114 rows are returned: 2 seconds in sqldeveloper, 22 seconds in the stimulsoft query designer. If more records are returned, the difference is even bigger.
Unfortunately I am not allowed to post information about the underlying database functions, and providing an example report would be difficult as well.
The SQL query looks like the following
where the null input parameters are sent instead of varchar2
and the function returns pipelined rows of pl/sql type like this
I use Stimulsoft Ultimate 2013.1.1507. Can you please look at the problem? Thank you for your time.
Jozef
I have a quite strange problem, I execute SQL queries from the report template, I connect to Orace database using the Oracle ODP.NET driver (the oracle odp.net OS driver is at version 11.2.0). In few cases the query execution tooks too long - compared with the execution from sql editors, like sqldeveloper. The difference is about 20 second when 114 rows are returned: 2 seconds in sqldeveloper, 22 seconds in the stimulsoft query designer. If more records are returned, the difference is even bigger.
Unfortunately I am not allowed to post information about the underlying database functions, and providing an example report would be difficult as well.
The SQL query looks like the following
Code: Select all
select * from table(reporting_pkg.report_body('SK', to_date('15/03/2010','dd/mm/yyyy'), to_date('15/03/2013','dd/mm/yyyy'), to_date('15/03/2010','dd/mm/yyyy'), to_date('15/03/2013','dd/mm/yyyy'), null, null, null, null))
and the function returns pipelined rows of pl/sql type like this
Code: Select all
create or replace
TYPE report_body_t AS OBJECT(
varch1 VARCHAR2(255)
,varch2 VARCHAR2(511)
,varch3 VARCHAR2(255)
,varch4 VARCHAR2(255)
,varch5 VARCHAR2(255)
,varch6 VARCHAR2(255)
,varch7 VARCHAR2(255)
,varch8 VARCHAR2(255)
,numb1 NUMBER(19,6)
,numb2 NUMBER(19,6)
,date1 DATE
,varch9 VARCHAR2(255)
);
Jozef
, but it still does not explain why the query was executed quicky in other sql editors and so slowly in Stimulsoft.