locked
SSRS Report connecting to Oracle running slowly RRS feed

  • Question

  • Hi

    I have a report that connects to an external providers Oracle server. The report can run within 78 seconds when working but most of the time it does not run at all. This is an SSRS 2016 Instance. The report was copied from a 2008 R2 Reporting server and this runs without any issue within 2 minutes.

    If I open SQL Developer the query takes 106 seconds.

    As it is an external Oracle server I cannot look at performance tuning the query, this is more a question of why is it not completing on a 2016 SSRS box as apposed to SQL Developer or a 2008 R2 reporting server?

    The logs do not show me any errors. The report window just shows loading even though the job that is running has long since stopped.

    Thanks

    Sam

    Tuesday, September 24, 2019 7:27 PM

Answers

  • Hi SG_87,

    Yeah, you could have a try.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by SG_87 Wednesday, October 30, 2019 9:40 AM
    Thursday, September 26, 2019 8:04 AM

All replies

  • Hi SG_87,

    There are several options you could have a try.

    1. Run Select * From Executionlog2, check the time and make some changes to parameters, please refer to SSRS report running very slow but query is very fast in SSMS.
    2. Use subreports or split large reports into parameterized drillthrough reports, please refer to ExecutionLog2 View – Analyzing and Optimizing Reports.

    Also, could you explain more about the 2016 SSRS box that you mentioned above?

    Hope it helps.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, September 25, 2019 2:29 AM
  • Hi Zoe

    The server it works on is a physical HP Server with 2 CPU with 4 cores each, 16GB Memory using SQL Server 2008 and SSRS 2008.

    The server it mostly fails on is a VM with 2 CPU and 8GB memory. This runs only SSRS, the SQL instance is running on another server with 2 CPU and 8GB memory. This is using SQL 2016 and SSRS 2016.

    The servers are not overloaded, CPU and memory usage is low.

    I do not know if it is because of the version of SSRS? The logs do not show anything wrong.

    Thanks

    Sam

    Wednesday, September 25, 2019 9:37 AM
  • Another update, turns out that the query was NOT the same on the old server.

    I am not an Oracle DBA and the query returns within 106 seconds through SQL Developer. Can anyone tell me why it would still not work through SSRS? Is there a setting I can add or would this be for an Oracle DBA to see what it is doing?


    Wednesday, September 25, 2019 3:55 PM
  • Could it be the ODBC driver we use? The Oracle server is 12 and we use the 11 driver, could 12 work better?
    Wednesday, September 25, 2019 3:58 PM
  • Hi SG_87,

    Yeah, you could have a try.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by SG_87 Wednesday, October 30, 2019 9:40 AM
    Thursday, September 26, 2019 8:04 AM