Hi,

I have an Oracle Stored procedure which returns data to SSRS in a sys_refcursor.

This query takes 13 seconds to complete within SQL Developer.

When running in SSRS the DataRetrieval time is showing at approx. 2 seconds, and the time processing is taking anything over 60 seconds. I have followed the guidance with regards to adding page breaks, setting CanGrow = False etc without success.

As a test I exported the tables to SQL Server, recreated the stored procedure and updating the RDL to reference the sql server data source/proc.

The query takes 13 seconds in Management Studio, and also shows as 13 seconds in SSRS. The time processing now drops to less than a second. So my issue appears to be with how the data is passed from Oracle to Reporting Services.

I have scoured the internet, without success so hoping someone who has worked on a similar project can shed some light.

If you require any additional details, please do not hesitate to ask.

Thanks

• Edited by Mitch_s_s Thursday, June 18, 2015 2:23 PM

Hi Mitch_s_s,

Thank you for your question. 

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

Thank you for your understanding and support.

Best regards,
Qiuyun Yu

Hi Mitch,

Thank you for your questions.

Report Data pass from SQL Server and Oracle to Reporting Service uses different processing extension.

• Data processing extension for SQL Server:Uses the .NET Framework Data Provider for SQL Server to connect to and retrieve data from the SQL Server Database Engine.
• Data processing extension for Oracle:Uses the .NET Framework Data Provider for Oracle. With this extension, the report server can access Oracle data sources through Oracle client connectivity software.

Here's the reference:

https://msdn.microsoft.com/en-us/library/ms152816(v=sql.120).aspx

We all know that data retrieval is happening on SQL Server side and data processing is happening on Report Server itself. If Reporting service get the data, the other process should be same. 

So if the data sets is same, the only different processing time should be the waiting time during the data pass period from SQL Server or Oracle database to Reporting Service.  If it is the case, then this issue may be caused by design of Data processing extension.

To prove that, the only method is to do more test. Please test whether other reports which get data from SQL Server and Oracle also have the same issue.

Best Regards,

Halin Huang

 

Hi Mitch_s_s,

We want to know whether our solutionhas resolved your problems?There are several days you don't contact with us. And we will close the case.
If there're any questions, don't hesitate to contact with us.
Thank you for your time.

Best wishes & regards!

Halin Huang