Oracle sql query executed slowly

[Jozef Balga]

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

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))

where the null input parameters are sent instead of varchar2
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)
 );

I use Stimulsoft Ultimate 2013.1.1507. Can you please look at the problem? Thank you for your time.

Jozef

[Alex K.]

Hello,

Can you please send us a script for create a problem query and test data for analysis.

Thank you.

[Jozef Balga]

It turned out the problem was fixed by rebuilding database statistics , but it still does not explain why the query was executed quicky in other sql editors and so slowly in Stimulsoft.
Thank you for your time.

Jozef

[Alex K.]

Hello,

It is difficult to say something without a sample which reproduces the issue.
We did not receive any messages reporting the similar issues.

Thank you.