Two fetchsize questions - A major improvement with cloud (remote) Oracle connectivity - (a) Upper practical limit? (b) why not as big an improvement as my workstation? RRS feed

  • Question

  • In an SSRS 2017 environment, a busy-ish server, with multiple SQL Server and Oracle back ends, almost all on premises except for one Cloud based Oracle database, I ended up making the Oracle cloud connection fetch data about two to four times faster by upping the Fetchsize, in my case in the 64 bit 4.0.30319 machine.config file.  (The regular one too, but it took doing it to the 64 bit folder to apply to SSRS.)

    The default is 131072, and setting it higher speeds things up noticeably for the cloud DB (no major observable difference for the on premises databases in my case.)  The development server is set to 1310720, and tests are two to four times faster.  I'm curious (before trying it) about the implications of setting it this way on the production server.  In the grand scheme of things, I don't think the 1310720 is an outrageously high number, but I'm curious if anyone out there has tried this, because in the context here, the change will apply to all Oracle connections.  (1310720 seems roughly in the ballpark of the sweet spot, I tried some higher numbers without much noticeable difference.)

    Question 2 is, on my desktop, the speed improvement was more dramatic with the same Fetchsize change.  Any experts out there have any ideas why the improvement is different on SSRS?  (Incidentally, ODBC was even easier, you can alter the fetchsize on an individual connection by connection basis.)

    FYI, this was the change, if anyone's curious:  Documentation is smattered all over the place.  I took this path rather than updating the registry.

        <version number="*">
    		<setting name="FetchSize" value="1310720"/>

    Any feedback on the two questions?

    1. whether a fetchsize of 1310720 might cause memory issues on the SSRS production server?  A few hundred users, a few thousand impressions per day, including some big reports that run for at times, hours.  The change would only impact Oracle connections.

    2. ideas why the improvement is less dramatic on SSRS than on my workstation? 

    And p.s., reminder, in my case the speed difference wasn't that dramatic when everything was local/on premises, so although it may be a big deal for my cloud connection, this probably isn't a universal improvement for everybody.
    Friday, June 5, 2020 5:39 PM

All replies

  • Hi johnqflorida,

    Thanks for posting question here.

    After doing some research about fetchsize in SSRS, I cannot find anything related. And we has not enough Oracle knowledge and the Oracle environment. Sorry to about that we cannot propose some professional suggestion. I recommend you to visit Oracle Communities for more professional and effective help: Oracle Communities 

    Best Regards


    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.

    Monday, June 8, 2020 8:22 AM