Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: July 13, 2021 - 4:32 pm UTC

Last updated: July 13, 2021 - 4:32 pm UTC

Version:

Viewed 1000+ times

You Asked

I've read Alex Keh's articles on Fetchsize setting for ODP.NET (there's a PowerBI article and an older SSRS related article), and I've seen first hand a cloud Oracle DB connection transferring data literally 10 times faster with a larger Fetchsize than the default. (On-premises databases didn't seem to show performance differences)

As an SSRS developer, I'm a heavy user/consumer of the Oracle ODP.NET Managed driver, rather than a dotnet developer. My main question is this:

- Given that there's one global Fetchsize setting for the ODP.Net Managed Driver, it means that ALL SSRS reports that connect to Oracle would use a newer/much larger Fetchsize. (The sweet-spot in my case appears to be setting it to about 1MB

- Is there any collective knowledge on whether making a large increase to Fetchsize impacts memory consumption on a busy SSRS server, one that runs thousands of reports per day, dozens, occasionally hundreds of concurrent requests, and most (roughly 80 percent of the Oracle connections, are on-premises, not cloud connections?)

- And (new topic, since I'm here already) - As a future enhancement, are there any plans to make the ODP.NET SSRS data provider support including fetchsize in the parameter's name/value pairs? That would make my earlier question not necessary.)

and we said...

To answer your two questions.

1) Yes, the larger the FetchSize and the more connections, the more memory will be consumed. It does impact memory. The solution is generally adjust the amount of physical memory, the number of middle-tier machines, and/or reduce the FetchSize. It's a balance between hardware cost and performance.

2) There's an open question how committed MS is to SSRS. Specifically, the outward signs point that the future of MS reporting tools is gravitating toward Power BI. We've observed that Power BI is adopting equivalent SSRS functionality, there is no SSRS equivalent PaaS on Azure, SSRS updates are published on the Power BI team blog, and MS has instructions on migrating SSRS to Power BI.

Without stronger MS support for SSRS, it's hard to justify investing in more SSRS integration vs. Power BI integration, which has similar needs.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow up question

A reader, July 14, 2021 - 1:05 pm UTC

Thanks for the speedy replies.

Regarding answer number 2 on the future of SSRS, I can reluctantly understand taking that position. (Although, SSRS is still "free" in a manner of speaking with SQL Server, so it's not quite dead yet!)

If I reframed the question to PBIRS, the superset to SSRS that comes with Power BI, I suppose I will toss out the same wish for a connection by connection Fetchsize parameter, since the unmanaged ODP.NET driver also only has one global Fetchsize setting.

Thanks again for the replies!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.